How to implement transactions using tableadapters?

How to implement transactions using tableadapters?

• prashantm

  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.

  
  
  

• avanderlaan

  Can you provide an example

  Thanks.

  
  

• Irina777

  Saludos amigos...
  Esto esto parece increible y quisas muy dificil, 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, consegui 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.

  
  
  
  

• Dave Irovic

  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

  
  
  

• b_shpungin

  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.
  
  

• waiwaipang

  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

  
  

• DenisG

  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.
  
  

• E Ingram

  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.

  
  
  

• Gilbee

  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
  
  
  

• sarel206

  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.

  
  

• Anthony Borton

  using System.Transactions;

  ...

  using (TransactionScope ts = new TransactionScope()) {

  // write you code here to handle tableadapter.

  ts.Complete();

  }

  ...

  
  
  
• How to implement transactions using tableadapters?