BitBadger.Documents
Advanced Usage: Transactions
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.