TableAdapter with Transactions

  • Thread starter Thread starter Ken Getz
  • Start date Start date
K

Ken Getz

I can see a BeginTransaction in TableAdapter Connection.
But I don't see a CommitTransaction.

How do I use database Transaction with tableadapter

Ken
 
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
 
Do like this

// Start a local transaction.
SqlTransaction myTrans = myConnection.BeginTransaction();

// Enlist the command in the current transaction.
SqlCommand myCommand = new SqlCommand();
myCommand.Transaction = myTrans;

try
{
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription)
VALUES (100, 'Description')";
myCommand.ExecuteNonQuery();
myCommand.CommandText = "Insert into Region (RegionID, RegionDescription)
VALUES (101, 'Description')";
myCommand.ExecuteNonQuery();
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
}
catch(Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
 
If i am not mistaken DataAdapter have on the commands.
Select,update or any other only the commit.
 
every dataadapter have commands right
a selectcommand
a updatecommand
and so on
What I was saying is that the commands have a commit only no other
transaction method
 
Thanks Malik, It works.

Ken


Sahil Malik said:
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
 
Sahil:

Just to be clear--the original question wasn't from the Ken Getz you
know. <g> There's apparently two of us, working in the same area. But
thanks for the careful explanation, anyway! -- Ken (the one you know)
 
Back
Top