Multiple queries using one SqlCommand possible?

Question

Is it possible to add multiple queries to the SqlCommand object in .net?

And if one of the queries fails and the rest succeeds, does the command report this as a failure or succes?

last question: can the queries share the same parameters? So, is this possible:

 UPDATE mytable 
    SET column1 = @param1 
  WHERE recid = @recid; 

 UPDATE mytable2 
    SET column1 = @param1 
  WHERE recid = @recid2;       

(so the param1 is shared between the queries)

thanks!

R

Answer 1

You can have multiple statements in the CommandText of a SqlCommand object, that's not a problem, and they can share parameters too. It essentially gets passed to sp_executesql on sql server and executed. If you're doing multiple UPDATEs then you'll want to do them inside a transaction so that you can rollback if there is a failure in one of the statements.

If returning multiple recordsets, you can use SqlDataReader's NextResult() method to get the next resultset.