Transactions

Documentation pages for BitBadger.Npgsql.Documents redirect here. This library replaced it as of v3; see project home if this applies to you.

On occasion, there may be a need to perform multiple updates in a single database transaction, where either all updates succeed, or none do.

Controlling Database Transactions

The Configuration static class/module of each library provides a way to obtain a connection. Whatever strategy your application uses to obtain the connection, the connection object is how ADO.NET implements transactions.

// C#, All
// "conn" is assumed to be either NpgsqlConnection or SqliteConnection
await using var txn = await conn.BeginTransactionAsync();
try
{
    // do stuff
    await txn.CommitAsync();
}
catch (Exception ex)
{
    await txn.RollbackAsync();
    // more error handling
}

// F#, All
// "conn" is assumed to be either NpgsqlConnection or SqliteConnection
use! txn = conn.BeginTransactionAsync ()
try
    // do stuff
    do! txn.CommitAsync ()
with ex ->
    do! txt.RollbackAsync ()
    // more error handling

Executing Queries on the Connection

This precise scenario was the reason that all methods and functions are implemented on the connection object; all extensions execute the commands in the context of the connection. Imagine an application where a user signs in. We may want to set an attribute on the user record that says that now is the last time they signed in; and we may also want to reset a failed logon counter, as they have successfully signed in. This would look like:

// C#, All ("conn" is our connection object)
await using var txn = await conn.BeginTransactionAsync();
try
{
    await conn.PatchById("user_table", userId, new { LastSeen = DateTime.Now });
    await conn.PatchById("security", userId, new { FailedLogOnCount = 0 });
    await txn.CommitAsync();
}
catch (Exception ex)
{
    await txn.RollbackAsync();
    // more error handling
}

// F#, All ("conn" is our connection object)
use! txn = conn.BeginTransactionAsync()
try
    do! conn.patchById "user_table" userId {| LastSeen = DateTime.Now |}
    do! conn.patchById "security"   userId {| FailedLogOnCount = 0 |}
    do! txn.CommitAsync()
with ex ->
    do! txn.RollbackAsync()
    // more error handling

A Functional Alternative

The PostgreSQL library has a static class/module called WithProps; the SQLite library has a static class/module called WithConn. Each of these accept the SqlProps or SqliteConnection parameter as the last parameter of the query. For SQLite, we need nothing else to pass the connection to these methods/functions; for PostgreSQL, though, we'll need to create a SqlProps object based off the connection.

// C#, PostgreSQL
using Npgsql.FSharp;
// ...
var props = Sql.existingConnection(conn);
// ...
await WithProps.Patch.ById("user_table", userId, new { LastSeen = DateTime.Now }, props);

// F#, PostgreSQL
open Npgsql.FSharp
// ...
let props = Sql.existingConnection conn
// ...
do! WithProps.Patch.ById "user_table" userId {| LastSeen = DateTime.Now |} props

If we do not want to qualify with WithProps or WithConn, C# users can add using static [WithProps|WithConn]; to bring these functions into scope; F# users can add open BitBadger.Documents.[Postgres|Sqlite].[WithProps|WithConn] to bring them into scope. However, in C#, this will affect the entire file, and in F#, it will affect the file from that point through the end of the file. Unless you want to go all-in with the connection-last functions, it is probably better to qualify the occasional call.