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!
Any suggestions about how to best manage this scenario ??