Manage Transactions on Business Layer

Jul 23, 2012 at 10:23 AM

I want to use TransactionScope class in my business layer to manage database operation in data access layer.

Here is my sample code. When i execute it, it tries to enable the dtc. I want to do the operation without enable dtc.

I already checked https://entlib.codeplex.com/discussions/32592 article. It didn't work for me since there is no working code.

Thanks,

Business Layer method:

 

public void SampleInsert()
        {
            using (TransactionScope scope = new TransactionScope())
            {
                Sample1DAL dal1 = new Sample1DAL(null);
                Sample2DAL dal2 = new Sample2DAL(null);
                Sample3DAL dal3 = new Sample3DAL(null);
                dal1.SampleInsert();
                dal2.SampleInsert();
                dal3.SampleInsert();
                scope.Complete();
            }
        }
Data Access Layer method:
//sampleInsert method structurally same for each 3 dal
public void SampleInsert()
        {
            Database database = DatabaseFactory.CreateDatabase(Utility.DATABASE_INFO); ;
            using (DbConnection conn = database.CreateConnection())
            {
                conn.Open();
                DbCommand cmd = database.GetStoredProcCommand("P_TEST_INS", "some value3");
                database.ExecuteNonQuery(cmd);
            }
        }

 

Jul 24, 2012 at 12:06 AM

It definitely sounds like you are hitting transaction escalation because you have more than one connection open at the same time.  I'm going to guess that you are using SQL Server 2005.  What database and version are you using?

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Jul 24, 2012 at 8:05 AM

I am using Sql Server 2008.

So, one more thing. Is the following pattern ok? I am not taking any exception for DTC and using sql profiler I can see that when i call the method several times it always use the same session id. And also i wonder when the database object disposed? Do i have to use it with "using". Because again in sql profiler i can see that after a while the connection really closed.

 

public void SampleInsert()
        {
            Database database = DatabaseFactory.CreateDatabase(Utility.DATABASE_INFO);
            using (DbCommand cmd = database.GetStoredProcCommand("P_TEST_INS", "some value1"))
            {
                database.ExecuteNonQuery(cmd);
            }
        }

Jul 24, 2012 at 4:23 PM
Edited Jul 24, 2012 at 4:30 PM

You can avoid transaction escalation to a distributed transaction if:

  • The database is SQL Server 2008
  • .NET Framework 3.5 or above is being used
  • Connection pooling is enabled (it is by default)
  • The connection strings are identical 
  • Only one connection is open at once

See http://blogs.msdn.com/b/adonet/archive/2008/03/26/extending-lightweight-transactions-in-sqlclient.aspx for more information about the changes made to ADO.NET to support the above.

DbCommand implements IDisposable.  In general, if an object implements IDisposable then Dispose should be called when finished with the object.  That said, not calling Dispose on DbCommand is usually not catastrophic since DbCommand.Dispose() only frees up some cached parameter information and does not affect the connection.

When you call ExecuteNonQuery() Enterprise Library will open a connection, execute the query and then call close on the connection.  So, the pattern is OK.

When viewing connections in SQL Profiler remember that connections are (usually) pooled so you will see the same connection reused for many calls even though in code the connection has been "opened" and then "closed".

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Jul 24, 2012 at 6:42 PM

Thanks for the lots of and helpful information you gave me.

Now i have no question for transactionscope thing anymore :)