Working with Transactions in SnapObjects

This document will describe using transactions in SnapObjects. SnapObjects provides SqlModelMapper and SqlExecutor objects for performing CRUD operations to the database. All those objects explicitly and/or implicitly use DataContext's transaction APIs to manage transactions.

DataContext

DataContext 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 SnapObjects

Transactions in SnapObjects can be either explicit or implicit.

An explicit transaction is one that you explicitly create by using context.BeginTransaction() . It supports full CRUD operations on the model (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 SqlModelMapper's SaveChanges(), and/or SqlExecutor's Execute() and/or ExecuteProcedure().

An implicit transaction inherently includes all the CUD operations on the model until a commit method is executed.

The implicit transaction in SqlModelMapper takes a SaveChanges() as the commit method. If any operation in the transaction fails, the SaveChanges() would be automatically rolled back.

Different from SqlModelMapper and SQLExcutor, transactions in SqlModelMapper (no matter explicit or implicit) can include not only CRUD operations, but also SQL statements and even C# operations. What is included in a SqlModelMapper transaction (no matter explicit or implicit) are identified by a variety of "track" methods. For example, to "track" SQL statements you would use TrackSqlCUD(sqlText, parameters). Because the SqlModelMapper's SaveChanges() encompasses everything that you "track" instead of just the CUD operations on the model, when you execute the SqlModelMapper's SaveChanges(), it in turn executes everything that has been tracked up to that point.
The implicit transaction in SqlExecutor takes an Execute() or ExecuteProcedure() as the commit method. If the particular Execute() or ExecuteProcedure() fails, then it 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
{
    // Execute raw Create/Update/Delete sql1
    _context.SqlExecutor.Execute("Raw Create/Update/Delete sql1");
    
    // Execute raw Create/Update/Delete sql2
    _context.SqlExecutor.Execute("Raw Create/Update/Delete sql2");
    
    _context.Commit();
}
catch (Exception)
{
    _context.Rollback();
}

Transaction example with multiple data operations and objects (pseudo code):

_context.BeginTransaction();

try
{
    //execute a Create/Update/Delete sql
    _context.SqlExecutor.Execute("Raw Create/Update/Delete sql");
    
    //data changes by SqlModelMapper
    _context.SqlModelMapper.TrackCreate(departmentModel);
    _context.SqlModelMapper.TrackDelete(employeeModel);
    _context.SqlModelMapper.SaveChanges();
    
    //all data operations above will be executed in one transaction
    _context.Commit();
}
catch (Exception)
{
    _context.Rollback();
}

Transactions Using SqlModelMapper

Execution process of SqlModelMapper.SaveChanges()

When the SqlModelMapper.SaveChanges() is executed the SqlModelMapper object will go through a 4-step process as follows:

Checks to determine if the transaction is explicit or implicit. If a transaction is already created then the instance of SaveChanges() is included in that (explicit) transaction; if no, then it creates a new (implicit) transaction.
Goes through and executes each tracked item one by one. If the tracked item is a model create/update/delete then it generates and executes the appropriate SQL. If the tracked item is a SQL create/update/delete then it executes the specified SQL statement. If the tracked item is an action then it executes the specified action. All the tracked items are executed in a single batch and become part of the explicit or implicit transaction mentioned in step 1.
Decides whether to commit or rollback the transaction (for implicit transactions only since explicit transactions are handled by the programmer). If all tracked items in step 2 executed successfully then it commits the implicit transaction; otherwise, it rolls back the implicit transaction.
Clears the list of all tracked items associated with the particular SaveChanges().

Implicit transaction example (pseudo code):

No transaction API is needed; however, each operation to be executed (when the SaveChanges() is called) must be tracked. The transaction APIs are called internally by the SnapObjects framework to create and commit/rollback transactions when the SaveChanges() is called.

//add to tracked list only, no execution
_context.SqlModelMapper.TrackCreate(departmentModel); 
//add to tracked list only, no execution
_context.SqlModelMapper.TrackUpdate(employeeModel);
employeeModel.Score = 5;
//add to tracked list only, no execution
_context.SqlModelMapper.TrackSqlCUD("Raw Create/Update/Delete sql");

//create a new transaction, execute operations above, and commit or rollback transaction
_context.SqlModelMapper.SaveChanges();

The implicit transaction only works for tracked items of the SqlModelMapper (i.e. operations performed by the SqlModelMapper's SaveChanges()). In the following example, the SQL statement is executed in a separate transaction.

//add to tracked list only, no execution
_context.SqlModelMapper.TrackCreate(departmentModel); 
//add to tracked list only, no execution
_context.SqlModelMapper.TrackUpdate(employeeModel);
employeeModel.Score = 5;

//using SqlExecutor to execute SQL, it will run in a separate transaction
_context.SqlExecutor.Execute("Raw Create/Update/Delete sql");

//create a new transaction, execute operations above exception for the SQL, and commit or rollback transaction
_context.SqlModelMapper.SaveChanges();