Cross Transactions between ADO & ADO.Net

[Brent]

Hi, we are changing our application from VB6 to VB.Net. This is far from a
trivial upgrade. We have over 400 forms and 40 dll's. We can't just take
down our production application and start the whole project from scratch. We
have to convert this stuff in stages. Our application design is pretty much
2-tier. We connection to SQL server when the application starts and then
disconnect when the application unloads. We even call ADO code from our dll
so transactions are scattered all over the code between dll's and the main
application. I guess the problem comes in when we are upgrade some code will
be ADO & some code will be ADO.Net. I came up with an idea which i think
would work which would be to hose both our ADO object ( a wrapper we wrote
for ADO) and ADO.Net wrapper in COM+. We could then use the DTC cordinator
from COM+ to handle the transactions between ADO.Net & plain ADO for us.

Does anyone see a better solution. The solution i came up with would require
two connections to the DB and if possible we would like a solution only
requiring one conneciton. We are also concerned about throughput since COM+
transactions are at a layer above ADO transactions and we don't want any
bottlenecks in our new code if possible. Does anyone have any better
solutions?

thanks,
Brent

 

[Sahil Malik [MVP]]

Brent,

DTC transactions are not only heavy, they might also cause deadlocks due to
the high isolation level they work under. SQL2k5 is a bit better, but I
assume you are using Sql2k.

Converting from VB6 to VB.NET is trivial only if your existing application
architecture is in line with the recommendations - but if you have
transactions scatterred all over, and logic not seperated into class
libraries - you don't have much choice left. Though, even then I would
recommend upgrading to .NET.

Here is an approach I took with a client. I instead of rewriting and
replacing their existing system, created a new modular system written in
..NET which was eventually to replace the old system. The new system had bits
and peices of functionality that slowly weaned the users away from the old
code. Not sure if that applies in your situation - but just a thought .
BTW, the easiest thing to pull out, are reports.

And also I wouldn't recommend keeping an open connection to the sql server
for the life of the application. You should instead rely on connection
pooling.

Also, it is very hard to give specific hard hitting advice without being in
your shoes , but above are a few general pointers.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync

 

[William \(Bill\) Vaughn]

I'm with Sahil on virtually all of this--except in a client/server
application there's little benefit to constantly closing and reopening the
connection--it thrashes the server unnecessarily and discards useful server
state. Some would say this would limit scalability. If this is an issue, the
application should go "dormant" and close the connection if the operator
goes away and quietly reopen the connection when they return. I've seen this
approach scale to thousands of users--this should be enough for most rigs.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Pablo Castro [MS]]

There is one thing that might help here, if you're using SQL Server. This is
a feature that we don't advertise a lot and we've been removing the focus
from it over time; so read on if you need this as an intermediate step while
you migrate your app, *not* as a long-term solution:

SQL Server supports something called "bound sessions", where two connections
(to the same server) can be bound to the same transaction, without involving
a distributed transaction coordinator. Bound sessions require careful
coordination, as while you use one (e.g. have a pending data-reader) you
cannot use the other. If those limitations are ok with you, then you can
find out how to use bound sessions in SQL Server Books Online (it's fairly
easy), start by looking at the sections for "sp_getbindtoken" and
"sp_bindsession"

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Sahil Malik [MVP]]

Wow thats hitech. But I can see why that is being discouraged.

One thing you mentioned is, if you have a pending datareader, the other
cannot be used. Can this restriction be gotten around using MARS? (But isn't
MARS session pool limited to one connection?).

Sounds like a hairy solution but a solution nonetheless.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync

 

[Pablo Castro [MS]]

The issue of one result-set (reader) at a time has to do with transaction
ownership, not with being able to multiplex the session, so no, MARS won't
help in this particular case. That's why this is to be used only in
exception cases, it has many limitations.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.