BitBadger.Npgsql.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 NpgsqlDataSource object has an OpenConnectionAsync() method which returns an open database connection; this connection can be used to start a transaction. (There is also a synchronous version.)
// C# using var conn = await Configuration.DataSource().OpenConnectionAsync(); using var txn = await conn.BeginTransactionAsync(); try { // do stuff await txn.CommitAsync(); } catch (Exception ex) { await txn.RollbackAsync(); // more error handling }
// F# use! conn = Configuration.DataSource().OpenConnectionAsync () use! txn = conn.BeginTransactionAsync () try // do stuff do! txn.CommitAsync () with ex -> do! txt.RollbackAsync () // more error handling
Providing This Connection to Queries
The above gets us a connection against which the transaction is being executed; but how do we tell the library about that connection? The answer is a static class or module we have yet to discuss - WithProps. Npgsql.FSharp uses SqlProps to build up a query which is then executed; this was covered a bit when we discussed related documents. WithProps contains all of the function definitions covered up to this point in the documentation, with SqlProps as the final parameter for the call.
First, we need SqlProps that know about our connection:
// C# var props = Sql.existingConnection(conn);
// F# let props = Sql.existingConnection conn
Then, we can pass these props to the queries. 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# using var conn = await Configuration.DataSource().OpenConnectionAsync(); using var txn = await conn.BeginTransactionAsync(); var props = Sql.existingConnection(conn); try { WithProps.Update.PartialById("user_table", userId, new { LastSeen = DateTime.Now }, props); WithProps.Update.PartialById("security", userId, new { FailedLogOnCount = 0 }, props); await txn.CommitAsync(); } catch (Exception ex) { await txn.RollbackAsync(); // more error handling }
// F# use! conn = Configuration.dataSource().OpenConnectionAsync () use! txn = conn.BeginTransactionAsync () let props = Sql.existingConnection conn try WithProps.Update.partialById "user_table" userId {| LastSeen = DateTime.Now |} props WithProps.Update.partialById "security" userId {| FailedLogOnCount = 0 |} props do! txn.CommitAsync () with ex -> do! txn.RollbackAsync () // more error handling
WithProps can be used as the default, with either using static BitBadger.Npgsql.Documents.Document.WithProps; in C# or open BitBadger.Npgsql.FSharp.Documents.WithProps in F#. In most cases, though, using it as the examples above do will be preferred; this allows the library to use its own connection handling for retrieval queries.