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

user_884132 0
Viewed 4386

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();
        }
}
transactions sql-server c#
4 Answers

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

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