I have at numerous times been asked the question, “How can I make TableAdapters Transactional”.
To set things straight, and this is in context with a discussion I was having with Steve Lasker at TechED 2006, Boston, TableAdapters are there to make your life “simple”. As a matter of fact, initially TableAdapters did expose all the goo – Connection, Transactions etc. But then, that doesn’t make things simple. TableAdapters in their current RTM implementation hide all those intricacies from you, and make things simple. But many of us advanced developers wish to have more power/knobs/properties - whatever you wanna call them. As an advanced developer you can extend table adapters using partial classes anyway, so you now have the best of both worlds.
As a result, the final implementation of TableAdapters in .NET 2.0 RTM, hides all that complexity from you; and considering partial classes extensibility, in my opinion that is a fair tradeoff.
In certain instances however, you may need the complexity. Two good examples are “Transactions” and “Connection lifetime management”.
Let us examine these by a simple blogpost that makes a simple TableAdapter transactional. For the purposes of this blogpost, I have setup a simple TableAdapter, targeting the Customer’s table in the Northwind database. I also have a CustomersTableAdapter. This can be seen in the figure below.
Okay good!! Now let’s say that you are trying to make the “Update” operation transactional. There are two ways you can go about to solve this problem
Implementing Transactions using SqlTransaction
For case #1, I am going to ignore System.Transactions and implement a plain vanilla ADO.NET transaction implementation.
The easiest way to make your TableAdapter transactional, is to extend it’s definition with a partial class that provides you with a “hook” to add transactional behavior into your TableAdapter as follows.
public SqlTransaction BeginTransaction(SqlConnection connection)
{
if (connection.State == ConnectionState.Closed)
{
throw new Exception(
"Connection State cannot be closed for a new transaction") ;
}
SqlTransaction tran = connection.BeginTransaction() ;
foreach (SqlCommand cmd in _commandCollection)
{
cmd.Transaction = tran ;
}
return tran ;
}
As you can see above, I am looking to see if the connection is currently open, as beginning a transaction on a closed connection is like a gay couple trying to conceive. Thus if the connection is closed, I throw an exception, otherwise I begin a transaction, and enlist all commands in the Tableadapter in the transaction. Of course, your implementation may be specific to your case, but this as a general case will work.
Now it is obvious that per the above implementation, you will also need to provide hooks to open and close connections, i.e. manage connection lifetime yourself. This can be done by adding two quick methods to the partial class definition as follows –
public SqlConnection OpenConnection()
{
if (_connection.State != ConnectionState.Open)
{
_connection.Open() ;
}
return _connection ;
}
public void CloseConnection()
{
_connection.Close() ;
}
The final code for the partial class definition looks like as follows –
public partial class CustomersTableAdapter : System.ComponentModel.Component
{
public SqlConnection OpenConnection()
{
if (_connection.State != ConnectionState.Open)
{
_connection.Open() ;
}
return _connection ;
}
public void CloseConnection()
{
_connection.Close() ;
}
public SqlTransaction BeginTransaction(SqlConnection connection)
{
if (connection.State == ConnectionState.Closed)
{
throw new Exception(
"Connection State cannot be closed for a new transaction") ;
}
SqlTransaction tran = connection.BeginTransaction() ;
foreach (SqlCommand cmd in _commandCollection)
{
cmd.Transaction = tran ;
}
return tran ;
}
}
With this much setup, the “transactional” usage of this TableAdapter becomes pretty damn simple. Here is a code sample –
CustomersTableAdapter tableAdap = new CustomersTableAdapter() ;
SqlTransaction tableAdapTran = null ;
SqlConnection tableAdapConn = null ;
try
{
tableAdapConn = tableAdap.OpenConnection() ;
tableAdapTran = tableAdapConn.BeginTransaction() ;
// Do your transactional work
tableAdapTran.Commit() ;
}
catch (Exception)
{
tableAdapTran.Rollback() ;
}
finally
{
tableAdapConn.Close() ;
}
The above will work, but is a tad bit cumbersome considering that you “do” need to write all that code. A simpler implementation can be achieved using System.Transactions.
Implementing Transactions using System.Transactions
I recommend reading up my quick primer on System.Transactions before diving into this implementation.
A much simpler implementation to the above would be to simply wrap a singular operation inside a System.Transactions.TransactionScope. This is shown as below –
using (TransactionScope tsc = new TransactionScope())
{
//Do your transactional work.
tableAdap.Update() ;
tsc.Complete() ;
}
IMPORTANT NOTE: I said “Singular” operation. Why? Because if you are working with plural operations inside one TransactionScope, i.e. “GetData” and “Update” both inside a single TransactionScope, or two Update’s within a TransactionScope, you will effectively open two SqlConnections to the single database, and thus unnecessarily promote the transaction from LTM to MSDTC. As a best practice, ALWAYS wrap only a singular operation inside a TransactionScope. Should you choose to wrap multiple operations inside a single TransactionScope, you must in that case manage connection lifetime yourself by extending the partial class definition. In other words, the following code will cause the transaction to promote –
using (TransactionScope tsc = new TransactionScope())
{
tableAdap.GetData() ;
//Do your transactional work.
tableAdap.Update() ;
tsc.Complete() ;
}
But the following code is just fine –
using (TransactionScope tsc = new TransactionScope())
{
tableAdap.OpenConnection() ;
tableAdap.GetData() ;
//Do your transactional work.
tableAdap.Update() ;
tableAdap.CloseConnection() ;
tsc.Complete() ;
}
(Where OpenConnection and CloseConnection are custom methods I added in the partial class definition).
This and other such use-cases you need to be careful of, are further explained in my code-magazine article. (Paid content)
IMPORTANT NOTE #2: For databases other than SQL Server 2005 (as of Sunday June 18, 2006), you must prefer to use the Local Transaction implementation over a System.Transactions implementation, since their transactions under System.Transactions will always promote to MSDTC. This is true for Oracle, SQL Server 2000, DB2, etc. On the other hand, if you have multiple databases involved in your transaction, then go with System.Transactions considering the simpler implementation, and no downside (because the transaction would have promoted anyway).
Closing Comments
Transactions are seldom as simple as “All or None”. If you had a DataTable with 5 changed rows, this translates to 5 commands to be executed during “Update”. If Command #1 succeeds, Command #2 succeeds, and Command #3 fails, you have the following choices at this time –
a) Stop right there and return
b) Ignore #3, and continue with #4 and #5
c) Stop and rollback #1 and #2
This blogpost addressed scenario #c. For a) and b) you will have to apply concepts similar to what you’d do in DataAdapter, alongwith all the code to merge delta datasets, and carrying errors across merges. Each one of these cases can be achieved using permutations and combinations of DataAdapter.AcceptChangesDuringUpdate, DataAdapter.ContinueUpdateOnError, Transactions, and DataTable.AcceptChanges.
One day in the future I’ll blog about them, or they can all be found in my book, Chapters 9,10 and 11.
