CancellationToken support

[TheAngryByrd]

Hey there!

Problem statement

I started playing with this library a bit more in depth and one of the things missing for me is CancellationToken support. The reason passing down a CancellationToken to is so database connections don't stay open longer than they need for.

For example, a user could create a complex search, start the query, then decide to do something else, and move away from the page. Without passing along the CancellationToken to the database query, it may run in the background for some time, consuming resources.

Changes

It looks like query1 and execute in IDbConnection.fs would need to accept a CancellationToken. Since this is an internal module it should be a safe change.

Example of the change proposed:

let query1<'a> (this:IDbConnection) trans timeout cancellationToken (logFunction:LogFn option) (query, pars) =
    if logFunction.IsSome then (query, pars) |> logFunction.Value
    CommandDefinition(query, pars, ?transaction = trans, ?commandTimeout = timeout, ?cancellationToken = cancellationToken)
    |> this.QueryAsync<'a>

Additionally the providers will need to add optional parameters to the public queries such as SelectAsync

    member this.SelectAsync<'a> (q:SelectQuery, ?trans:IDbTransaction, ?timeout:int, ?logFunction, ?cancellationToken : CancellationToken) =
        q |> Deconstructor.select<'a> |> IDbConnection.query1<'a> this trans timeout cancellationToken logFunction

Since this is an optional parameter, this should not be a breaking change.

I could not find support for query2 because in dapper the underlying calls don't support a CancellationToken so this will remain the same unfortunately.

[Dzoukr]

I knew it! I knew your Twitter rant about F# libs missing support for CancellationToken was about me 😄 As I already commented in PR - I am absolutely open to anything that makes it better and pass the tests (which should, because as you said - it's not a breaking change). Thanks for the work and let me know when ready to merge. 🙏

[Dzoukr]

Aaaaaaaaaaaaand published as v3.2.0 Thanks again!!!

[no1melman]

@Dzoukr So how do you actually pass a cancellation token given

let queryTable (conn: SqlConnection) (ct: CancellationToken) = 
    select {
        for f in filesProcessTable do
            selectAll
    } |> conn.SelectAsync<File>

[JordanMarr]

@Dzoukr So how do you actually pass a cancellation token given

You should be able to do something like this:

let queryTable (conn: SqlConnection) (ct: CancellationToken) = 
    let query = 
        select {
            for f in filesProcessTable do
                selectAll
        }

    conn.SelectAsync<File>(query, cancellationToken = ct)