Dapper.net transaction problem

Question

I'm trying to commit a transaction to my Sql Server 2008 database - firstly 2 insert's followed by a couple update's, however, as soon as it attempts to execute the first of the update's, I get the following error:

ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

Here's the code, edited slightly for brevity:

using (_cn) {     _cn.Open();     IDbTransaction transaction = _cn.BeginTransaction();     topicId = (int)_cn.Query<decimal>(qAddTopic, new { pForumId = topic.ForumId }, transaction).Single();     postId = (int)_cn.Query<decimal>(qAddPost, new { pTopicId = topicId }, transaction).Single();      _cn.Execute(qUpdateForums, new { pLastPostId = postId });     _cn.Execute((qUpdateSiteTotals));      transaction.Commit(); } 

The first 2 inserts work fine, but as soon as it tries to perform one of the updates, no joy.

Answer 1

I have found the problem - I was simply missing the transaction param when I was calling the updates, whereas with the previous inserts that were working fine, I had included the IDbTransaction param! My bad!

Example:

Connection.Query<Entitiy>("sqlQuery",param: new { id= ID}, transaction: Transaction) 

Answer 2

Microsoft recommends to use TransactionScope over database IDbTransaction when possible. The following code should work, assuming that nothing's wrong with your SQL and the managed provider automatically enlists in the ambient transaction - something that well-behaved providers need to do.

using (var ts = new TransactionScope()) {   using (_cn)   {     _cn.Open();     ...   }    ts.complete(); }