Dec 072008

It’s so easy! Start downloading Enterprise Library 4.1 now while you read this. The data application block syntax has not changed much since the first version. The most notable change was allowing us to use System.Data.Common.DbCommand when version 3.0 was released. I understand the uneasy feeling some developers have using Enterprise Library. My team at my previous employer decided not to use it, thinking it would add increased complexity and would not give us the flexibility we needed if we had to change something. This is typical of groups that do not have an established Data Access Library.

Your Data Access Library should be one of the most highly tested libraries in your application. If there is a problem there, you will have issues everywhere. Enterprise Library not only comes with the source code, but also includes the full suite of unit tests for each of the application blocks. You should feel at ease when you decide to migrate to Enterprise Library. Run it through your full battery of tests before you commit the team to it. If you find any problems, check the forums, request changes/enhancements from the MS Patterns & Practices team, or fix it yourself.

The steps to achieve EntLib goodness:

  1. Download Enterprise Library
  2. Add reference to “Enterprise Library Data Access Application Block” and “Enterprise Library Shared Library”
  3. Change your app.config or web.config
  4. Write some much more readable data access code

I’ll start at step 3 as steps 1 and 2 are self-explanatory. Your connection string needs to be in you app’s config file, the machine.config file, or in a connectionStrings.config file referenced in those config files. You can start using it just by adding the <configSections> clock and the <dataConfiguration> node. This will allow you to have one default database for all commands you will execute.

<?xml version=”1.0encoding=”utf-8“?>



        <section name=”dataConfigurationtype=”Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35” />


    <dataConfiguration defaultDatabase=”Testing” />


        <add name=”TestingconnectionString=”server=Server_Name;database=DB_Name;Integrated Security=true;

                  providerName=”System.Data.SqlClient” />



By the time you get to step 4, you have all of the infrastructure in place. Painless so far, let’s see how steep the learning curve is.

With ADO.NET, you would write:

  116 string connectionString = ConfigurationManager.ConnectionStrings[“Testing”].ConnectionString;

  117 using (SqlConnection con = new SqlConnection(connectionString))

  118 using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

  119 {

  120     cmd.CommandType = System.Data.CommandType.StoredProcedure;

  121     cmd.Parameters.AddWithValue(“Message”, “Testing 1”);

  122     cmd.Parameters.AddWithValue(“UserID”, 5150);

  123     try

  124     {

  125         con.Open();

  126         cmd.ExecuteNonQuery();

  127     }

  128     finally

  129     {

  130         con.Close();

  131     }

  132 }

With Enterprise Library, you write:

  170 Database db = DatabaseFactory.CreateDatabase();

  171 DbCommand cmd = db.GetStoredProcCommand(“usp_ErrorLog_Insert”);

  172 db.AddInParameter(cmd, “Message”, System.Data.DbType.String, “Testing 1”);

  173 db.AddInParameter(cmd, “UserID”, System.Data.DbType.Int32, 5150);

  174 db.ExecuteNonQuery(cmd);


Line 170 creates the database object. This is the hardest thing to get used to. You call everything related to the Database object. In ADO.NET, we are used to creating a connection, adding the connection to a command, using the command in an adapter. Here you’ll always be using the Database object to create a command, add parameters to the command, execute the command, fill a DataSet, etc. It is definitely less code to write, but it is also more readable and elegant.

If you have a database to execute commands against other than the defaultDatabase specified in the config file, then the first line changes to:

  170 Database db = DatabaseFactory.CreateDatabase(“OtherConnectionStringKey”);


That’s it. The patterns & practices team has really done a nice job making it painless to use Enterprise Library. Take the time to try it out again if you reviewed a previous version. I reviewed 2.0, and chose not to use it. When 3.0 came out, I was hooked.

Dec 052008

Unless you are working on a extremely simple or read-only application, transactions are a must. Using the System.Transactions namespace is the easiest and most efficient way to maintain system consistency when dealing with multiple calls or multiple resources. Although System.Transactions arrived in .NET in the 2005 product, it is still a relatively unknown part of the framework. System.Transactions was designed by the Indigo team in preparation for WCF. It is not compatible with Win98 or WinME, but most people are incompatible with Win98 and WinME so it works out just fine.

Before System.Transactions, we only had access to System.Data.SqlClient.SqlTransaction or a true SQL transaction using BEGIN/ROLLBACK/COMMIT TRAN. Using SQL transactions, you are stuck with only being able to update DB records as part of your transaction. If you wanted to change a cached value in your app in addition to the SQL updates in the same transaction then you would be out of luck. This also required a lot of transaction code in your stored procedures, writing stored procedures that can be called independently or part of transaction made for very messy stored procedures and often led to multiple stored procedures that served the same purpose.

Using the SqlTransaction class was also messy. The most important restriction is that you need to have all database calls inside the same SqlConnection. This does not work well for a well-designed N-tier application. The other problem is that you need to handle your own non-DB transaction logic inside the same SqlTransaction and conditionally commit/rollback as necessary. This all tends to lead to several try-catch statements within one SqlTransaction. Handling the plumbing to manually add each SqlCommand to the transaction gets old quickly too.

Using SqlTransaction

   27 string connectionString = ConfigurationManager.ConnectionStrings[“Testing”].ConnectionString;

   28 using (SqlConnection con = new SqlConnection(connectionString))

   29 {

   30     SqlTransaction tran = null;

   31     try

   32     {

   33         con.Open();

   34         tran = con.BeginTransaction();

   35         using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

   36         {

   37             cmd.Transaction = tran;

   38             cmd.CommandType = System.Data.CommandType.StoredProcedure;

   39             cmd.Parameters.AddWithValue(“Message”, “Testing 1”);

   40             cmd.Parameters.AddWithValue(“UserID”, 5150);

   41             cmd.ExecuteNonQuery();

   42         }


   44         using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

   45         {

   46             cmd.Transaction = tran;

   47             cmd.CommandType = System.Data.CommandType.StoredProcedure;

   48             cmd.Parameters.AddWithValue(“Message”, “Testing 2”);

   49             cmd.Parameters.AddWithValue(“UserID”, 5150);

   50             cmd.ExecuteNonQuery();

   51         }


   53         tran.Commit();

   54     }

   55     catch

   56     {

   57         if (tran != null) tran.Rollback();

   58     }

   59     finally

   60     {

   61         con.Close();

   62     }

   63 }

System.Transactions liberated us from the mundane SqlClient code and repetitive try-catches. simply wrapping your old ADO.NET with a using (TransactionScope) { } is all you need to do. You will typically add a transactionScope.Complete() statement as the last line in the TransactionScope using block is really all you need. Any exception thrown before this point will break out of scope, implicitly aborting the transation. Much better.

System.Transactions uses the LTM (Lightweight Transaction Manager) when dealing with single resources or machines. The transaction is automatically promoted to MSDTC (Microsoft Distributed Transaction Coordinator) when another resource is enlisted in a transaction. A lot of people struggle with MSDTC because it is difficult to setup, requires special firewall considerations, and doesn’t really work well for smart client applications since you have to install MSDTC on every client machine.

I’ll show one transaction performed three different ways and show what happens with the LTM and MSDTC for each of them. I will also demonstrate an excellent reason to migrate to Enterprise Library.

1) Executing two ADO.NET SqlCommands in different SqlConnections

  122 using (TransactionScope scope = new TransactionScope())

  123 {

  124     string connectionString = ConfigurationManager.ConnectionStrings[“Testing”].ConnectionString;

  125     using (SqlConnection con = new SqlConnection(connectionString))

  126     using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

  127     {

  128         cmd.CommandType = System.Data.CommandType.StoredProcedure;

  129         cmd.Parameters.AddWithValue(“Message”, “Testing 1”);

  130         cmd.Parameters.AddWithValue(“UserID”, 5150);

  131         try

  132         {

  133             con.Open();

  134             cmd.ExecuteNonQuery();

  135         }

  136         finally

  137         {

  138             con.Close();

  139         }

  140     }


  142     Console.WriteLine(“Local Transaction ID: {0}”,

  143         Transaction.Current.TransactionInformation.LocalIdentifier);

  144     Console.WriteLine(“Distributed Transaction ID: {0}”,

  145         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());


  147     using (SqlConnection con = new SqlConnection(connectionString))

  148     using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

  149     {

  150         cmd.CommandType = System.Data.CommandType.StoredProcedure;

  151         cmd.Parameters.AddWithValue(“Message”, “Testing 2”);

  152         cmd.Parameters.AddWithValue(“UserID”, 5150);

  153         try

  154         {

  155             con.Open();

  156             cmd.ExecuteNonQuery();

  157         }

  158         finally

  159         {

  160             con.Close();

  161         }

  162     }


  164     Console.WriteLine(“Local Transaction ID: {0}”,

  165         Transaction.Current.TransactionInformation.LocalIdentifier);

  166     Console.WriteLine(“Distributed Transaction ID: {0}”,

  167         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());


  169     scope.Complete();

  170 }

This writes the following to the command line:

Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:2
Distributed Transaction ID: 00000000-0000-0000-0000-000000000000
Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:2
Distributed Transaction ID: 1fad8108-ddae-496a-a7da-ce92df175e40

You’ll notice that the first command creates a transaction using LTM as indicated by the Local Transaction ID. After the second command is executed, the transaction is promoted to DTC as indicated by the Distributed Transaction ID. This is expected because there are two distinct SqlConnections. Even though the connection string is the same, TransactionScope treats these ADO.NET objects as unique resources.

This has additional implications when connection pooling comes into play. After I close the first connection, it is returned to the pool and is available for use. If this connection is requested for use, it will no longer be available to commit or abort this transaction, and you will see the dreaded MSDTC error “Communication with the underlying transaction manager has failed.”

2) Executing two ADO.NET SqlCommands in the same SqlConnection

   69 string connectionString = ConfigurationManager.ConnectionStrings[“Testing”].ConnectionString;

   70 using (TransactionScope scope = new TransactionScope())

   71 using (SqlConnection con = new SqlConnection(connectionString))

   72 {

   73     using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

   74     {

   75         cmd.CommandType = System.Data.CommandType.StoredProcedure;

   76         cmd.Parameters.AddWithValue(“Message”, “Testing 1”);

   77         cmd.Parameters.AddWithValue(“UserID”, 5150);

   78         try

   79         {

   80             con.Open();

   81             cmd.ExecuteNonQuery();

   82         }

   83         finally

   84         {

   85             con.Close();

   86         }

   87     }


   89     Console.WriteLine(“Local Transaction ID: {0}”,

   90         Transaction.Current.TransactionInformation.LocalIdentifier);

   91     Console.WriteLine(“Distributed Transaction ID: {0}”,

   92         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());


   94     using (SqlCommand cmd = new SqlCommand(“usp_ErrorLog_Insert”, con))

   95     {

   96         cmd.CommandType = System.Data.CommandType.StoredProcedure;

   97         cmd.Parameters.AddWithValue(“Message”, “Testing 2”);

   98         cmd.Parameters.AddWithValue(“UserID”, 5150);

   99         try

  100         {

  101             con.Open();

  102             cmd.ExecuteNonQuery();

  103         }

  104         finally

  105         {

  106             con.Close();

  107         }

  108     }


  110     Console.WriteLine(“Local Transaction ID: {0}”,

  111         Transaction.Current.TransactionInformation.LocalIdentifier);

  112     Console.WriteLine(“Distributed Transaction ID: {0}”,

  113         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());


  115     scope.Complete();

  116 }

This writes the following to the command line:

Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:1
Distributed Transaction ID: 00000000-0000-0000-0000-000000000000
Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:1
Distributed Transaction ID: becac9c9-e15f-4370-9f73-7f369665bed7

This is not expected because both commands are part of the same connection. Of course I am closing the connection to simulate an N-tier app where the data access layer is maintaining it’s own SQL access, opening and closing its connection as it should. If I did not close the connection, you would not see a Distributed Transaction ID after the second command.

3) Executing two Enterprise Library commands

  176 using (TransactionScope scope = new TransactionScope())

  177 {

  178     Database db = DatabaseFactory.CreateDatabase(“Testing”);

  179     DbCommand cmd = db.GetStoredProcCommand(“usp_ErrorLog_Insert”);

  180     db.AddInParameter(cmd, “Message”, System.Data.DbType.String, “Testing 1”);

  181     db.AddInParameter(cmd, “UserID”, System.Data.DbType.Int32, 5150);

  182     db.ExecuteNonQuery(cmd);


  184     Console.WriteLine(“Local Transaction ID: {0}”,

  185         Transaction.Current.TransactionInformation.LocalIdentifier);

  186     Console.WriteLine(“Distributed Transaction ID: {0}”,

  187         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());


  189     Database db1 = DatabaseFactory.CreateDatabase(“Testing1”);

  190     DbCommand cmd1 = db.GetStoredProcCommand(“usp_ErrorLog_Insert”);

  191     db1.AddInParameter(cmd1, “Message”, System.Data.DbType.String, “Testing 2”);

  192     db1.AddInParameter(cmd1, “UserID”, System.Data.DbType.Int32, 5150);

  193     db1.ExecuteNonQuery(cmd1);


  195     Console.WriteLine(“Local Transaction ID: {0}”,

  196         Transaction.Current.TransactionInformation.LocalIdentifier);

  197     Console.WriteLine(“Distributed Transaction ID: {0}”,

  198         Transaction.Current.TransactionInformation.DistributedIdentifier.ToString());


  200     scope.Complete();

  201 }

This writes the following to the command line:

Local Transaction ID: 6737b756-2d5b-4eff-902d-15f9ccd5c26f:3
Distributed Transaction ID: 00000000-0000-0000-0000-000000000000
Local Transaction ID: 6737b756-2d5b-4eff-902d-15f9ccd5c26f:3
Distributed Transaction ID: 00000000-0000-0000-0000-000000000000

Whoa! How cool is that? No DTC promotion. Enterprise Library is intelligently deciding to keep the connection open when it is part of the transaction. This will save a lot of wasted time as the promotion to DTC adds a noticeable delay. If I wasn’t using Enterprise Library already, I’d switch now.

Useful links: