The definitive TableAdapters + Transactions blog post

Posted on 7/2/2006 @ 12:13 AM in #Vanilla .NET by | Feedback | 23094 views

I have at numerous times been asked the question, “How can I make TableAdapters Transactional”.

To set things straight, and this is in context with a discussion I was having with Steve Lasker at TechED 2006, Boston, TableAdapters are there to make your life “simple”. As a matter of fact, initially TableAdapters did expose all the goo – Connection, Transactions etc. But then, that doesn’t make things simple. TableAdapters in their current RTM implementation hide all those intricacies from you, and make things simple. But many of us advanced developers wish to have more power/knobs/properties - whatever you wanna call them. As an advanced developer you can extend table adapters using partial classes anyway, so you now have the best of both worlds.

As a result, the final implementation of TableAdapters in .NET 2.0 RTM, hides all that complexity from you; and considering partial classes extensibility, in my opinion that is a fair tradeoff.

In certain instances however, you may need the complexity. Two good examples are “Transactions” and “Connection lifetime management”.

Let us examine these by a simple blogpost that makes a simple TableAdapter transactional. For the purposes of this blogpost, I have setup a simple TableAdapter, targeting the Customer’s table in the Northwind database. I also have a CustomersTableAdapter. This can be seen in the figure below.

Okay good!! Now let’s say that you are trying to make the “Update” operation transactional. There are two ways you can go about to solve this problem

Implementing Transactions using SqlTransaction


For case #1, I am going to ignore System.Transactions and implement a plain vanilla ADO.NET transaction implementation.

The easiest way to make your TableAdapter transactional, is to extend it’s definition with a partial class that provides you with a “hook” to add transactional behavior into your TableAdapter as follows.

public SqlTransaction BeginTransaction(SqlConnection connection)

{

    if (connection.State == ConnectionState.Closed)

    {

      throw new Exception(

        "Connection State cannot be closed for a new transaction") ;

    }

 

    SqlTransaction tran = connection.BeginTransaction() ;

    foreach (SqlCommand cmd in _commandCollection)

    {

        cmd.Transaction = tran ;

    }

 

    return tran ;

}

As you can see above, I am looking to see if the connection is currently open, as beginning a transaction on a closed connection is like a gay couple trying to conceive. Thus if the connection is closed, I throw an exception, otherwise I begin a transaction, and enlist all commands in the Tableadapter in the transaction. Of course, your implementation may be specific to your case, but this as a general case will work.

Now it is obvious that per the above implementation, you will also need to provide hooks to open and close connections, i.e. manage connection lifetime yourself. This can be done by adding two quick methods to the partial class definition as follows –

public SqlConnection OpenConnection()

{

    if (_connection.State != ConnectionState.Open)

    {

        _connection.Open() ;

    }

    return _connection ;

}

 

public void CloseConnection()

{

    _connection.Close() ;

}

The final code for the partial class definition looks like as follows –

public partial class CustomersTableAdapter : System.ComponentModel.Component

{

    public SqlConnection OpenConnection()

    {

        if (_connection.State != ConnectionState.Open)

        {

            _connection.Open() ;

        }

        return _connection ;

    }

 

    public void CloseConnection()

    {

        _connection.Close() ;

    }

 

    public SqlTransaction BeginTransaction(SqlConnection connection)

    {

        if (connection.State == ConnectionState.Closed)

        {

            throw new Exception(

                "Connection State cannot be closed for a new transaction") ;

        }

 

        SqlTransaction tran = connection.BeginTransaction() ;

        foreach (SqlCommand cmd in _commandCollection)

        {

            cmd.Transaction = tran ;

        }

 

        return tran ;

    }

}

With this much setup, the “transactional” usage of this TableAdapter becomes pretty damn simple. Here is a code sample –

CustomersTableAdapter tableAdap = new CustomersTableAdapter() ;

 

SqlTransaction tableAdapTran = null ;

SqlConnection tableAdapConn = null ;

 

try

{

    tableAdapConn = tableAdap.OpenConnection() ;

    tableAdapTran = tableAdapConn.BeginTransaction() ;

    // Do your transactional work

    tableAdapTran.Commit() ;

}

catch (Exception)

{

    tableAdapTran.Rollback() ;

}

finally

{

    tableAdapConn.Close() ;

}

The above will work, but is a tad bit cumbersome considering that you “do” need to write all that code. A simpler implementation can be achieved using System.Transactions.

Implementing Transactions using System.Transactions

 

I recommend reading up my quick primer on System.Transactions before diving into this implementation.

A much simpler implementation to the above would be to simply wrap a singular operation inside a System.Transactions.TransactionScope. This is shown as below –

using (TransactionScope tsc = new TransactionScope())

{

    //Do your transactional work.

    tableAdap.Update() ;

    tsc.Complete() ;

}

 

IMPORTANT NOTE: I said “Singular” operation. Why? Because if you are working with plural operations inside one TransactionScope, i.e. “GetData” and “Update” both inside a single TransactionScope, or two Update’s within a TransactionScope, you will effectively open two SqlConnections to the single database, and thus unnecessarily promote the transaction from LTM to MSDTC. As a best practice, ALWAYS wrap only a singular operation inside a TransactionScope. Should you choose to wrap multiple operations inside a single TransactionScope, you must in that case manage connection lifetime yourself by extending the partial class definition. In other words, the following code will cause the transaction to promote –

using (TransactionScope tsc = new TransactionScope())

{

    tableAdap.GetData() ;

    //Do your transactional work.

    tableAdap.Update() ;

    tsc.Complete() ;

}

 

But the following code is just fine –

using (TransactionScope tsc = new TransactionScope())

{

    tableAdap.OpenConnection() ;

    tableAdap.GetData() ;

    //Do your transactional work.

    tableAdap.Update() ;

    tableAdap.CloseConnection() ;

    tsc.Complete() ;

}

(Where OpenConnection and CloseConnection are custom methods I added in the partial class definition).

This and other such use-cases you need to be careful of, are further explained in my code-magazine article. (Paid content)

IMPORTANT NOTE #2: For databases other than SQL Server 2005 (as of Sunday June 18, 2006), you must prefer to use the Local Transaction implementation over a System.Transactions implementation, since their transactions under System.Transactions will always promote to MSDTC. This is true for Oracle, SQL Server 2000, DB2, etc. On the other hand, if you have multiple databases involved in your transaction, then go with System.Transactions considering the simpler implementation, and no downside (because the transaction would have promoted anyway).

Closing Comments

Transactions are seldom as simple as “All or None”. If you had a DataTable with 5 changed rows, this translates to 5 commands to be executed during “Update”.  If Command #1 succeeds, Command #2 succeeds, and Command #3 fails, you have the following choices at this time –

a)      Stop right there and return

b)      Ignore #3, and continue with #4 and #5

c)       Stop and rollback #1 and #2

This blogpost addressed scenario #c. For a) and b) you will have to apply concepts similar to what you’d do in DataAdapter, alongwith all the code to merge delta datasets, and carrying errors across merges. Each one of these cases can be achieved using permutations and combinations of DataAdapter.AcceptChangesDuringUpdate, DataAdapter.ContinueUpdateOnError, Transactions, and DataTable.AcceptChanges.

One day in the future I’ll blog about them, or they can all be found in my book, Chapters 9,10 and 11.

 

Sound off but keep it civil:

Older comments..


On 7/4/2006 5:54:06 AM Erik said ..
Great article!


I'm wondering if it's ok to wrap the tableAdap.GetData() in a suppress transaction scope or do you still get the same scenarios you're describing in your devX article "System.Transactions and ADO.NET 2.0"?

using (TransactionScope tsc = new TransactionScope())


{

using(TransactionScope ts2 = new TransactionScope(TransactionScopeOption.Suppress))


{


tableAdap.GetData() ;


}

//Do your transactional work.

tableAdap.Update() ;

tsc.Complete() ;

}


On 7/4/2006 6:55:35 AM Sahil Malik said ..
Erik -

By using TransactionScopeOption.Suppress, you would loose the transactional behavior between GetData and Update. So that escalation won't happen, but the GetData+Update won't be transactionally sensical either. :).

I still like the idea of managing your own connection lifetime in these scenarios.

Sahil


On 7/10/2006 1:19:30 PM Geetha said ..
If I am using two table adapters tableAdap1, tableAdap2.


do we still need to change the class for the table adapters and use the Open and Close connections to avoid MSDTC promote? if yes is this code correct.

using (TransactionScope tsc = new TransactionScope())

{

tableAdap1.OpenConnection() ;

tableAdap1.Update() ;

tableAdap1.CloseConnection() ;

tableAdap2.OpenConnection() ;

tableAdap2.Update() ;

tableAdap2.CloseConnection() ;

tsc.Complete() ;

}


On 7/10/2006 1:40:45 PM Sahil Malik said ..
Geetha,

That code will cause unnecessary promotion. The idea is - you don't want to close the connection until *all* your operations within the same TxScope are complete.

Sahil


On 7/10/2006 1:56:50 PM Geetha said ..
Thanks for the reply. I like your article about the transactions.

I am new to the transactions and asp.net. I have single database involved in transaction. There are two table adapters to two different tables in the databases. I am inserting some data into the two tables(ex. tableAdap1.insert). Can you please tell me how i can do this inside transactionscope using system.transaction. Do i need to use the partial class for the two tableadapters like you described before? Anyhelp is appreciated.


On 7/10/2006 4:03:21 PM Sahil Malik said ..
Geetha,

Just modify your code to this -

using (TransactionScope tsc = new TransactionScope())


{


// Open a SqlConnection


// Assign that SqlConnection to both tableAdap1 and tableAdap2.


tableAdap1.Update() ;


tableAdap2.Update() ;


// Close the SqlConnection


tsc.Complete() ;


}

Also - make sure that tableAdap1 and tableAdap2 are using the same instance of SqlConnection. You can easily acheive this by extending the partial class.

Sahil


On 7/11/2006 11:04:09 AM Geetha said ..
How I can assign the conn to tableAdap1 and tableAdap2? How can I extend the partial class to make sure that tableAdap1 and tableAdap2 are using the same instance of SqlConnection?

Geetha.


On 7/11/2006 11:47:50 AM Sahil Malik said ..
Geetha,

I'd recommend looking into the generated TableAdapter source code, and extending it in a similar manner to what I have shown above. It's really easy - give it a try.

I will probably blog about Multiple TableAdapters _ Txn at some point in the future, just too busy right now :(. But it's on my ToDo. :)

Sahil


On 7/11/2006 1:13:25 PM Geetha said ..
Hi Sahil,

Thanks for your suggestions. Here is the code I have updated. It hope this doesn't have any downside.

using (TransactionScope tsc = new TransactionScope())


{


SqlConnection conn1 = new SqlConnection(Connection_string);


conn1.Open();


CustomersTableAdapter tableAdap1 = new CustomersTableAdapter() ;


SuppliersTableAdapter tableAdap2 = new SuppliersTableAdapter();


tableAdap1.AssignConnection(conn1);


tableAdap1.Update() ;


tableAdap2.AssignConnection(conn1);


tableAdap2.Update() ;


conn1.Close();


tsc.Complete() ;


}

Where AssignConnection is custom method I added in the partial class definition

Here is the extension of partial class I used.


public partial class CustomersTableAdapter : System.ComponentModel.Component


{

public void AssignConnection(SqlConnection cn)


{


this._connection = cn;

}

}

Similar for the SuppliersTableAdapter partial class

Geetha


On 1/10/2007 12:20:32 PM CodePosta said ..
Why not just set the ConnectionModifer property of the TableAdapter to Public and pass the SqlConnection object that was opened within the TransactionScope when you want to use multiple TableAdapters within the same TransactionScope?


On 1/10/2007 2:27:06 PM Sahil Malik said ..
CodePosta - please elaborate a bit.


On 1/10/2007 6:00:45 PM CodePosta said ..
If you open your DataSet that contains your TableAdapter and select it. There is a property called ConnectionModifier. Change that from Internal (default) to Public. Now when you create an instance of your TableAdapter in code, you can access it's Connection Property. You can then set it to a new Sql connection that you open within the TransactionScope. Perhapse some Code will help. :)

// Create an instance of an SQL connection obtained through your Web.Config...


System.Data.SqlClient.SqlConnection myConn =


new System.Data.SqlClient.SqlConnection(connString.ConnectionString);

// Create an instance of your two table adapters to be used in the same transaction.


MyTableAdapter myTA = new MyTableAdapter();


MyOtherTableAdapter myOTA = new MyOtherTableAdapter();

// Create the TransactionScope in which our TableAdapters Update() method will be called.


using (System.Transactions.TransactionScope newTran = new System.Transactions.TransactionScope())


{


myConn.Open();


// The Connection property of the TableAdapters are exposed by setting the


// ConnectionModifier in the TableAdapter's properties to Public.


myTA.Connection = myConn;


myOTA.Connection = myConn;

// call all the operations that you want to be part of the transaction


myTA.Update(dataTable);


myOTA.Update(otherDataTable);


// and finish it...


newTran.Complete();


}

myConn.Close();

Hope that helps.


On 1/11/2007 8:47:43 AM Sahil Malik said ..
CodePosta -

That would work. Like anything else, I would add though that you would need to be careful of maintaining connection open lifetimes in that statement block you posted. So, don't open a connection on a tableadapter, and then specify another open connection on top of it (and thus leave the original connex. for garbage collector - that would be bad). Also, don't leave the connex open.

SM


On 3/20/2007 7:49:52 PM Chris said ..
How do you deal with rolling back changes in the dataset in the event the transaction gets rolled back? For instance (not actual code obviously):

// you've opened and set a transaction, then proceed to call update on various tables

tableAdapter1.Update(); // succeeds, and AcceptChanges() implicitly called on table1


tableAdapter2.Update(); // table2 is not updated and AcceptChanges() is not called thereby setting success = false


...

if(success) Commit


else Rollback

In my limited testing, if the above scenario occurs, and Rollback is called, the state of table1 is left in the dataset with any and all data that was refreshed from the database; for instance, identity column values obtained during the Update. Of course, because table2 failed on its Update, AcceptChanges() was not called on that table and any identity column values remain as they were prior to calling Update(). More importantly, assuming that the identity values obtained for table1 remain valid, all the rowstates in table1 were marked as "Unchanged", so even though they are not in the database, they will not be submitted on a subsequent attempt.

How do you deal with this in your transactions?


On 3/20/2007 8:35:54 PM Sahil Malik said ..
Chris -

The answer is a complex mix of AcceptChangesDuringUpdate/ContinueOnError/ & DataSet.Merge/GetChanges.

This is all explained in Chapters 8 through 11 in my book.

Sahil


On 6/27/2007 1:16:43 PM Kanhar Munshi said ..
Sahil,

Thats the best Transaction article I've seen on the internet after a couple days of intensive searching and researching. The below line is elegant and concise(and its only a line but it solved all my problems).

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

I still find it hard to grasp that .NET would make databases so easy with their Database Wizard but not provide support (or a GUI) for transactions. They are always a little slow.

Cheers,


Kanhar


On 7/30/2007 9:27:54 AM Morten said ..
Sahil,

Nice article that inspired me a lot. However, wrapping the call to the tableadapter's update method in TransactionScope does not work on SQL Compact Edition.

How come, and is there a reason and/or workround for this?

Regards,


Morten


On 11/2/2007 10:19:07 AM Lio said ..
Thanks for the great pointers. One thing I don't understand though: your example code doesn't actually use the partial class's "BeginTransaction(SqlConnection connection)" but instead call "SqlConnection.BeginTransaction()". Typo?


On 2/28/2008 10:08:05 AM Chris Henry said ..
"Like a gay couple trying to conceive" That analogy really made me laugh! Thanks for the good info and the laugh!


On 2/28/2008 5:10:26 PM Sahil Malik said ..
LOL Chris, I got another one for you .. like a gay democrat trying to win in alabama !! :)


On 3/6/2009 7:56:57 AM Hamed said ..
Hi Sahil


Thanks for this great article, i have some trouble with this example. however i want to know is this code correct and best practice for multiple TableAdapters :

using (TransactionScope ts = new TransactionScope())


{


try


{


TestDataSetTableAdapters.t5TableAdapter adapterT5 = new TransactionScopeInsertSample.TestDataSetTableAdapters.t5TableAdapter();


TestDataSetTableAdapters.t6TableAdapter adapterT6 = new TransactionScopeInsertSample.TestDataSetTableAdapters.t6TableAdapter();

adapterT6.Connection = adapterT5.Connection;


adapterT5.OpenConnection();

adapterT5.Insert(int.Parse(this.textBox1.Text));


adapterT6.Insert(int.Parse(this.textBox2.Text));

adapterT5.CloseConnection();

ts.Complete();


MessageBox.Show("Values inserted!");


}


catch (Exception ex)


{


MessageBox.Show(ex.Message);


}


}


On 3/18/2010 7:25:32 AM Steve said ..
This is ... easy ?


I think it's everything but not easy or simple.

I'm new at ADO .NET but I have been using VB 6, Delphi, Java and C++ several years ... And I don't think this is easy.

I think we have to write much code than ever.

Why you have to write a lot of code for doing something as simple than Start and Commit a Transaction ?

Other languages with other librariries do it easier. Why a modern Development Enviroment does not autogenerate code that solve it for you ?

I think that ADO .NET could be good for solving high systems or problems, but nowadays is a big stone that we have to carry, for doing small / middle proyects. So ... If you are in a big proyect, pheraps you need an ORM, and you don't need to view/fight against ADO .NET.


On 5/14/2010 5:33:01 PM Lewis Edward Moten III said ..
With C# Extensions, things got much, much easier now. Problem before is that Data Table Adapters didn't inherit from any partial classes, and it kept writing redundant code that couldn't be centralized when initiating connections and commands. It only had the Component to inherit. This meant that if you are like many of us, you had to write tons of partial tables with the same code over and over again. Forget about that now. Just use reflection with extensions! Write the code once, and then call it against any Table Adapter in your project. Now if table adapters would inherit a common interface (such as ITableAdapter), it would make things much easier to work with rather than going through reflection to get at the internal connection property.

TransactionScope ts = new TransactionScope();


adapter.OpenConnection(); // <----- here it is!


table = adapter.GetData();


table[0].Field1 = "hello world";


adapter.Update();


adapter.CloseConnection(); // <---- there you go!


ts.Complete();

And here is the magic that makes it happen:

using System;


using System.ComponentModel;


using System.Data;


using System.Data.SqlClient;


using System.Reflection;

namespace MSDTCPromotionTest


{


public static class DataAdapterExtensions


{


public static SqlConnection GetConnection(this Component component)


{


if (component == null)


{


throw new ArgumentNullException("component");


}

PropertyInfo property = component.GetType().GetProperty("Connection", BindingFlags.NonPublic | BindingFlags.Instance);

if (property == null)


{


throw new ArgumentOutOfRangeException("component");


}

return property.GetValue(component, null) as SqlConnection;


}

public static SqlConnection OpenConnection(this Component component)


{


if (component == null)


{


throw new ArgumentNullException("component");


}

SqlConnection connection = component.GetConnection();

if (connection.State != ConnectionState.Open)


{


connection.Open();


}

return connection;


}

public static void CloseConnection(this Component component)


{


if (component == null)


{


throw new ArgumentNullException("component");


}

component.GetConnection().Close();


}

public static void SetConnection(this Component component, SqlConnection connection)


{


if (component == null)


{


throw new ArgumentNullException("component");


}

PropertyInfo property = component.GetType().GetProperty("Connection", BindingFlags.NonPublic | BindingFlags.Instance);

if (property == null)


{


throw new ArgumentOutOfRangeException("component");


}

property.SetValue(component, connection, null);


}

}


}


On 7/8/2010 2:53:50 AM Moses said ..
This article was a great help. I have an application VS 2010 and Winforms C#. The app needs to talk with an old MicroFocus COBOL app. I am using an OLEDN.NET provider from CONNX to interface with the COBOL C-ISAM files. The legacy app uses a nasty pessimistic locking schem when editing any records in their CRUD forms. Basically it grabs a record lock and holds it until the user is kind enough to exit from the form.

I simulated the same thing for now by using your first transaction sample (the partial class mods) and it works great. The only problem is that if another instance of the form is opened I open (without a transaction) two datasets via TableAdapter.Fill(Datset.TableName). As soon as the first Fill happens it releases the transaction on the other open form and releases the record lock as the result. Not sure why another form instance should do that. I guess this will happen if I open any form in my winforms app NOT just an instance of this one (have not tested that yet). How can I get the transaction to stay open even if another tabledapter is used in another instance?