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

What prevents you to do that? (Apart from the reinventing the wheel of course) — Steve, Jul 30 '17 at 15:53
Well the using syntax is a lot safer and what is the purpose of passing the transaction to methods? It should be transparent — , Jul 30 '17 at 16:10

Doug Knudsen · Accepted Answer · 2017-08-01T15:29:26.690

7

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.

edited Aug 01 '17 at 15:29

answered Jul 31 '17 at 03:00

Doug Knudsen

935
9
15

No need to explicitly call rollback, leaving the using statement without calling commit will cause a rollback to happen. And if you where going to explicitly do it in a catch block i would add in a `throw;` after the rollback so the exception bubbles up. – Scott Chamberlain Jul 31 '17 at 04:35
How would you use the transaction object to make a crud queries? is it possible to have an example? – Dizzle Jul 31 '17 at 23:09
@Dizzle - modified code so show example SqlTransaction usage in a method. Each method would need to be added to the transaction in a similar fashion. – Doug Knudsen Aug 01 '17 at 14:38
@ScottChamberlain - thanks for the comments. I was trying to show the commit/rollback functionality explicitly for clarification. I added the `throw;` per your suggestion as a more complete example. – Doug Knudsen Aug 01 '17 at 14:43
1

Up voting this since TransactionScope seems overkill, I use it to govern connections to multiple sql servers (distributed transaction). – Alex Aug 01 '17 at 14:52
@ScottChamberlain thanks for updating it is as I thought it might look, I have a further question if i may, if you perform queries in order will the subsequent queries see the most recent data? eg if you create a new record in one function which i assume appends the record to the transaction and then the next function try to read the newly created record will the read be successful against the new id even though the transaction is not committed until the end of all the functions? – Dizzle Aug 02 '17 at 18:45
As long as both functions are under the same transaction yes the 2nd call will see the changes. – Scott Chamberlain Aug 02 '17 at 18:48
@ScottChamberlain ok great this is exactly what i was looking for and glad to know its safe to do it like this. Thanks :) – Dizzle Aug 03 '17 at 10:05
@DougKnudsen If I change TransactionScope's iso level to Read Committed (which is BeginTransaction's default) then it shouldn't matter if I use SqlTransaction or TransactionScope? – bigb055 Dec 28 '18 at 07:38
@DougKnudsen Bonus question: Why would I pass the connection as an argument when I could access it via transaction's **Connection** property? – bigb055 Dec 28 '18 at 07:51
@bigb055 - There are differences between SqlTransaction and TransactionScope that may make one more appropriate or advantageous based on your situation (for example, TransactionScope can elevate to use MSDTC unnecessarily and cause headaches or come in handy when using different SQL data sources). Search on "transactionscope vs sqltransaction" for numerous debates. Regarding your second comment, yes that is true and you wouldn't need to pass both unless you were concerned that the transaction might become unavailable for some reason. – Doug Knudsen Dec 29 '18 at 16:01

score 1 · Answer 2 · answered Jul 30 '17 at 16:21

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.

score 0 · Answer 3 · answered Aug 14 '20 at 03:36

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

score 0 · Answer 4 · answered Sep 22 '21 at 18:36

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

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

4 Answers4