Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.
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 }
43
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 }
52
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 }
141
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());
146
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 }
163
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());
168
169 scope.Complete();
170 }
This writes the following to the command line:
Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:2Distributed Transaction ID: 00000000-0000-0000-0000-000000000000Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:2Distributed 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 }
88
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());
93
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 }
109
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());
114
115 scope.Complete();
116 }
Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:1Distributed Transaction ID: 00000000-0000-0000-0000-000000000000Local Transaction ID: e90f47f4-df80-496b-a9c0-0c45b2f452c4:1Distributed 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);
183
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());
188
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);
194
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());
199
200 scope.Complete();
201 }
Local Transaction ID: 6737b756-2d5b-4eff-902d-15f9ccd5c26f:3Distributed Transaction ID: 00000000-0000-0000-0000-000000000000Local Transaction ID: 6737b756-2d5b-4eff-902d-15f9ccd5c26f:3Distributed 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:
Remember Me
a@href@title, strike