Working with Transactions in .NET DataStore
Last Updated: February 2023
Introduction
This document will describe how to work with transactions for CRUD operations (create, read, update, and delete) in .NET DataStore (DWNet.Data).
Dependencies
Because .NET DataStore explicitly and/or implicitly uses SnapObjects DataContext's transaction APIs to manage transactions, using transactions in .NET DataStore has dependencies on the following SnapObjects NuGet packages:
DataContext
DataContext in SnapObject is the object used to manage database connections and transactions.
Transaction APIs
BeginTransaction()
-- Creates a new transaction for the underlying database with the default isolation level using the current connection on the context and makes it the current transaction.
public virtual IAdoDbTransaction BeginTransaction()
BeginTransaction(IsolationLevel)
-- Creates a new transaction for the underlying database with the specified isolation level using the current connection on the context and makes it the current transaction.
public virtual IAdoDbTransaction BeginTransaction(IsolationLevel isolationLevel)
Commit()
-- Commits the current transaction on the context.
public virtual void Commit()
Rollback()
-- Rolls back the current transaction on the context.
public virtual void Rollback()
Creating an instance of DataContext
DataContext is an abstract class, so you cannot directly instantiate a DataContext. Rather, you have to instantiate a database-specific DataContext. When a DataContext is instantiated, a database connection is automatically created and the connection is set as the DataContext's CurrentConnection.
The following code examples create an instance of DataContext for SQL Server. You can use the same instructions with the corresponding methods to create an instance for the other database types including Oracle, PostgreSQL, MySQL, SQLite, ODBC etc.
Option 1: Create the instance in the Web API via dependency injection.
//Add this code after builder.Services.AddSwaggerGen(); in Program.cs
//SampleDataContext is a user defined class derived from SqlServerDataContext
//Replace the ConnectionString name “Local” with your database connection name
builder.services.AddDataContext<SampleDataContext>(m => m.UseSqlServer(this.Configuration, "Local"));
Option 2: Create the instance manually.
var options = new SqlServerDataContextOptions(connectionString);
using (var context = new SqlServerDataContext(options))
{
context.BeginTransaction();
//CRUD code here
context.Commit();
}
How Transactions Work in .NET DataStore
Transactions in .NET DataStore can be either explicit or implicit.
An explicit transaction is one that you explicitly create by using SnapObjects context.BeginTransaction(). It supports full CRUD operations (including retrieve). If anything in the transaction fails then your code would dictate whether to roll back the transaction. An explicit transaction can consist of multiple .NET DataStore's Update() and Retrieve().
An implicit transaction inherently includes all the CUD operations on the model until a commit method is executed. An implicit transaction in .NET DataStore takes an Update() as the commit method. If any CUD operation fails, that particular Update() would be automatically rolled back.
In addition, the Track
method in SqlModelMapper can now track CRUD operations with .NET DataStore. When you execute the SqlModelMapper's SaveChanges() after the tracking, it will execute the .NET DataStore's Update
method to commit the operation.
Transactions Using DataContext Transaction APIs
DataContext.BeginTransaction() has two overrides – one takes as an argument an explicit IsolationLevel, and one takes no arguments thus using the default IsolationLevel from the underlying database provider. Both overrides return an AdoDbTransaction object with Commit() and Rollback() methods that perform the commit or rollback for the underlying transaction (DataContext's CurrentTransaction).
DataContext.Commit() commits the DataContext's current transaction.
DataContext.Rollback() rolls back the DataContext's current transaction.
The transaction is meant to be disposed once it has been committed or rolled back.
Basic transaction example (pseudo code):
_context.BeginTransaction();
try
{
//update datastore1
dataStore1.Update();
//update datastore2
dataStore2.Update();
_context.Commit();
}
catch (Exception)
{
_context.Rollback();
}