Multiple Database Transactions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using fw 2.0....

Any ideas on how (if at all) to use a single transaction scope against 2 (or
more) database connections..?? My app interacts with SqlServer, and Oracle,
using OleDB model for DB i/o.

I have read (a little) on System.Transaction (TransactionScope), but not
seeing anything that specifically suggests I can set scope across multple
connection objects.

Scenario is as follows:

Database #1 (Sql Server) has a View of records. The app traverses the View,
and inserts records to other databases (possibly more than one). When the
app is finished with the inserts to the traget DBs, the app then deletes the
record from the unerlying table(s) which defined the View, such that the item
from the View is not processed a second time into the target DB's.

Thanks in advance....
 
hmmm,

Design issues aside, TransactionScope *should* work with an Oracle database
as long you have an instance of Microsoft DTC to enlist oracle transactions
and use the Oracle .Net provider (not sure abour OleDb). For more info see
http://msdn2.microsoft.com/en-us/library/ms172152.aspx.

Now if the structure around the View was properly designed to use a statusid
to 'lock' a row while you were processing it and you made sure that no other
row was picked up until the locked row was processed, then you would not need
to hold a transaction against the view at all and save on considerable system
resources. This will work if your aim is to just lock the View while you were
processing a row at time as you stated.

HTH
--
Good luck!

Shailen Sukul
Architect
(BSc MCTS, MCSD.Net MCSD MCAD)
Ashlen Consulting Service P/L
(http://www.ashlen.net.au)
 
Back
Top