CancellationToken with async Dapper methods?

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.