Working with Transactions in .NET DataStore

This document will describe how to work with transactions for CRUD operations (create, read, update and delete) in .NET DataStore.

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:

SnapObjects.Data

SnapObjects.Data.Odbc

SnapObjects.Data.Oracle

SnapObjects.Data.PostgreSql

SnapObjects.Data.SqlServer

DataContext

DataContext in SnapObject is the object used to manage database connections and transactions.

Transaction APIs

//Creates a new transaction for the underlying database with the default isolationLevel
//using CurrentConnection on the context and make it the CurrentTransaction
public virtual IAdoDbTransaction BeginTransaction()

//Creates a new transaction for the underlying database with specified isolationLevel
//using CurrentConnection on the context and make it the CurrentTransaction
public virtual IAdoDbTransaction BeginTransaction(IsolationLevel isolationLevel)

//Commit the CurrentTransaction on the context
public virtual void Commit()

//Rollback the CurrentTransaction 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.

Creating an instance of a SQL Server DataContext

Option 1: Create the instance in the Web API via dependency injection.

//Add this code in ConfigureServices(IServiceCollection services) method in Startup.cs
//SampleDataContext is a user defined class derived from SqlServerDataContext
//Replace the ConnectionString name “Local” with your database connection name
services.AddDataContext<SampleDataContext>(m => m.UseSqlServer(Configuration["ConnectionStrings: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();
}

Creating an instance of an Oracle DataContext

Option 1: Create the instance in the Web API via dependency injection.

//Add this code in ConfigureServices(IServiceCollection services) method in Startup.cs
//SampleDataContext is a user defined class derived from OracleDataContext
//Replace the ConnectionString name “Local” with your database connection name
services.AddDataContext<SampleDataContext>(m => m.UseOracle(Configuration["ConnectionStrings:Local"]));

Option 2: Create the instance manually.

var options = new OracleDataContextOptions(connectionString);
using (var context = new OracleDataContext(options))
{
    context.BeginTransaction();
    //CRUD code here
    context.Commit();
}

Creating an instance of a PostgreSQL DataContext

Option 1: Create the instance in Web API via dependency injection.

//Add this code in ConfigureServices(IServiceCollection services) method in Startup.cs
//SampleDataContext is a user defined class derived from PostgreSqlDataContext
//Replace the ConnectionString name “Local” with your database connection name
services.AddDataContext<SampleDataContext>(m => m.UsePostgreSql(Configuration["ConnectionStrings:Local"]));

Option 2: Create the instance manually.

var options = new PostgreSqlDataContextOptions(connectionString);
using (var context = new PostgreSqlDataContext(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 yourself 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.

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 DataContext's CurrentTransaction.

DataContext.Rollback() rollbacks DataContext's CurrentTransaction.

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();
}