Typed DataSets and SqlTransaction
I've recently been using typed DataSets to talk to a database for the first time. Strange, I know, but I learned how to do database access using raw ADO.NET back in the 1.1 days, and never really bothered with the nice visual DataSet designers or the new TableAdapter helpers.
Anyway, I have a project here with a DataSet containing four tables (and hence four TableAdapter classes). When the client program sends the changes in the DataSet back to the server, I want to update the tables, but I want to do it in a transaction so that if any one thing fails, no changes are written to the database.
Turns out that this is non-trivial. Support for transactions is not built in to the generated TableAdapter classes. Luckily it's possible to implement it yourself using .NET 2.0's partial class feature.
Sahil has written a bit about how to do this if you just have the one TableAdapter. In his post, he talks about creating a BeginTransaction method on that TableAdapter. That works well, but if you have more than one, you need some way to share a transaction across all your adapters.
So here's my solution. Instead of a BeginTransaction method, I have implemented a Transaction property on my TableAdapters, like this:
partial class CategoriesTableAdapter
{
public SqlTransaction Transaction
{
get
{
return _adapter.SelectCommand.Transaction;
}
set
{
if (_adapter == null) InitAdapter();
Connection = value.Connection;
_adapter.InsertCommand.Transaction = value;
_adapter.UpdateCommand.Transaction = value;
_adapter.DeleteCommand.Transaction = value;
}
}
}
As you can see, the property assigns the given transaction to the Transaction property on all its commands. This means I can do this in my Update method:
CategoriesTableAdapter catsTA = new CategoriesTableAdapter();
CategoryCodesTableAdapter codesTA = new CategoryCodesTableAdapter();
AllocationsTableAdapter allocsTA = new AllocationsTableAdapter();
CostsTableAdapter costsTA = new CostsTableAdapter();
catsTA.Connection.Open();
try
{
SqlTransaction txn = catsTA.Connection.BeginTransaction();
try
{
catsTA.Transaction = txn;
codesTA.Transaction = txn;
allocsTA.Transaction = txn;
costsTA.Transaction = txn;
// update each table, commit transaction or
// rollback if there's a problem
What do you think? It certainly seems to work. There's probably more to it ... like I should probably check that there's not already a Transaction set on the _adapter commands etc. It's doing the job for me, though, so I hope it helps someone else out there.
