Using Transactions with Strongly Typed datasets

Strongly typed datasets , generated by Visual Studio 2005 have a lot of disadvantages and developer of a real-world application usually have to write a lot of additional code in order to use them.

One of the issues - you cannot use SqlTransaction object with auto-generated datasets unless you write some additional code in partial classes of data adapters. Surprisingly, most .NET developers even don't care about that. But if you develop dataset composed of several tables that together mean one business entity, one may expect that if any exception during update of this entity happens, all changes should be rolled back, otherwise you may get inconsistent data.

In my example I created a Windows Console Application in VS 2005 C#. Then using database explorer connected to AdventureWorks database (that comes with MS SQL Server 2005 examples) created a dataset with the tables Customer (mapped to Sales.Customer), Address (mapped to Person.Customer) and CustomerAddress (mapped to Sales.CustomerAddress)

Supposing that I will have a form on UI where I want to edit customer's data and customer's addresses as well. It is not so unusual scenario, and I naturally will want to save all this in one transaction.

In order to do this I have to created a partial classes for all table adapters to make transaction property available to set from an external class, i.e.:

namespace TestDatasetTransaction.CustomerDataSetTableAdapters
{
partial class CustomerTableAdapter
{
public void SetTransaction(SqlTransaction tran)
{
this._adapter.UpdateCommand.Transaction = tran;
this._adapter.InsertCommand.Transaction = tran;
this._adapter.DeleteCommand.Transaction = tran;
}
}

partial class AddressTableAdapter
{
public void SetTransaction(SqlTransaction tran)
{
this._adapter.UpdateCommand.Transaction = tran;
this._adapter.InsertCommand.Transaction = tran;
this._adapter.DeleteCommand.Transaction = tran;
}
}

partial class CustomerAddressTableAdapter
{
public void SetTransaction(SqlTransaction tran)
{
this._adapter.UpdateCommand.Transaction = tran;
this._adapter.InsertCommand.Transaction = tran;
this._adapter.DeleteCommand.Transaction = tran;
}
}
}

in Visual Studio designer I created a

then I wrote the following code in the console's main class to prove that transactions really works.


using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace TestDatasetTransaction
{
class Program
{
static void Main(string[] args)
{
int customerID = 1;

CustomerDataSet customer = new CustomerDataSet();

//fill all records in CustomerDataSet with data for one customer
CustomerDataSetTableAdapters.CustomerTableAdapter customerTableAdapter = new CustomerDataSetTableAdapters.CustomerTableAdapter();
customerTableAdapter.FillByCustomerID(customer.Customer, customerID);

CustomerDataSetTableAdapters.AddressTableAdapter addressTableAdapter = new CustomerDataSetTableAdapters.AddressTableAdapter();
addressTableAdapter.FillByCustomerID(customer.Address, customerID);

CustomerDataSetTableAdapters.CustomerAddressTableAdapter customerAddressTableAdapter = new CustomerDataSetTableAdapters.CustomerAddressTableAdapter();
customerAddressTableAdapter.FillByCustomerID(customer.CustomerAddress, customerID);

Console.WriteLine("Current Customer's ModifiedDate is : {0:g}", customer.Customer[0].ModifiedDate);

//make some changes
//this change is valid
customer.Customer[0].ModifiedDate = DateTime.Now;
Console.WriteLine("Will change Customer's ModifiedDate to : {0:g}", customer.Customer[0].ModifiedDate);

//this change is not valid, sql server will not accept such date
customer.Address[0].ModifiedDate = DateTime.MinValue;
Console.WriteLine("Will change Address ModifiedDate to : {0:g}", customer.Address[0].ModifiedDate);



using (SqlConnection conn = new SqlConnection(
TestDatasetTransaction.Properties.Settings.Default.AdventureWorksConnectionString))
{
conn.Open();
SqlTransaction tran = null;
try
{
tran = conn.BeginTransaction();

customerTableAdapter.Connection = conn;
addressTableAdapter.Connection = conn;
customerAddressTableAdapter.Connection = conn;

customerTableAdapter.SetTransaction(tran);
addressTableAdapter.SetTransaction(tran);
customerAddressTableAdapter.SetTransaction(tran);

customerTableAdapter.Update(customer);
addressTableAdapter.Update(customer);
customerAddressTableAdapter.Update(customer);

tran.Commit();

}
catch (Exception ex)
{
if (tran != null) tran.Rollback();
Console.WriteLine(ex.Message);
}
tran.Dispose();

customerTableAdapter.FillByCustomerID(customer.Customer, customerID);


conn.Close();
}

Console.WriteLine("Finally Customer's ModifiedDate is : {0:g}", customer.Customer[0].ModifiedDate);
Console.WriteLine();
Console.WriteLine("Press a key");
Console.ReadKey();

}
}
}

-------------

The console output was respectivly


Current Customer's ModifiedDate is : 19.03.2008 17:16
Will change Customer's ModifiedDate to : 19.03.2008 17:33
Will change Address ModifiedDate to : 01.01.0001 0:00
Over SqlDateTime. Should be from 1/1/1753 12:00:00 AM to
12/31/9999 11:59:59 PM.
Finally Customer's ModifiedDate is : 19.03.2008 17:16
Press a key


------------

So, the transaction really works, and because of an exception happened during update of the 2nd table, all changes made to the first table was rolled back.

Of course the more simple solution is to use the TransactionScope() object from System.Transaction.dll, however this will not work on all platforms and databases. Also it seems, that an internal mechanism that exist in System.Transaction that is responsible for transparent (from developer's point of view) transaction enlisting from different resource managers should have affect on performance. If I will have some time, I will try to make a small performance test comparing TransactionScope() and SqlTransaction later...