Hi

I've read various articles/blogs/posts about adding transactions to
TableAdapters, mainly from Sahil Malik. I'm certainly not an expert in
ADO.NET but I understand what transactions, commands and connections are.
Sadly I've been unable to get the examples I found to work.

I want to be able to work with 2 or more TableAdapters within one
transaction. I understand that I need to extend the appropriate TableAdapter
partial classes. This isn't too much of a problem because I only have 10 or
so database tables and TableAdapters  to worry about.

I don't think I want to use System.Transaction.TransactionScope. I'm only
concerned with one database. I would like to continue to use TableAdapters
(because I've already invested time implementing for non-transactional
uses).

Another thing: I need to do quite a bit of manipulation of the DataTable
between Filling and Updating using the TableAdapter(s) so it makes sense to
do this in my business logic layer. However, if I need to work with
transactions then the business layer is no longer 'Data Provider neutral',
i.e. in my case, I need to work with the System.Data.SqlClient namespace. I
don't suppose this is a real world problem for me but if a customer did want
to use Oracle with our application then we have to create a new DAL and BLL.

Is there a tried and tested solution out there yet that someone would be
good enough to share?

Thanks
Andrew

<---- That would be a good approach.

You could also write something like a factory that ties all TableAdapters in
a transaction.

- Sahil Malik
http://blah.winsmarts.com

Hi Sahil

I've poked around a bit more to see what the TableAdapter is doing and
managed to get the commands enlisted in a transaction for one TableAdapter.
Now I just need to work out how to add another one.

I took delivery of your ADO.NET book yesterday so I hope to advance my
understanding of this wondrous technology soon.

But this I fear may well be beyond me for a while yet, although I do like
the idea.

Thanks
Andrew

Andrew,

I hope you like the book - even though I've advocated against TableAdapters
in Chapter 3 :) for the very reasons you are running into.

Anyway, the idea of factory would be something as simple as creating an
EnlistInTransaction method on each TableAdapter. You could then have each
TableAdapter implement a specific custom interface, and then you could
create a class on top that calls EnlistInTransaction for every instance of
that interface passed in.

This way, you can easily enlist multiple table adapters in one tx.

- Sahil Malik [MVP]
http://blah.winsmarts.com




Show quoteHide quote

Hi

Yes, the lack of support for transactions has caught me out a bit.

OK. I thought I could loop through a dictionary collection of say
'ITransTableAdapter' interfaces. The business layer can add all the TAs in
and then call them by the key once they have been enlisted in the
transaction.

Can I start a transaction on the first adapter in the loop and then pass it
to each subsequent adapter in the collection? Can you give me an idea of how
the EnlistInTransaction method might look?

Does this sound reasonable? It's a bit messy using keys but I can't think
how else to do it.

I think it's starting to look like a workable plan. Your comments will be
very much appreciated.

Thanks again
Andrew

Yep that sounds awrite. (Also see a comment about DbConnectionScope below).

Yeah that should work out just fine.

It should be very similar to a single table adapter scenario; take in a
transaction object, and a connection object. The connection needs to be
open.

BTW - Alazel Acheson from the MSFT ADO.NET team once blogged about something
called DbConnectionScope; (just search on it), he used an interesting design
pattern that you may find useful as well. His stuff was specific to Sys.Tx
though; but no reason why you couldn't use the same paradigm.

- Sahil Malik [MVP]
http://blah.winsmarts.com






Show quoteHide quote

Hi

I've knocked up some code which I've included here for anyone who might
benefit and also to get comments on how it might be improved.

The main class is called the AdapterTransactor which sits in the DAL. It
adds one or many adapters into a transaction. They must implement the
ITransTableAdapter interface and include the Transaction and OpenConnection
methods. It all seems to work ok although I haven't done much testing. I
like the solution because my BLL remains Data Provider neutral and fairly
uncluttered. The obvious downside is that you have to add the methods to any
adapters which may need to work in a transaction. It is only copying and
pasting though.

Thanks to Sahil for the advice.

Andrew.

public interface ITransTableAdapter
    {
        SqlConnection OpenConnection();
        void Transaction(SqlTransaction tran);
    }

    public partial class MyTableAdapter : System.ComponentModel.Component,
ITransTableAdapter
    {
        public SqlConnection OpenConnection()
        {
            if (_connection == null)
            {
                InitConnection();
            }
            if (_connection.State != ConnectionState.Open)
            {
                _connection.Open();
            }
            return _connection;
        }

        public void Transaction(SqlTransaction trans)
        {
            // must use the connection from the transaction object
            _connection = trans.Connection;

            if (_adapter == null)
            {
                InitAdapter();
            }
            if (_adapter.InsertCommand != null)
            {
                _adapter.InsertCommand.Transaction = trans;
            }
            if (_adapter.DeleteCommand != null)
            {
                _adapter.DeleteCommand.Transaction = trans;
            }
            if (_adapter.UpdateCommand != null)
            {
                _adapter.UpdateCommand.Transaction = trans;
            }
            if (_adapter.SelectCommand != null)
            {
                _adapter.SelectCommand.Transaction = trans;
            }

            if (_commandCollection == null)
            {
                InitCommandCollection();
            }

            foreach (SqlCommand cmd in _commandCollection)
            {
                cmd.Transaction = trans;
            }
        }
    }


public class AdapterTransactor
    {
        private SqlConnection conn = null;
        private SqlTransaction trans = null;
        private List<ITransTableAdapter> adapterCollection = new
List<ITransTableAdapter>();
        private bool hasStarted = false;

        public void AddAdapter(ITransTableAdapter adapter)
        {
            adapterCollection.Add(adapter);
        }

        public void BeginTransaction()
        {
            if (adapterCollection.Count < 1)
            {
                throw new ArgumentOutOfRangeException("No adapters have been
added.");
            }

            int counter = 0;
            foreach (ITransTableAdapter adapter in adapterCollection)
            {
                counter++;
                if (counter == 1)
                {
                    // open the connection on the first adapter in the list
                    conn = adapter.OpenConnection();
                    // begin a transaction on the connection
                    trans = conn.BeginTransaction();
                    // call the adapter transaction method to enlist the
command objects
                    adapter.Transaction(trans);
                }
                else
                {
                    adapter.Transaction(trans);
                }
            }
            hasStarted = true;
        }

        public void Commit()
        {
            if (!hasStarted)
            {
                throw new ApplicationException("BeginTransaction must be
called before Commit.");
            }

            try
            {
                // Commit the transaction
                trans.Commit();
            }
            catch (Exception ex)
            {
                // Rollback if it all goes wrong
                trans.Rollback();
                throw (ex);
            }
            finally
            {
                if (conn.State == System.Data.ConnectionState.Open)
                    conn.Close();
            }
        }

// In your BLL, or wherever, you can then create an AdapterTransactor object
and use like this:

AdapterTransactor atx = new AdapterTransactor();

atx.AddAdapter(adapter1);
atx.AddAdapter(adapter2);
atx.AddAdapter(adapter3);

atx.BeginTransaction();

// transactional work on adapters

atx.Commit();

Oops! I said I hadn't done much testing. You need to add a rollback method
to the AdapterTransactor class.

public void Rollback()
{
    // Rollback if it all goes wrong
    trans.Rollback();
    if (conn.State == System.Data.ConnectionState.Open)
    conn.Close();
}

Then put your adapter transactions is a try catch block

AdapterTransactor atx = new AdapterTransactor();

atx.AddAdapter(adapter1);
atx.AddAdapter(adapter2);
atx.AddAdapter(adapter3);

atx.BeginTransaction();

try
{
    // transactional work on adapters
    atx.Commit();
}
catch (Exception)
{
    atx.Rollback();
    throw;
}

Otherwise  Rollback will never be reached if there's an exception.

Thanks
Andrew

Hi,

I have tried your solution on my project. There are four tables in sql server 2005 express, that form a tree (table1 has foreign key to table2, table2 to table3, and table3 to table4)

Firstly, I insert into table4, then table3, table2 and last table1. But when inserting into table1, trouble arise as it stated time out

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

When I change the sql table relation (enforce foreign key constraint to "no") for the last table(table1). it works alright, but it's not what I want to ( it didn't check the integrity)

Please advise
Thanks, appreciate all reply

Sincerely,

Moniq

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

binding tables in many-to-many relationships
comparison reporting -- technique suggestions?
.NET 2 DataTable SLOW
Best way to create a database
Addign a row to a dataset