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.

score 33 · Accepted Answer · edited Feb 07 '18 at 13:48

33

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)

edited Feb 07 '18 at 13:48

Liam

27,717
28
128
190

answered Aug 01 '11 at 20:16

marcusstarnes

6,393
14
65
112

1

`Connection.Query("sqlQuery",param: new { id= ID}, transaction: Transaction)` – vaheeds Jul 15 '17 at 16:31
1

If your query isn't using any parameters (say, `select * from table`), if you just pass the `transaction` dapper will interpret it as a parameter object. So the above syntax using named optional parameters will prevent that. – Kemuel Sanchez Apr 22 '21 at 16:12
I had used commandDefinition, so used the below. `CommandDefinition command = new CommandDefinition(storedProcedureName, dynamicParameters, commandTimeout: commandTimeout, commandType: commandType, transaction: _dbTransaction);` – Sivarajan Sivanesan Oct 11 '21 at 09:47

score 5 · Answer 2 · edited Aug 24 '23 at 07:35

5

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

edited Aug 24 '23 at 07:35

Adriaan

17,741
7
42
75

answered Jul 31 '11 at 20:12

Oliver Weichhold

10,259
5
45
87

1

Thanks for the reply, Oliver. Unfortunately it looks like Dapper.net doesn't support TransactionScope, just IDbTransaction :( – marcusstarnes Jul 31 '11 at 20:28
@marcusstarnes are you sure? its supposed to work. Just dont use BeginTransaction and Commit. transactionScope will handle rest – adt Jul 31 '11 at 20:40
4

Dapper is absolutely agnostic to transactions. I'm using TransactionScope in conjunction with Dapper with absolutely zero problems. – Oliver Weichhold Jul 31 '11 at 20:44
That's because TransactionScope, by design, affects all connections automatically. It's not that Dapper has something specific to transactions. – ashes999 Oct 04 '13 at 14:20
This brings errors of its own when using with dapper - Enlisting in Ambient transactions is not supported (feb 2019). – Spikolynn Feb 19 '19 at 07:44
This should work in .NET Framework, but won't work in .NET Core until there is a resolution to https://github.com/dotnet/runtime/issues/715 – Rob Johnston Jul 23 '20 at 18:06
transactionScope is distributed and therefore configuring them when the database is not on the same server is quite a handful. I understand is for distributed transactions – Tonto Jul 12 '23 at 05:43

score 0 · Answer 3 · answered Jun 14 '23 at 15:55

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.

Dapper.net transaction problem

3 Answers3