TransactionScope not working
TransactionScope not working
TransactionScope not working
TransactionScope not working
TransactionScope not working
TransactionScope not working TransactionScope not working TransactionScope not working TransactionScope not working TransactionScope not working TransactionScope not working TransactionScope not working TransactionScope not working
TransactionScope not working TransactionScope not working
TransactionScope not working
Go Back  Xtreme Visual Basic Talk > > > TransactionScope not working


Reply
 
Thread Tools Display Modes
  #1  
Old 05-21-2014, 03:41 PM
AlanS AlanS is offline
Newcomer
 
Join Date: May 2003
Location: Philadelphia, PA, US
Posts: 18
Default TransactionScope not working


I'm building a VB.Net 2005 application. At present it opens all database connections when the startup form loads, and closes them when that form unloads. I have a Sub that performs various table inserts, updates and deletes, all via calls to other Subs that actually do the work, and all on the EPConn database connection. I want to use a transaction to ensure that all these database changes succeed or fail as a group. I'm using this code:

Code:
Using TransScope As New TransactionScope
  Try
    'calls to database modification routines begin here
    '...
    Err.Raise(513, "[source info]", "[dummy error description]")
    'additional calls to database modification routines begin here
    '...
    TransScope.Complete()
  Catch ex As Exception
    Msgbox(ex.Message)
  End Try
End Using
When I run this code, only the first group of database modifications are executed, the dummy error message is displayed, and the call to the Complete() method is NOT reached, all as expected. However, the TransactionScope doesn't work, because the changes made by the first group of database modifications are still persisted in the database.

When I change this, by moving the EPConn.Open() call to immediately following the Try statement, TransactionScope works as expected, but leaving it there will require unwanted changes in the application design.

Is there any way to make TransactionScope work with already open connections? I can't find anything in the Help screens that in any way addresses the placement of the connection opening as an issue).
Reply With Quote
  #2  
Old 05-21-2014, 06:36 PM
PlausiblyDamp's Avatar
PlausiblyDampTransactionScope not working PlausiblyDamp is offline
Ultimate Contributor

Forum Leader
* Expert *
 
Join Date: Nov 2003
Location: Newport, Wales
Posts: 2,058
Default

If your connection is already open you can use the EnlistTransaction (http://msdn.microsoft.com/en-gb/libr...ansaction.aspx) method of the SqkConnection instance.

I would tend towards just creating / opening the connection as I need it it rather than maintaining a connection for the duration of the application though, is there a reason it is needing to be open permanently? (Then again I would tend towards Linq to SQL or the Entity Framework and Linq rather than dealing with commands and connections anyway)

I also notice the Err.Raise in there - is there a reason for still using Err.Raise rather than exceptions? Just curious more than anything...
__________________
Intellectuals solve problems; geniuses prevent them.
-- Albert Einstein

Posting Guidelines Forum Rules Use the code tags
Reply With Quote
  #3  
Old 05-22-2014, 04:29 PM
AlanS AlanS is offline
Newcomer
 
Join Date: May 2003
Location: Philadelphia, PA, US
Posts: 18
Default

Thanks, PlausiblyDamp - EnlistTransaction is what I ended up stumbling across, and it did indeed solve the problem.

I'm keeping the connection open just to make the code simpler, rather than having to open and close a connection in every method that hits the database.

I used Err.Raise because it was the only way I knew of off the top of my head to simulate an error. It's not intended for use in production code (where normal Try...Catch structures will be used to actually trap whatever errors may arise), but only to simulate an error so as to test whether or not TransactionScope would actually do what it was supposed to do. As it turns out, it doesn't seem to do that with already open connections. It makes me wonder how many unsuspecting folks have simply taken Microsoft's recommendation to use this "simpler" way of implementing transactions, only to have it silently fail if the connection was already open.
Reply With Quote
Reply

Tags
transactionscope


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
TransactionScope not working
TransactionScope not working
TransactionScope not working TransactionScope not working
TransactionScope not working
TransactionScope not working
TransactionScope not working TransactionScope not working TransactionScope not working TransactionScope not working TransactionScope not working TransactionScope not working TransactionScope not working
TransactionScope not working
TransactionScope not working
 
TransactionScope not working
TransactionScope not working
 
-->