Solved

Transactions in VB.Net

Posted on 2015-02-17
13
291 Views
Last Modified: 2016-02-11
Hi,

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 ??

Matt
0
Comment
Question by:matthewgreen
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40613766
I'm no expert in VB.Net, but one thing that comes to mind.

Can you start with a

BEGIN TRAN

and end with a

COMMIT TRAN

statement?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 334 total points
ID: 40613784
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
0
 

Author Comment

by:matthewgreen
ID: 40613853
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).

Matt
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40613859
Can you use a stored procedure instead?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40613917
I think what you request is precisely described here at MSDN:
How to: Update Data by Using a TableAdapter

and here (link from the page above) about the TableAdapterManager:
Hierarchical Update Overview
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,
/gustav
0
 

Author Comment

by:matthewgreen
ID: 40613976
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).

Hope that makes sense.

Matt
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

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)

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40614001
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.

/gustav
0
 

Author Comment

by:matthewgreen
ID: 40614009
Okay Gustav - I will look at the code in the link again.

Matt
0
 

Author Comment

by:matthewgreen
ID: 40614042
Gustav,

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!

Access VBA always made transactions so easy in the past.

Matt
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 334 total points
ID: 40614066
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.

This is probably the article you're referring to, but I'm including it here for others: https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 166 total points
ID: 40614115
I see. But then you should move ahead and consider Entity Framework 6 and TransactionScope:
Entity Framework transaction scope examples

I haven't used this but it can be expanded to cover connection issues:
Managing Transactions in Entity Framework 6

I think the learning curve will pay off compared to a custom built system.

/gustav
0
 

Author Closing Comment

by:matthewgreen
ID: 40627685
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.

Thanks for all the inputs and suggestions.

Matt
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40627701
OK, thanks for the feedback.

/gustav
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now