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

there is no need for it to be atomic or transactional. I just want to perform a set of operations. I just want to be sure everyone of them got done — Toad
– Toad, Commented Oct 1, 2009 at 13:10

Russ Cam · Accepted Answer · 2014-03-25 20:43:20Z

9

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.

edited Mar 25, 2014 at 20:43

answered Oct 1, 2009 at 13:15

Russ Cam

126k34 gold badges211 silver badges275 bronze badges

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

4 Comments

Toad Over a year ago

ok thanks... no need for a transaction for me, since I don't care if one opf them gets done if one fails...I just have to know if any of them fails... Do you know how this is handled? If anyone of them fails, am I notified?

Russ Cam Over a year ago

when you say fails, do you mean a syntactical error in the sql statement? Or do you mean, as in the example statement given, if one of two UPDATE statements doesn't update any rows? The former will throw a SqlException however many statements you have, with the latter I think that you would still need to keep the statements separate in order to assess the return value of ExecuteScalar()/ExecuteNonQuery()

Toad Over a year ago

the latter... ok... so you are saying that in that case I'd either have to separate the queries or use a transaction right?

Russ Cam Over a year ago

For the latter, as far as the database is concerned, an UPDATE affecting no rows is not an error. It may be an error for your application logic however. There are a couple of ways to handle this that I can think of off the top of my head, which may involve a transaction depending on whether the statements are all or nothing. You could use a return value or use RAISERROR to return a message to the calling application.

Collectives™ on Stack Overflow

Multiple queries using one SqlCommand possible?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related