BeginTransaction not calling BEGIN TRANSACTION

Question

I've got a simple bit of code that uses BeginTransaction(). The resulting transaction is assigned to the connection that I'm using for some sql commands.

When I profile the resulting sql, I don't see a BEGIN TRANSACTION at any point. What might be happening that would prevent the transaction from being used?

Answer 1

Transactions are handled at a lower level when using ADO.NET. There are no "BEGIN TRANSACTION" statements sent to the server.

Answer 2

You need to ensure that you not only set the transaction on the connection object, but you also need to assign the transaction into the sqlCommand.

See this codeproject article for an example.

Answer 3

To reiterate Philippe's statement:

Transactions are handled at a lower level when using ADO.NET. There are no "BEGIN TRANSACTION" statements sent to the server.

At some point SQL has to be converted into actual calls. Most ADO.NET (all that I've worked with) often send a database specific command to BEGIN, COMMIT, and ROLLBACK transactions as sending ASCII (or whatever else) would be less efficient than something the server will have to parse.

This is why sending parameterised queries are often faster than pure SQL based ones as the library can send specific commands which results in less parsing and probably less data validation (?).

HTH!