Transactions in VB.Net

I have a question regarding tranasctions across multiple tables in the same OleDB database, and also across multiple tables in multiple OleDB databases. I also work with SQL Databases in the same application, but work on tables in those databases tend to be individual acts so do not (currently) need to be in any transaction.

I have (using the designers) created several DataTable and associated TableAdapter object in a single DataSet (somewhere in the region of 20). Each of these tables is connected to its appropriate DataSource and data can be previewed successfully. Several, of the TableAdapters have multiple queries attached meaning that all of the inserts, updates, and deletes to these tables can be done through the TableAdapter. Each of the queries has been tested individually and works.

I now have the need to wrap certain groups of database actions within a transaction to improve reliability and consistency. The issue I am running into is that although the Tables/Adapters in the Dataset link to the same database, each seems to have its own connection that seems independent of the others (I have tested this by manually opening the connection on one TableAdapter and then testing the State on the others - which are all still closed).

Now, it appears that a transaction must be started on a specific connection ..... but I need the transaction to work across multiple tables (and even databases in some cases!). I seem to be in a bit of a catch 22. Each connection seems to need its own transaction, which means in some cases I would be dealing with ~12 transactions. All seems very cumbersome.

There has to be a better way - I can't possibly be the first person that has needed to update multiple tables in the context of a transaction.

One way I have thought would be to manage my own connection on which I can start a transaction and then use DBCommand objects to excute the required SQL commands on the connection. This way I would only have one transaction per database, so at most I would be managing two transactions.

I have done some research and found documents about TransactionScope objects and Distributed Transactions, which seem like they may be the way forwards - but I am not 100% sure which is the best/most efficient way forwards. All I know is managing 12+ transactions myself is going to blow up in my face!

A Transaction is atomic, and can only be attached to a single connection, but you can run multiple transactions at the same time, especially if you're connecting to different databases. If you do that,

A Transaction is atomic, and can only be attached to a single connection, but you can run multiple transactions at the same time, especially if you're connecting to different databases. If you do that, then you either Commit or Rollback ALL transactions, based on the success/failure of a SINGLE transaction.

So you'd do this:

Dim trn1 as SQLTransaction
Dim trn2 as SQLTransactions
Using con1 As New SQLConnection("connect string")
con1. Open
trn1 = con1.BeginTransaction
Using con2 As New SQLConnection("Connnect string")
con2.Open
trn2 = con2.BeginTransaction
<your code here>
If <your code succeeds for con1> Then
If <your code succeeds for con2> Then
trn1.Commit
trn2.Commit
Else
trn1.Rollback
trn2.Rollback
Else
trn1.Rollback
trn2.Rollback
End If
End Using
End Using

Thanks for the replies, but both really just suggest options I mentioned in my original post.

Phillip's suggestion to start a single transaction, do your work, and then commit/rollback that transaction (as you would do on the DBEngine object in Access) is pretty much my suggestion of managing a connection per database and then using DBCommands to do the work and then commit/rollback those transactions. You would still need one transaction per database. MSAccess transactions clearly have some intelligence built in that manages the transactions across multiple databases for you as a single unit meaning that you do not need to worry about this stuff. However, with VB.NET transactions can be associated with a single connection as Scott says.

Scott's suggestion also matches something from my original post whereby you have a transaction per connection. My issue here was that as my TableAdapters each have their own independent connection, if I were to update every table I had within a transaction then I would be managing 20 transactions! I realise this was an option, but was convinced that there was a better and more efficient way of managing this.

As I said, I found an MSDN article relating to a TransactionScope object, and the implication of the document was that you can create a TransactionScope object and then any connection that is opened within the scope of this is automatically enrolled as a lightweight transaction. You then complete() the TransactionScope and it manages the commit actions to all of the connections that are associated with it as a unit, and an abort() call manages the rollback actions as a unit. There was also another article on Distributed Transaction Components, which if I am honest I did not really follow on the first reading.

I remain convinced that there must be a better/cleaner/more efficient way of managing such transactional requirements. If it transpires that I have to manage them all myself, then I think the preferred option would be to move away from the TableAdapters for the Insert, Update, and Delete options and manage these via DBCommands against a single connection per database. At least that way I minimise the number of transactions I need to manage at any one time to the number of databases (1 per database instead of 1 per table).

TableAdapterManager reduces the code that is required for saving data in multiple related tables from several routines with many lines of code to a single method call: TableAdapterManager.UpdateAll(TypedDataset). The TableAdapterManager provides all the underlying logic that is required to maintain referential integrity during update (save) operations between the dataset and the database. For more information,

Again, thanks for the comments. I cannot use a stored procedure as the OleDB databases I am running against are Access Databases that do not have these (as far as I am aware). .NET is also sensitive about what can be in an Access Query and it be visible through the connection. As soon as you use any function in a query in Access (such as Nz() etc) the query becomes invisible through the .NET database connection.

Regards Gustav's comment. Some of the tables I am working with are hierarcical so this would work in those cases. However, some are not parent/child relationships - but more of an indirect relationship. Trying to give an example of what I mean - the manufacture of a component X starts, so the scheduled manufacture record needs to be updated to show this. This component is related to a Works Order, which does not need a direct update, but provides a link to records in the Bill Of Materials that need to be flagged as allocated (BOM Records need updating), and then the linked product stock levels need down-dating. The link between the Manufacture Record for the Component and the Bill Of Materials is indirect via the Works Order (the Works Order has materials, not the individual component).

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

You describe a sequence of updates. I see no problem inserting the steps of this in code similar to the UpdateDB sample code of the first link.
If the Try block fails, you won't call AcceptChanges() and nothing is saved.

I have read through the text from the link again and it has refreshed my mind as to what I thought the issue would be (I incorrectly described it in by initial reply).

There is a highlighted portion of text that states:

"Calling AcceptChanges will not replicate these changes back to the data source if the DataSet was filled using a DataAdapter. In that situation, call Update instead."

This is exactly the situation I have - my data is loaded through TableAdapters (essentially DataAdapters). I take this section of text at what it says directly, namely that the changes will only be in the dataset itself after the AcceptChanges() call, and not in the database. To update the actual data in the datasource I will need to call the .Update method of each affected TableAdapter. Doing this still treats each table individually, and exposes me to non-transactional updates in that some updates may work, and then one fails. By that time the prior update calls are completed and done and I have database inconsistency.

This still makes me think that I need a transaction wrapped round all this to make the updates a single unit of work on the database.

I am starting to think that the way forward is as per my original idea (and that suggested by Scott), which is to use the TableAdapters for read-only data access, and to then do data updates via an alternate connection to the database that I create and manage myself in code on which I have a transaction. I can then fire SQL at it as required and have a transactional unit of work to commit or roll back.

I may well be missing something here as I am very new to handling this stuff in VB.Net. I have written many applications in VB.NET now, some quite complex, but this is the first to nead transactions. Each new learning presents a wall to get over!

In reading further about the TransactionScope class, it would seem that's the answer to your question. Essentially, you create a TransactionScope code block, then open the connections within that block, do your work using those connections, and then Complete the TransactionScope.

I have accepted a number of comments as the solution. The solution I have gone with for the time being, due to time constraints, is one that I suggested in my own initial post but was then also suggested by Scott.

I have implemented a Database Connection and Transaction per database at the application level, and these are used throughout the application for updates, deletes, and inserts - all within the context of the transaction on the database.

The multiple transactions are treated as a unit to ensure that (as far as possible with this crude implementation) all commit or all rollback. I accept that there is still technically a hole in that one commit may work and another may fail (meaning that if the first succeeds and then the second fails it is too late to rollback the first ....), but I will have to live with that for now.

When time permits I will advance my knowledge with reading about TransactionScope and the additional subjects suggested by Gustav, and may come back to this to implement a better solution.

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string.
Specify the first argument, which is the expression to be returned:
Specify the second argument, which …

With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship.
Add the tables:
Create the relationship:
Decide if you’re going to set referential integrity:
Decide if you want cascade upda…