Re: TableAdapter with Transactions

Thanks Malik, It works.

Ken


"Sahil Malik [MVP]" <contactmethrumyblog@xxxxxxxxxx> wrote in message
news:uBFxNDfzFHA.3856@xxxxxxxxxxxxxxxxxxxxxxx
> 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)
  • Re: TableAdapter with Transactions
    ... bet is to simply open the connection, wrap it in a TransactionScope and run ... sure your transaction isn't promoting the MSDTC. ... SqlTransaction TableAdapter.BeginTransaction ... This way, you can use the TableAdapter to start/end transactions, and ENSURE ...
    (microsoft.public.dotnet.framework.adonet)
  • 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)