CODEWITHSUNDEEP
sql.net-2.0transactions
ilivewithian
ilivewithian

Reputation: 19702

BeginTransaction not calling BEGIN TRANSACTION

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?

Upvotes: 2

Views: 919

Answers (3)

mimetnet
mimetnet

Reputation: 657

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!

Upvotes: 0

Jayden
Jayden

Reputation: 2778

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.

Upvotes: 0

Philippe Leybaert
Philippe Leybaert

Reputation: 171864

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

Upvotes: 2

Related Questions