CODEWITHSUNDEEP
asp.netsql-server-2008ormdapper
marcusstarnes
marcusstarnes

Reputation: 6531

Dapper.net transaction problem

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.

Upvotes: 21

Views: 14877

Answers (3)

Oliver Weichhold
Oliver Weichhold

Reputation: 10296

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();
}

Upvotes: 5

Andrei Bazanov
Andrei Bazanov

Reputation: 352

Here is what the problem was in my case. I had this code and it all looked fine.

public async Task ClearCurrentBasePriceFile(IDbTransaction transaction = null)
{
    var connection = transaction?.Connection ?? _db;

    await connection.ExecuteAsync(@"
        DELETE
        FROM PRICE_LIST_BASE",
        transaction
    ).ConfigureAwait(false);
}

I am passing in the transaction. I am certain the transaction is not null. But it would still give me the error.

Turns out, if I add null in front of the transaction parameter, where the param parameter should be, then it works. It seems that it was taking the transaction parameter as the param parameter and hence it was thinking that I was not supplying the transaction.

Upvotes: 0

marcusstarnes
marcusstarnes

Reputation: 6531

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)

Upvotes: 34

Related Questions