What is the best way to pass a SQL Server transaction as a parameter

Question

I am aware of the solution to implement SQL Server transactions in .net C# with the "using" keyword and with code like this:

InsertDetails()
{
    using (TransactionScope ts = new TransactionScope()) 
    {    
        InsertName();//SQL functions to insert name into name table   
        Insertaddress();//SQL functions to insert address into address table
        InsertPhoneNo();//SQL functions to insert phone number into contact table

        ts.Complete();    
    }    
}

But say for example I wished to instead pass the sql server transaction as a parameter to many different functions for different database queries, without having the using statement example.

After calling all the functions in the code path I would then like to make a call to commit the data and if something went wrong then perform a rollback.

Pseudo code would look like this

InsertDetails()
{
    var transaction = new Transaction();
    var sqlcon = new SqlConnection();    
        InsertName(transaction, sqlcon);//SQL functions to insert name into name table  
        Insertaddress(transaction, sqlcon);//SQL functions to insert address into address table
        InsertPhoneNo(transaction, sqlcon);//code to insert phone no into contact table
        try
        {
            ts.commit();       
        }
        catch(Exception ex)
        {
            ts.rollback();
        }
}

Answer 1

NOTE #1: TransactionScope can escalate to use MSDTC so using SqlTransaction can be beneficial to avoid that behavior.

NOTE #2: TransactionScope will also use Serialized isolation level by default, when can be overly aggressive in row/table locking. So you might want to change that behavior when using TransactionScope. See for more details: Why is System.Transactions TransactionScope default Isolationlevel Serializable

Using SqlTransaction

Sticking to your pseudo code example, I would rewrite more like this:

InsertDetails()
{
    using (var sqlcon = new SqlConnection(<connectionString>))
    {
        sqlcon.Open();

        // Create transaction to be used by all commands.
        var transaction = sqlcon.BeginTransaction();

        try
        {
            InsertName(transaction, sqlcon);//SQL functions to insert name into name table  
            Insertaddress(transaction, sqlcon);//SQL functions to insert address into address table
            InsertPhoneNo(transaction, sqlcon);//code to insert phone no into contact table

            transaction.commit();       
        }
        catch(Exception ex)
        {
            transaction.rollback();
            throw;
        }
    }
}

// Typical method implementation.
private void InsertName(SqlTransaction transaction, SqlConnection sqlcon)
{
    using (var cmd = sqlcon.CreateCommand())
    {
        // This adds this command to the transaction.
        cmd.Transaction = transaction;

        // The rest is fairly typical.
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "InsertStoredProcedureName";
        ... set parameters etc.
        cmd.ExecuteNonQuery();
        ... handle any OUTPUT parameters etc.
    }
}

This will rollback the transaction for all errors in any of the called methods.

Answer 2

I wouldn't recommend this approach.

If you are using new TransactionScope then you just need to call Complete when all is good. If not, then everything will be rolled back if Complete is not called. So kind of defeats the purpose of passing Transaction around, no? This syntax is clearer in my opinion.

You can call Transaction.Current to get the current transaction if you need to determine whether you are running under a transactional context.

Answer 3

The following pattern worked for me with a DataContext (confirmed using Sql Server Profiler). You can probably replace the DataContext with a SqlConnection instead. This does NOT result in escalating the transaction to MSDTC. If there is an exception or return statement from within the transaction scope, the transaction will automatically be rolled back.

using (MyDataContext dc = new MyDataContext(MyConnectionString))
{
    using (var t = new TransactionScope(TransactionScopeOption.Required,
        new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
    {
        InsertName(dc);
        InsertAddress(dc);
        InsertPhoneNo(dc);
        t.Complete();
    }
}

void InsertName(MyDataContext dc)
{
   dc....
   dc.SubmitChanges();
}

Answer 4

I'm not sure, but we can simply use like this right! Is there any other issues?

using (TransactionScope scope = new TransactionScope())
{
  var isSuccess = Save();
  var isSuccess2 = Save2();
  if (isSuccess && isSuccess2 )
  {
    scope.Complete();
  }
}