How to implement transactions using tableadapters?

Question

The old dataadapter had a transaction object which you could assign to a .NET SQLtransaction.  This made transaction handling very simple.

What is the equivalent (or similar) method for handling transactions using tableadapters?

I'm using the table adapters generated by the data designer.

Answer 1

dataadapter has no transaction property which can be assigned to a sqltransaction.

You could use System.Transactions.TransactionScope to simplify the transaction handling if you have only one conneciton opened.

Answer 2

I'm looking for a solution similar to this one posted in the online documentation:

dcNorthwind.Open()

Dim transaction1 As SqlClient.SqlTransaction
transaction1 = dcNorthwind.BeginTransaction()

daCustomers.UpdateCommand.Transaction = transaction1
daOrders.UpdateCommand.Transaction = transaction1

Try
    daCustomers.Update(dsNorthwind1)
    daOrders.Update(dsNorthwind1)
    transaction1.Commit()

Catch dbcex As DBConcurrencyException
    MessageBox.Show("Update Failed, Concurrency Exception")
    transaction1.Rollback()

Catch ex As Exception
    MessageBox.Show("Update Failed")
    transaction1.Rollback()

Finally
    dcNorthwind.Close()
End Try

Thanks in advance to anyone who can answer this question.

Answer 3

You cannot do the similary thing with TableAdapter because the update command property is not exposed for coding.

 

As I said before, introducing a transacationscope() is a good way if you use TableAdapter.

Answer 4

Can you provide an example?

Thanks.

Answer 5

using System.Transactions;

 

...

using (TransactionScope ts = new TransactionScope()) {

// write you code here to handle tableadapter.

ts.Complete();

}

...

Answer 6

Thanks.

I tested it and it seems to work fine.  Is this new to the 2.0 framework?  Are there any gotchas when using this method?

Answer 7

yes, this is new in the .NET Framework version 2.0.

 

By using it, you are creating a scope where a transaction will exist and will automatically commit or roll back.

 

You can find more about this class from MSDN docs:

http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope(VS.80).aspx

Answer 8

A tableadapater have a settable .Connection property. Thus, you can alternatively assign an open connection that is already enlisted in a transaction to the tableadapter.

And, yes, there are some drawbacks, mainly that the lightweight transaction model (LTM) only supports SQL Server 2005, and then only with a single open connection to a single database. It will "promote" the transaction into a full MS DTC transaction otherwise. More info about LTM vs DTC: http://kjellsj.blogspot.com/2006/01/systemtransactions-ltm-promoting-to.html

This will affect you when you need to update two tableadapters, e.g. master-details data.

Answer 9

Actually, I think it's really common in updating master-detail data. So, is there any work-around exist to handle the problem of auto promotion. I've read some messages from MS said that it's by design. However, I don't think it really fits the need of normal usage.

Answer 10

check out the DbConnectionScope posted by Alazel Acheson on the ADO.NET team blog:
http://blogs.msdn.com/dataaccess/archive/2006/02/14/532026.aspx

Answer 11

Saludos amigos...
Esto esto parece increíble y quisas muy difícil, pero realmente es mucho mas facil de lo que se inmaginan. No crean que esto lo descubri yo (no me sente bajo un arbol y una manzana me golpeo la cabeza) simplemente busque en Internet, conseguí varias personas y paginas web que preguntaban y deseaban hacer lo mismo que yo queria. Comenze a probar e investigar la mayoria estaban perdidos, solo unas pocas paginas web se acercaban a esto. 

Las transacciones van pegadas a los objetos command de los TableAdapter (Insert, Update, Delete) dependiendo del caso…

Pueden encontrar el articulo completo en:
http://codigonet.blogspot.com/2007/03/implementando-sqltransaction-en-un.html

http://labloguera.net/blogs/elperucho/archive/2007/03/31/implementando-sqltransaction-en-un-tableadapters.aspx

Disculpen porque no esta en ingles, pero realmente es muy facil de entender.. por las imagenes y los codigos.

Greetings friends…
This this seems incredible and very difficult, but really it is much more easy of which they are inmaginan. They do not create that this descubri I (seated under a tree and an apple to me I do not strike the head) simply looks for in Internet, I obtained to several people and pages Web that asked and wished to do just like I queria. Beginning to prove and to investigate the majority they were lost, single few pages Web approached this. The transactions go stuck to the objects command of the TableAdapter (Insert, Update, Delete) depending on the case… They can find I articulate complete in:
http://codigonet.blogspot.com/2007/03/implementando-sqltransaction-en-un.html

http://labloguera.net/blogs/elperucho/archive/2007/03/31/implementando-sqltransaction-en-un-tableadapters.aspx

Excuses because not this in English, but really is very easy to understand. by the images and the codes.