I love the new features of ADO.Net, the Object Datasources and the table adapters that the XSD designer can generate. But they kind of have a will of their own! They open and close connections as they see fit, and dont really care about transactions. If you have a set of related tables that you make changes to, and then want to write all of the changes to the database in one transaction, you need to do things a certain way to make it work...
What about the TransactionScope class?
My first attempt was to use the new TransactionScope functionality in ADO.Net 2.0. You can do something like this:
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
{
// do stuff that manipulates data
ts.Complete();
}
While this is a very elegant solution, it is very tricky to get to work. The mechanism uses a Local Transaction Coordinator, but as soon as you execute your second database update command, it escalates the transaction to a distributed transaction (even if you are just doing sequential operations in the same SqlConnection), and tries to do 2 way communication with the DTC (Distributed Transaction Coordinator) running on the SqlServer. This communication turns out to be a major headache to get working. Asides from opening all kinds of firewall holes and configuring lots of complicated DTC options on both the server machine and your local machine, the server needs to be able to do a reverse DNS lookup to call back to your client machine, which proves to be unfeasable if you are developing from home across a VPN or have a dynamic IP address that the server cant lookup. It really only works when your client and the SQL Server are part of the same AD domain.
Long story short, TransactionScope is too complicated right now to actually use in normal development. So I wanted to stick with the traditional transaction support.
Getting the TableAdapters to use the correct Connection object
First of all, you need to make sure all of your TableAdapters use the same Connection object. TableAdapters are not an actual class in ADO.Net, they are code generated classes, kind of like typed DataSets, but unlike typed DataSets they dont inherit from some common TableAdapter class, they just inherit from Component. However, they do expose a Connection property, so I use that to hook up all my TableAdapters to the same Connection object. Something like this:
void InitTableAdapters( SqlConnection conn )
{
taReportSetGroupAccountNotAssigned.Connection = conn;
taReportSetGroupAccount.Connection = conn;
taReportSetGroup.Connection = conn;
taReportSet.Connection = conn;
}
Setting up the transaction
Now, you can encapsuluate your database operations in a transaction as follows:
SqlConnection conn = new SqlConnection( connStr );
InitTableAdapters( conn );
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
// manipulate data
tran.Commit();
}
catch()
{
tran.Rollback();
}
finally
{
conn.Close();
}
However, there is still a problem with this. The SqlCommands inside the TableAdapter need to use the transaction of the Connection object in the Table Adapter, but the generated code does not do that for you. And you don't want to start messing with the generated code, or your changes will be wiped out next time it is regenerated. What to do?
Making the SqlCommand enlist in your transaction
First I tried subclassing the generated TabeAdapter, as I saw there was a protected CommandCollection property. However, it turned out that this collection only contains the Select Command, and I wanted the Insert, Update and Delete commands to participate in the transaction. These are part of the SqlDataAdapter member of the TableAdapter, which is private. Duh!
Well, partial classes come to the rescue! The TableAdapter class is generated as a partial class, which allows you to extend it by adding in additional methods in your own partial class. One gotcha is that you need to use the same namespace for your partial class as for the generated partial class, and that namespace is actually not the same as that of the typed dataset. It has .XXXTableAdapters added on to the end of it, where XXX is the name of your typed dataset. For instance, if your dataset namespace is Falafel.Data, and its name is FalafelDataset, then your TableAdapter namespace will be Falafel.Data.FalafelDatasetTableAdapters.
Armed with this knowledge, we can extend the partial class of each TableAdapter as follows:
namespace Falafel.Data.FalafelDataSetTableAdapters
{
public partial class ReportSetGroupAccountTableAdapter
{
public void JoinTransaction( SqlTransaction tran )
{
TransactionHelper.JoinTransaction(_adapter, tran);
}
}
public partial class ReportSetGroupTableAdapter
{
public void JoinTransaction( SqlTransaction tran )
{
TransactionHelper.JoinTransaction(_adapter, tran);
}
}
}
Here I am using my own helper class:
public class TransactionHelper
{
public static void JoinTransaction( SqlAdapter adapter, SqlTransaction tran)
{
CheckTransaction(tran, adapter.InsertCommand);
CheckTransaction(tran, adapter.DeleteCommand);
CheckTransaction(tran, adapter.UpdateCommand);
CheckTransaction(tran, adapter.SelectCommand);
}
public static void CheckTransaction( SqlAdapter adapter, SqlCommand cmd)
{
if (cmd != null)
cmd.Transaction = tran;
}
}
Putting it all together
Now that I have injected a public JoinTransaction method into each TableAdapter, I can get them to enlist in the transaction by surreptitiously calling the trojan horse helper:
void EnlistTableAdaptersInTransaction( SqlTransaction tran )
{
taReportSetGroupAccountNotAssigned.JoinTransaction( tran );
taReportSetGroupAccount.JoinTransaction( tran );
taReportSetGroup.JoinTransaction( tran );
taReportSet.JoinTransaction( tran );
}
Adding a call to this in my update logic, this is the final sequence of events:
SqlConnection conn = new SqlConnection( connStr );
InitTableAdapters( conn );
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
try
{
EnlistTableAdaptersInTransaction( tran );
// manipulate data
tran.Commit();
}
catch()
{
tran.Rollback();
}
finally
{
conn.Close();
}
It's not the most elegant or compact code, but it gets the job done! I hope this will save you some time and allow you to productively use TableAdapters in transactions until the TransactionScope class becomes more development environment friendly!