Re: TableAdapter with Transactions

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
----------------------------------------------------------------------------




"Ken Getz" <Ken Getz@xxxxxxxxxx> wrote in message
news:OzeS1ydzFHA.464@xxxxxxxxxxxxxxxxxxxxxxx
>I can see a BeginTransaction in TableAdapter Connection.
> But I don't see a CommitTransaction.
>
> How do I use database Transaction with tableadapter
>
> Ken
>


.

Relevant Pages

  • Re: Transactions + multiple table adapters
    ... TA's connection by using the Connection property. ... Public Shared Sub SetTransaction(ByVal tableAdapter As Object, ... For Each command As SqlCommand In commandCollection ... stored procedure on the second table adapter - all within a transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Concurrent Access in Transactions
    ... When beginTrasactionfunction is invoked from a specific connection, ... p_Description As String, ByVal p_ItemImage As HtmlInputFile, ByVal ... sqlConn, sqlTransaction) ... Is there any way to maintain transaction integrity, ...
    (microsoft.public.dotnet.languages.vb)
  • Setting Locks on Rows in MSSQL Database (Currenlty Unsolvable Problem and Trying)
    ... I am beginning a transaction from PHP and setting a lock on a record ... I then commit the transaction I started when the user clicked ... There is no way in PHP where I can start a persistent ms sql connection ...
    (alt.php)
  • TransactionScope Transactions not commiting
    ... be) using the same SQL Connection. ... and dtermines the Connection String to use to create the connection to ... MyClass obj = new MyClass; ... not involved in a Transaction. ...
    (microsoft.public.dotnet.framework.adonet)