Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple queries using one SqlCommand possible?

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

like image 422
Toad Avatar asked Jan 24 '23 03:01

Toad


1 Answers

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.

like image 156
Russ Cam Avatar answered Feb 01 '23 11:02

Russ Cam