Hey all!

While developing the Data Access Layer (DAL) in my "home developing" project - Haverut.co.il I deliberated wheather to use the old fashioned way - the Application Blocks of Microsoft, which I need to implement all the database contact by hand and use the 'jacket' of the Application Blocks adapters OR to use Typed datasets...
I decided to use the second choice, because (as knows) this module knows to generate code of database tables and to create by default the main CRUD (Create, Read, Update and Delete) methods and also custom SQL queries.

But this is not the main issue of this post...

While working, I needed to do several connected commands against the database, therefore I needed to use the Transaction term to knot these commands and to avoid database's commands failure or 'half-way actions'.

Now, the biggest deliberation: To use SQLTransction module or TransactionScope module (which is new module that cane out in .NET 2.0)?

In the first thought I decided to use TransactionScope, because it's very easy to use and it doesn't make "pain in the neck". The usage it easy: you need to wrap the wanted scope with this module and all the job will be done safely under this transaction.
further documentation you can find here:

• http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx
• http://msdn2.microsoft.com/en-us/library/ms172152.aspx

But this module holds not much of disadvantages like:

• Low performence of this action in the application, in large amount of users and actions the performance of this actions will be very bad and slow.
• By default, when using this module, the system tries to look for a transaction that is otherwise current, or a TransactionScope object that dictates that Current (a static property of this namespace) is null. If it cannot find either one of these, System.Transactions queries the COM+ context for a transaction. Note that even though System.Transactions may find a transaction from the COM+ context, it still favors transactions that are native to System.Transactions. This thing is not recommended because we need to handle the COM+ context in addition to our application context. More info you can find here.

Because of that, I decided to use SQLTransactions over my Typed Datasets' actions.

To do this in appropriate way, I used a partial class (very nice innovation in .NET 2.0) with the same name of the Typed DS class, to 'continue' its code and overload some of the members like the main member that does the connection with the database: _adapter.
This member is private and is not accessible to outside requests.

Instead of a BeginTransaction method, I have implemented a Transaction property on my TableAdapters, like this:

partial class CitiesTableAdapter
{
   public SqlTransaction Transaction
   {
      get { return _adapter.SelectCommand.Transaction; }
      set
      {
         if (_adapter == null)
         {
            InitAdapter();
         }

         _adapter.InsertCommand.Transaction = value;
         _adapter.UpdateCommand.Transaction = value;
         _adapter.DeleteCommand.Transaction = value;
      }
   }
}

This property assigns the given transaction to the Transaction property on all its commands. Now I can do CRUD method as I like with knowing that is under SQLTransaction control:

CitiesTableAdapter citiesAdapter = new CitiesTableAdapter();

citiesAdapter.Connection.Open();
try
{
   SqlTransaction trans = citiesAdapter.Connection.BeginTransaction();
   try
   {
      citiesAdapter.Transaction = trans;

      // CRUD the table, commit transaction or rollback if there's a problem

Nice way of implementing, hope it helped someone...

See you