How do we do database transactions WITHOUT using MTS

[cjard]

I have a problem whereby I have used a modern version of a database driver (the oracle 10g client) to develop a system that will go into production with an older version of the driver.

It appears that the old version, in this deployment, lacks MTS transaction support, so any transactions the code will fail because the DLL that implements them simply isnt on the machine

In a TableAdapter based hierarchy, where the TAs themselves are responsible for leasing and re-pooling connections, I dont know how to start a transaction on just one conenction and carry it to the others...

Other developments writen long ago in VS2003 used explicitly declared connections and dataadapters. Because the connection was accessible to the programmer, the transaction could be controlled through connection.BeginTransaction et al and this seems not to require MTS

Is there anything I can do short of rewriting the app? Can I tell a TableAdapter which connection to use?

 

[vis781]

I believe it can be done with a partial class. Have a look at this....

http://blogs.msdn.com/vbteam/archive/2004/07/19/187953.aspx

 

[cjard]

I cant find anything usable in that post; my complaints would be the same as the others in terms of the number of tables that would need changing and Im having trouble locating the sample code the author says is there.

 

[vis781]

I think this is the part they were referring to .....

If you want to make the ConnectionString public you can simply add the following to your own partial class:

[FONT=Courier New][COLOR=black][COLOR=blue][FONT=Courier New]Partial[/FONT][/COLOR][FONT=Courier New] [COLOR=blue]Public[/COLOR] [COLOR=blue]Class[/COLOR] CustomersTableAdapter[/FONT][/COLOR]
[FONT=Courier New][COLOR=black]   [COLOR=blue]Public[/COLOR] [COLOR=blue]Property[/COLOR] ConnectionString() [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR][/COLOR][/FONT]
[FONT=Courier New][COLOR=black]       [COLOR=blue]Get[/COLOR][/COLOR][/FONT]
[FONT=Courier New][COLOR=black]           [COLOR=blue]Return[/COLOR] [COLOR=blue]Me[/COLOR].Connection.ConnectionString[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]       [COLOR=blue]End[/COLOR] [COLOR=blue]Get[/COLOR][/COLOR][/FONT]
[FONT=Courier New][COLOR=black]       [COLOR=blue]Set[/COLOR]([COLOR=blue]ByVal[/COLOR] value [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR])[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]           [COLOR=blue]Me[/COLOR].Connection.ConnectionString = value[/COLOR][/FONT]
[FONT=Courier New][COLOR=black]       [COLOR=blue]End[/COLOR] [COLOR=blue]Set[/COLOR][/COLOR][/FONT]
[FONT=Courier New][COLOR=black]   [COLOR=blue]End[/COLOR] [COLOR=blue]Property[/COLOR][/COLOR][/FONT]
[COLOR=black][COLOR=blue][FONT=Courier New]End[/FONT][/COLOR][FONT=Courier New] [COLOR=blue]Class[/COLOR][/FONT][/COLOR]
[/FONT]

http://blogs.msdn.com/vbteam/archive/2004/08/05/209140.aspx

So it would seem that it can be done, however on the whole the common consensus is that tableadapter connection strings are fixed. I haven't tried it so i don't know how well it would work or how flexible it is.

 

[cjard]

I'm not sure how fixing the connection string actually helps; all my table adapters get their conenction string out of the application settings, which I can scope as a User setting if I want to change it (even though i dont right now which is why i left it)

As a guess, i'd have to:

Check to see if a tableadapter reopens an open connection
Write another proerty that isnt readonly, that lets me modify the connection (which may hit the "partial classes may not modify other partial classes' privates" restriction)
Open a conenction
Start a transaction
Set the tableadapter to use the connection
Have the TA do work
Set the connection of another TA to this conenction
Have this other TA do work too
Commit the transaction


I get the feeling it's not going to be as simple as that, especially if I cant prevent the TA from closing the connection after it has done its work

 

[vis781]

Maybe i mis-understood your question. You want to wrap a couple of TableAdapters into a transaction and commit them to the database? If yes, then you can do this using reflection. It's in c# but as i understand it you know a bit of c# so it shouldn't be problem for you.

http://weblogs.asp.net/ryanw/archive/2006/03/30/441529.aspx

 

[cjard]

Righteous find; I'll be looking at that shortly!