Hi Ken,
The answer to this is slightly long drawn and depends on your specific
situation.
a) If you are using Sql Server 2005, and *only one* connection, your best
bet is to simply open the connection, wrap it in a TransactionScope and
run your commands - they will automatically enlist within a transaction -
make sure your transaction isn't promoting the MSDTC. That would be the
simplest & cleanest option.
b) If you are using SQL 2k, you can use something similar to this here -
http://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx .
c) For all other scenarios, a good idea is to probably just extend the
TableAdapter and add a BeginTransaction method on the table adapter
itself. Here is some pseudo code for it
SqlTransaction TableAdapter.BeginTransaction(SqlConnection connection)
{
// Check if connection is closed, if it is closed throw exception
// If connection is not closed, do a begin transaction on the
connection. hold the SqlTransaction instance returned in a variable
// Set every SqlCommand.Transaction in m_commandCollection to the
transaction you got in step #2
// Set the transaction on Transaction properties of
(DbData)Adapter.InsertCommand/DeleteCommand/UpdateCommand/SelectCommand
// Return the SqlTransaction object.
}
This way, you can use the TableAdapter to start/end transactions, and
ENSURE that no commands are left zombie (i.e. transaction-less when a
transaction is running). Once you have done this, your usage of the
TableAdapter becomes very simple
SqlTransaction tran = adapter.BeginTransaction(whateverSqlConnection) ;
// do some work
tran.Commit() ; -)
(... Now I got a Question for you .. Why isn't there a BeginTransaction on
a TableAdapter to begin with?
.. )
BTW, I am sure there may be other ways to do this, but the above seems
neat and clean. Once you actually do write the code, you will see that it
uses no tableadapter-specific objects, i.e. it can be easily machined in.
d) Word of caution --- If you are using SQL2k5 and decide to use
System.Transactions - don't wrap TableAdapter.GetData and
TableAdapter.Update within the same transactionscope for various reasons I
can go into if you are interested
Hope this helped
- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx