ADO and ADO.net in the same transaction

[Guest]

Our old COM data access components use ADO.

As we are now begining to move to C#.net, we would like any new data access
components to use ADO.net.

Therefore we will end up having two seperate connections: One ADO connection
and one ADO.net connecation.

Is there any way of making it so that updates made through either of these
connections will be part of the same transaction?

We use Oracle but do not currently have any preference as to which ADO.net
data provider we use.

Thanks,

Jonathan

 

[W.G. Ryan eMVP]

Johnathan - depending on what you do - I think you'll need to use a
distributed transaction for it (95% sure on this).

The thing is that the modes they operate in are fundamentally different. If
you are going to roll Updates for instance in a transaction - that's one
thing - but with old ADO - you grab your data - keep the connection live,
work with the data and send it back all during the same session. In
ADO.NET - unless you are using a DataReader or one of the ExcecuteXXX
methods of the command object- then you pull the data over and the
connection is gone - at least that's how it should be working and if not
there's a problem.

As such - what you are going to wrap in a transaction has a huge bearing on
how you'd go about implementing it. Since the ADO code is going to have a
constant connection - that connection is out of play for use . However I
believe (and I'll need to verify this b/c I've never tried it before in this
use case) that you can beging the transaction on your ado piece and then
using Distributed transactions - you can enlist the ADO.NET stuff into it.

But remember that the whole model of disconnected ADO.NET is a lot different
than ADO - it's totally different really. So for instance, if you did a
select with the ado - then did the same with ADO.NET, modified the ado.net
stuff and sent it back to the db - and had it in a big transaciton -
there'd be a lot of complexity b/c you'd need to keep your local data synced
with the db - and if there was a rollback - you'd need to change the
rowstate of your local data.

This definitely would not be fun, clean or elegant via any means that I can
envision.

HTH,

Bill

 

[Paul Clement]

¤ Our old COM data access components use ADO.
¤
¤ As we are now begining to move to C#.net, we would like any new data access
¤ components to use ADO.net.
¤
¤ Therefore we will end up having two seperate connections: One ADO connection
¤ and one ADO.net connecation.
¤
¤ Is there any way of making it so that updates made through either of these
¤ connections will be part of the same transaction?
¤
¤ We use Oracle but do not currently have any preference as to which ADO.net
¤ data provider we use.
¤

No, not unless it is accomplished via the database server (on the server side) or middleware (such
as COM+). ADO and ADO.NET cannot share their transaction space.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Guest]

Thanks to both Bill and Paul,

It looks as though either using COM+ or sticking with ADO (At least an ADO
recordset can populate an ADO.net dataset) are the only options then.

Thanks,

Jonathan