Transactions carrying over after an ExecuteNonQuery?

Question

Hello, I've discovered something pretty amazing that works recently. I am wanting to know if it is "correct" and if it should work "everywhere." (as, it's not just a coincidence and it's supported as some sort of standard)

Well, I can have code like this...

IDbCommand cmd=new ....; //this changes depending on if I'm using Sql Server or PostgreSQL
cmd.Connection=connection;
cmd.CommandText="begin;" //postgresql keyword to begin a transaction
cmd.ExecuteNonQuery();
cmd.CommandText=...
cmd.Execute...
.....
//later(without changing cmd's connection)..
cmd.CommandText="commit;"; //postgresql keyword to commit the open transaction.
cmd.ExecuteNonQuery();
connection.Close();

Well, I am wanting to know if this will always work. I was under the impression that each query in a Command was only in the scope of that command, such that something like this:

cmd.CommandText="declare @i int;"; //sql server code to create a temporary variable
cmd.Execute..
cmd.CommandText="set @i=0;"; //set the temporary variable to 0
cmd.Execute...

I thought this would fail with something like @i not declared because of scope. So is this true that transactions are different, or is this database dependent?

(also, sorry to comment the keywords and such. I'm wanting for this to be answerable by people familiar with just generic SQL)

Answer 1

Yup, this is just fine. If you're using the same connection (rather than returning it to the connection pool), then this is expected behaviour.

it's also how Integration Services does transactions.