c#.Net: Execute two SQL statements at a time

Question

I am wondering if there is any way to execute multiple SQL statements. Like I want to get count as well as the value. How can I do this in c#?

Query 1:

cmd = new SqlCommand("select count(*) from tblStock where prodName=@prodName", con); // to get count

Query 2:

cmd = new SqlCommand("select quantity from tblStock where prodName=@prodName", con);// to get qty

int count, qty;

        try
        {
            con.Open();
            count = (Int32)cmd.ExecuteScalar(); //returns null if doesnt exist
            qty = (Int32)cmd.ExecuteScalar();

        }

Is it possible?

Is this the bottleneck in your application? If not, it is unwise to be complicating the code to optimize it. — mellamokb
– mellamokb, Commented Apr 9, 2014 at 22:19

Martin Costello · Accepted Answer · 2014-04-09 22:24:49Z

4

Can you not just select both values in the one select query?

cmd = new SqlCommand("select quantity, count(*) from tblStock where prodName=@prodName", con);

answered Apr 9, 2014 at 22:24

Martin Costello

11.1k5 gold badges66 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Insafian Over a year ago

and then how can i save the result in two different variables? qty and count?

Martin Costello Over a year ago

You need to use an SqlDataReader via ExecuteReader. See here for example usage: msdn.microsoft.com/en-us/library/9kcbe65k.aspx

Martin Costello Over a year ago

Also see here where you can see indexing used to get the column values: msdn.microsoft.com/en-us/library/…

Kyle Hale Over a year ago

This query will fail since you're not grouping by quantity.

Martin Costello Over a year ago

My point was more to write one query that gets both values at once, rather than to execute two. The OP can make the query do what's correct, given then know the schema.

|

Kyle Hale · Accepted Answer · 2014-04-10 16:02:15Z

This doesn't make sense.

If there is more than one row per prodName in tblStock, then what good is returning "quantity" which is not a scalar, but an array of values?

If there is only one row per prodName in tblStock, then what good is asking for a count(*) which is always 1?

Either you want an aggregate value of quantity ("sum(quantity)", perhaps) or you don't need count(*) because there is only one row.

I'm assuming it's the former in which case your SQL query is

select sum(quantity) as sumQty, count(*) as ct from tblStock where prodname = @prodName

Ahmed Galal · Accepted Answer · 2014-04-09 23:41:39Z

0

you can use SqlDataReader use statement

cmd = new SqlCommand(select sum(quantity) QTY, count(*) from tblStock where prodName=@prodName);

SqlDataReader dr =cmd.ExecuteReader(CommandBehavior.SingleRow);

var Q=dr[0];
var C=dr[1];

answered Apr 9, 2014 at 23:41

Ahmed Galal

7147 silver badges21 bronze badges

Collectives™ on Stack Overflow

c#.Net: Execute two SQL statements at a time

3 Answers 3

6 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Linked

Related