Working with Transactions

These scenarios demonstrate how to create a transaction and then commit or rollback.

Scenario Prototype

public interface ITransactionsScenario<TEmployeeClassification>
   where TEmployeeClassification : class, IEmployeeClassification, new()
{
    /// <summary>
    /// Create a new EmployeeClassification row, returning the new primary key.
    /// </summary>
    /// <param name="classification">The classification.</param>
    /// <param name="shouldRollBack">If True, the pending transaction should be rolled back.</param>
    /// <param name="isolationLevel">The isolation level.</param>
    /// <returns>System.Int32.</returns>
    int Create(TEmployeeClassification classification, bool shouldRollBack);

    /// <summary>
    /// Create a new EmployeeClassification row, returning the new primary key.
    /// </summary>
    /// <param name="classification">The classification.</param>
    /// <param name="shouldRollBack">If True, the pending transaction should be rolled back.</param>
    /// <param name="isolationLevel">The isolation level.</param>
    /// <returns>System.Int32.</returns>
    int CreateWithIsolationLevel(TEmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel);

    /// <summary>
    /// Gets an EmployeeClassification row by its primary key. If no row is found, a null is returned.
    /// </summary>
    TEmployeeClassification? GetByKey(int employeeClassificationKey);
}

ADO.NET

While there is an open transaction against a connection, all commands must be explcitly provided with the transaction object.

public class TransactionsScenario : SqlServerScenarioBase, ITransactionsScenario<EmployeeClassification>
{
    public TransactionsScenario(string connectionString) : base(connectionString)
    { }

    public int Create(EmployeeClassification classification, bool shouldRollBack)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        const string sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )";

        using (var con = OpenConnection())
        using (var trans = con.BeginTransaction())
        using (var cmd = new SqlCommand(sql, con, trans))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
            var result = (int)cmd.ExecuteScalar();

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        const string sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )";

        using (var con = OpenConnection())
        using (var trans = con.BeginTransaction(isolationLevel))
        using (var cmd = new SqlCommand(sql, con, trans))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationName", classification.EmployeeClassificationName);
            var result = (int)cmd.ExecuteScalar();

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        const string sql = @"SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName
                    FROM HR.EmployeeClassification ec
                    WHERE ec.EmployeeClassificationKey = @EmployeeClassificationKey;";

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@EmployeeClassificationKey", employeeClassificationKey);
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                    return null;

                return new EmployeeClassification()
                {
                    EmployeeClassificationKey = reader.GetInt32(reader.GetOrdinal("EmployeeClassificationKey")),
                    EmployeeClassificationName = reader.GetString(reader.GetOrdinal("EmployeeClassificationName"))
                };
            }
        }
    }
}

Chain

When calling DataSource.BeginTransaction(), you get a TransactionalDataSource with the capabilities of the original DataSource.

Unlike a normal DataSource, a TransactionalDataSource holds an open database connection and thus must be disposed after use.

public class TransactionsScenario : ITransactionsScenario<EmployeeClassification>
{
    readonly SqlServerDataSource m_DataSource;

    public TransactionsScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        return m_DataSource.Insert(classification).ToInt32().Execute();
    }

    public int Create(EmployeeClassification classification, bool shouldRollBack)
    {
        using (var trans = m_DataSource.BeginTransaction())
        {
            var result = trans.Insert(classification).ToInt32().Execute();

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        using (var trans = m_DataSource.BeginTransaction(isolationLevel: isolationLevel))
        {
            var result = trans.Insert(classification).ToInt32().Execute();

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        return m_DataSource.GetByKey<EmployeeClassification>(employeeClassificationKey).ToObjectOrNull().Execute();
    }
}

Dapper

While there is an open transaction against a connection, all commands must be explcitly provided with the transaction object.

public class TransactionsScenario : ScenarioBase, ITransactionsScenario<EmployeeClassification>
{
    public TransactionsScenario(string connectionString) : base(connectionString)
    {
    }

    public int Create(EmployeeClassification classification, bool shouldRollBack)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        var sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )";

        using (var con = OpenConnection())
        using (var trans = con.BeginTransaction())
        {
            var result = con.ExecuteScalar<int>(sql, classification, transaction: trans);

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        var sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )";

        using (var con = OpenConnection())
        using (var trans = con.BeginTransaction(isolationLevel))
        {
            var result = con.ExecuteScalar<int>(sql, classification, transaction: trans);

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        using (var con = OpenConnection())
            return con.Get<EmployeeClassification>(employeeClassificationKey);
    }
}

DbConnector

DbConnector will automatically create transactions for all Non-Query executions or when invoking WithIsolationLevel and setting a proper isolation level.

Otherwise, a custom transaction can be used and configured via the Execute functions.

public class TransactionsScenario : ScenarioBase, ITransactionsScenario<EmployeeClassification>
{
    public TransactionsScenario(string connectionString) : base(connectionString)
    {
    }

    public int Create(EmployeeClassification classification, bool shouldRollBack)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        var sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )";

        using (var con = OpenConnection())
        using (var trans = con.BeginTransaction())
        {
            //Set the custom transaction when invoking Execute
            var result = DbConnector.Scalar<int>(sql, classification).Execute(trans);

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        var sql = @"INSERT INTO HR.EmployeeClassification (EmployeeClassificationName)
                    OUTPUT Inserted.EmployeeClassificationKey
                    VALUES(@EmployeeClassificationName )";

        using (var con = OpenConnection())
        using (var trans = con.BeginTransaction(isolationLevel))
        {
            //Set the custom transaction when invoking Execute
            var result = DbConnector.Scalar<int>(sql, classification).Execute(trans);

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        string sql = "SELECT * FROM " + EmployeeClassification.TableName + " WHERE EmployeeClassificationKey = @employeeClassificationKey;";

        return DbConnector.ReadFirstOrDefault<EmployeeClassification>(sql, new { employeeClassificationKey })
                           .WithIsolationLevel(IsolationLevel.ReadCommitted) //Enable the use of a transaction
                           .Execute();
    }
}

Entity Framework 6

Entity Framework will automatically create and commit a transaction when you call SaveChanges(). To override this behavior, you can explicitly create a transaction.

When setting the isolation level, you need to use an extension method defined in Microsoft.EntityFramework.RelationalDatabaseFacadeExtensions.

public class TransactionsScenario : ITransactionsScenario<EmployeeClassification>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public TransactionsScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int Create(EmployeeClassification classification, bool shouldRollBack)
    {
        using (var context = CreateDbContext())
        using (var trans = context.Database.BeginTransaction())
        {
            context.EmployeeClassification.Add(classification);
            context.SaveChanges();
            var result = classification.EmployeeClassificationKey;

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        using (var trans = context.Database.BeginTransaction(isolationLevel))
        {
            context.EmployeeClassification.Add(classification);
            context.SaveChanges();
            var result = classification.EmployeeClassificationKey;

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public EmployeeClassification GetByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
            return context.EmployeeClassification.Find(employeeClassificationKey);
    }
}

Entity Framework Core

EF Core will automatically create and commit a transaction when you call SaveChanges(). To override this behavior, you can explicitly create a transaction.

When setting the isolation level, you need to use an extension method defined in Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.

public class TransactionsScenario : ITransactionsScenario<EmployeeClassification>
{
    private Func<OrmCookbookContext> CreateDbContext;

    public TransactionsScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int Create(EmployeeClassification classification, bool shouldRollBack)
    {
        using (var context = CreateDbContext())
        using (var trans = context.Database.BeginTransaction())
        {
            context.EmployeeClassifications.Add(classification);
            context.SaveChanges();
            var result = classification.EmployeeClassificationKey;

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var context = CreateDbContext())
        using (var trans = context.Database.BeginTransaction(isolationLevel))
        {
            context.EmployeeClassifications.Add(classification);
            context.SaveChanges();
            var result = classification.EmployeeClassificationKey;

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        using (var context = CreateDbContext())
            return context.EmployeeClassifications.Find(employeeClassificationKey);
    }
}

LINQ to DB

While the transaction is open, all operations are automatically associated with the transaction.

public class TransactionsScenario : ITransactionsScenario<EmployeeClassification>
{
    public int Create(EmployeeClassification classification, bool shouldRollBack)
    {
        using (var db = new OrmCookbook())
        using (var trans = db.BeginTransaction())
        {
            var result = db.InsertWithInt32Identity(classification);

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        using (var db = new OrmCookbook())
        using (var trans = db.BeginTransaction(isolationLevel))
        {
            var result = db.InsertWithInt32Identity(classification);

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        using (var db = new OrmCookbook())
        {
            return db.EmployeeClassification.Where(d => d.EmployeeClassificationKey == employeeClassificationKey).SingleOrDefault();
        }
    }
}

LLBLGen Pro

The DataAccessAdapter manages the transaction itself.

public class TransactionsScenario : ITransactionsScenario<EmployeeClassificationEntity>
{
    public int Create(EmployeeClassificationEntity classification, bool shouldRollBack)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var adapter = new DataAccessAdapter())
        {
            adapter.StartTransaction(IsolationLevel.ReadCommitted, null);
            adapter.SaveEntity(classification, true, recurse: false);
            var result = classification.EmployeeClassificationKey;

            if (shouldRollBack)
{
    adapter.Rollback();
}
            else
{
    adapter.Commit();
}
            return result;
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassificationEntity classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var adapter = new DataAccessAdapter())
        {
            adapter.StartTransaction(isolationLevel, null);
            adapter.SaveEntity(classification, true, recurse: false);
            var result = classification.EmployeeClassificationKey;

            if (shouldRollBack)
{
    adapter.Rollback();
}
            else
{
    adapter.Commit();
}
            return result;
        }
    }

    public EmployeeClassificationEntity? GetByKey(int employeeClassificationKey)
    {
        using (var adapter = new DataAccessAdapter())
        {
            return new LinqMetaData(adapter).EmployeeClassification
                                .FirstOrDefault(ec => ec.EmployeeClassificationKey == employeeClassificationKey);
        }
    }
}

NHibernate

While the transaction is open, all operations are automatically associated with the transaction.

public class TransactionsScenario : ITransactionsScenario<EmployeeClassification>
{
    readonly ISessionFactory m_SessionFactory;

    public TransactionsScenario(ISessionFactory sessionFactory)
    {
        m_SessionFactory = sessionFactory;
    }

    public int Create(EmployeeClassification classification, bool shouldRollBack)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var session = m_SessionFactory.OpenSession())
        using (var trans = session.BeginTransaction())
        {
            session.Save(classification);
            session.Flush();
            var result = classification.EmployeeClassificationKey;

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var session = m_SessionFactory.OpenSession())
        using (var trans = session.BeginTransaction(isolationLevel))
        {
            session.Save(classification);
            session.Flush();
            var result = classification.EmployeeClassificationKey;

            if (shouldRollBack)
                trans.Rollback();
            else
                trans.Commit();

            return result;
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        using (var session = m_SessionFactory.OpenStatelessSession())
            return session.Get<EmployeeClassification>(employeeClassificationKey);
    }
}

RepoDb

Simply call the BeginTransaction() method of the DbConnection object and pass the instance of DbTransaction when you are calling any of the operations.

public class TransactionsScenario : BaseRepository<EmployeeClassification, SqlConnection>,
    ITransactionsScenario<EmployeeClassification>
{
    public TransactionsScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public int Create(EmployeeClassification classification, bool shouldRollBack)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var transaction = CreateConnection().EnsureOpen().BeginTransaction())
        {
            var result = Insert<int>(classification, transaction: transaction);

            if (shouldRollBack)
                transaction.Rollback();
            else
                transaction.Commit();

            return result;
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var transaction = CreateConnection().EnsureOpen().BeginTransaction(isolationLevel))
        {
            var result = Insert<int>(classification, transaction: transaction);

            if (shouldRollBack)
                transaction.Rollback();
            else
                transaction.Commit();

            return result;
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        return Query(e => e.EmployeeClassificationKey == employeeClassificationKey).FirstOrDefault();
    }
}

ServiceStack

internal class TransactionsScenario : ITransactionsScenario<EmployeeClassification>
{
    private IDbConnectionFactory _dbConnectionFactory;

    public TransactionsScenario(IDbConnectionFactory dbConnectionFactory)
    {
        this._dbConnectionFactory = dbConnectionFactory;
    }

    public int Create(EmployeeClassification classification, bool shouldRollBack)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            using(var tx = db.OpenTransaction())
            {
                var result = (int) db.Insert(classification, true);

                if (shouldRollBack)
                    tx.Rollback();
                else
                    tx.Commit();

                return result;
            }
        }
    }

    public int CreateWithIsolationLevel(EmployeeClassification classification, bool shouldRollBack, IsolationLevel isolationLevel)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $"{nameof(classification)} is null.");

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            using(var tx = db.OpenTransaction(isolationLevel))
            {
                var result = (int) db.Insert(classification, true);

                if (shouldRollBack)
                    tx.Rollback();
                else
                    tx.Commit();

                return result;
            }
        }
    }

    public EmployeeClassification? GetByKey(int employeeClassificationKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
            return db.SingleById<EmployeeClassification>(employeeClassificationKey);
    }
}