62

I'm using Dapper 1.31 from Nuget. I have this very simple code snippet,

string connString = "";
string query = "";
int val = 0;
CancellationTokenSource tokenSource = new CancellationTokenSource();
using (IDbConnection conn = new SqlConnection(connString))
{
    conn.Open();
    val = (await conn.QueryAsync<int>(query, tokenSource.Token)).FirstOrDefault();
}

When I press F12 on QueryAsync, it points me to

public static Task<IEnumerable<T>> QueryAsync<T>
     (
        this IDbConnection cnn, 
        string sql, 
        dynamic param = null, 
        IDbTransaction transaction = null, 
        int? commandTimeout = null, 
        CommandType? commandType = null
     );

There is no CancellationToken on its signature.

Questions:

  • Why is the snippet completely buildable assuming that there is no compiler error on the whole solution?
  • Forgive me that I cannot test if calling tokenSource.Cancel() would really cancel the method because I don't know how to generate a long running sql query. Will the .Cancel() really cancel the method and throw OperationCancelledException?

Thank you!

Palle Due
  • 5,929
  • 4
  • 17
  • 32
Pedigree
  • 2,384
  • 3
  • 23
  • 28
  • 1
    `dynamic param` will take pretty much anything. What you're doing is kind of like passing a cancellation token as a parameter to `Console.WriteLine(string, params object[])`. Just because you can pass it doesn't mean the function supports cancellation. – ta.speot.is Aug 28 '14 at 04:46

4 Answers4

108

You are passing the cancellation token as the parameter object; that won't work.

The first async methods in dapper did not expose a cancellation token; when I tried to add them as an optional parameter (as a separate overload, to avoid breaking existing assemblies), things got very confused with "ambiguous method" compilation problems. Consequently, I had to expose this via a separate API; enter CommandDefinition:

val = (await conn.QueryAsync<int>(
    new CommandDefinition(query, cancellationToken: tokenSource.Token)
).FirstOrDefault();

This then passes the cancellation-token down the chain to all the expected places; it is the job of the ADO.NET provider to actually use it, but; it seems to work in most cases. Note that it can result in a SqlException rather than an OperationCancelledException if the operation is in progress; this again is down to the ADO.NET provider, but makes a lot of sense: you could have interrupted something important; it surfaces as a critical connection issue.

As for the questions:

Why is the snippet completely buildable assuming that there is no compiler error on the whole solution?

Because... it is valid C#, even if it doesn't do what you expect.

Forgive me as I cannot test if calling tokenSource.Cancel() would really cancel the method because I don't know how to generate long running sql query. Will the .Cancel() really cancels the method and throws OperationCancelledException?

ADO.NET provider-specific, but yes it usually works. As an example of "how to generate long running sql query"; the waitfor delay command on SQL server is somewhat useful here, and is what I use in the integration tests.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 2
    This is a great answer from the author itself. Thanks Marc for the alternative API! Well, I'm fine with catching `SqlException` rather than `OperationCancelledException`, I will handle the logic here. Thanks again! – Pedigree Aug 30 '14 at 07:34
  • 1
    @Pedigree I *think* I've also figured out how to make it work correctly in the primary API too. – Marc Gravell Aug 30 '14 at 08:18
  • I have another question marc and I don't know if this is still in the scope, why do you have set `.ConfigureAwait(false)` on *line 78* of [file: SqlMapperAsync.cs](https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper%20NET45/SqlMapperAsync.cs)? What does it really mean? Thanks. – Pedigree Aug 30 '14 at 11:39
  • 1
    @Pedigree it is standard in library code; it means it doesn't need to go to the sync-context - it doesn't depend on anything like adp.net context etc. Library code should almost always use it; application code usually *should* use sync-context, so should usually omit it. In fact, if library code forgets to do this, it can sometimes cause hard deadlocks (depending on what the calling application code does) – Marc Gravell Aug 30 '14 at 11:44
  • Hi Marc. I have another followup question. Is this acceptable? `await SqlMapper.ExecuteAsync(connection, new CommandDefinition(sqlStatement, param, cancellationToken: token));` where ***param*** is the list of parameters? I'm confused why you use `object parameters = null` rather than `dynamic param = null` in the `CommandDefinition`. Does it have any difference? – Pedigree Sep 05 '14 at 10:37
  • @Pedigree none whatsoever; our use of `param` doesn't actually use `dynamic`; at the IL level, `dynamic` === `object`. It is only compiler magic that does things like `obj.Whatever()` in a special way, and *we don't use that* – Marc Gravell Sep 05 '14 at 10:44
  • 1
    @Pedigree in the next release of dapper, `cancellationToken` may be available on the original API; but today: it isn't – Marc Gravell Sep 05 '14 at 10:48
  • so whatever I use either *object* or *dynamic*, the result is the same. Cool! Will it correctly throws `OperationCancelledException`then? – Pedigree Sep 05 '14 at 10:52
  • 1
    @Pedigree that is a question for ADO.NET; as I already mentioned, it *may* surface as `SqlException` if the operation was in progress – Marc Gravell Sep 05 '14 at 10:53
  • 1
    @MarcGravell why is there no support for `CancellationToken` on the `ExecuteAsync` method? I notice it builds a `CommandDefinition` in the background and sets its `CancellationToken = default`. – Matt Cotton Sep 07 '22 at 10:25
-1

You can fix SqlMapper.cs in Dapper lib by adding these lines:

    internal IDbCommand SetupCommand(IDbConnection cnn, Action<IDbCommand, object> paramReader)
    {
        var cmd = cnn.CreateCommand();

#if ASYNC
        // We will cancel our IDbCommand
        CancellationToken.Register(() => cmd.Cancel());
#endif

Rebuild your own Dapper lib and enjoy :)

rislanov
  • 23
  • 2
-1

try using a SqlConnection and catch the exception on cancel

var sqlConn = db.Database.Connection as SqlConnection;
sqlConn.Open();

_cmd = new SqlCommand(textCommand, sqlConn);
_cmd.ExecuteNonQuery();

and cancel the SqlCommand

_cmd.Cancel();
Matt
  • 2,096
  • 14
  • 20
-4

I was using one SqlConnection for multiple threads. And then when I changed it so that each Thread created it's own SqlConnection the error disappeared.

Tadej
  • 553
  • 7
  • 16