Implicit transaction for a page request?

If I execute multiple SqlCommands in a single .aspx, using a single SqlConnection, are all the commands part of the same transaction?

That is, if I say:

cmd1.CommandText = "UPDATE foo SET myVal = myVal - 1"
cmd2.CommandText = "SELECT myVal FROM foo"
cmd1.Connection = myConn
cmd2.Connection = myConn
myConn.Open()
cmd1.ExecuteNonQuery() ' decrement myVal
intResult = CInt(cmd2.ExecuteScalar()) ' select value of myVal
myConn.Close()

Do the UPDATE and the SELECT happen in the same transaction, or is it possible that someone else would have changed the value of myVal between these two calls?Nope. Each statement has an implicit transaction but to put them both under a single transaction you have to use an explicit transaction.

So yes, data could have changed between the statements.

Don|||Thanks for the clarification. So if I understand you right, this:

cmd.CommandText = "UPDATE ... ; SELECT @.myResult = ... "
cmd.ExecuteNonQuery()

would put them in the same transaction, which is what I want.|||Now I'm on shakier ground. I'm pretty sure that SQL Server still considers them to be separate statements, simply batched together. But not absolutely positive.

Any reason you don't want to just use an explicit transaction?

Don|||No -- I'd like to use an explicit transaction, but I'm not sure how. Can you give me an example of how to do it w/o moving the queries to a stored procedure?

Thanks!|||Well, you're opening yourself up to all kinds of security holes by using dynamic SQL like this. Stored procedures are much better way to go.

But you can either use transactions via dynamic SQL and batch statements, or using the SQLTransaction class in ADO.NET. The example in the .NET docs is pretty clear. For the latter, just new up a SQLTransaction object and use the connection object to begin the transaction. Then call either the Commit or Rollback methods of the transaction object.

But remember that your app won't be very secure unless you take all the steps to close the holes.

Don|||Thanks, Don. What kind of hacks am I risking? I'm using parameters to avoid sql-insertion attacks; are there other risks that come with using SqlCommands?|||Ah, you're using ADO.NET parameters? That wasn't clear from the snippets you've posted, although you had one @.Result showing. You're using parameters to provide the changeable values in the SQL, right?

If that's the case, you're probaly reasonably well protected from SQL injection, since ADO.NET passes the SQL and parameters separately to SQL Server, where they are inserted into the SQL.

Don