Re: Cross Transactions between ADO & ADO.Net

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.
__________________________________


"Sahil Malik [MVP]" <contactmethrumyblog@xxxxxxxxxx> wrote in message
news:OA1dwQOcFHA.2980@xxxxxxxxxxxxxxxxxxxxxxx
> 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
> ----------------------------------------------------------------------------
>
> "Brent" <brentwa@xxxxxxxxxxxxxxxx> wrote in message
> news:u1ChPVDcFHA.2696@xxxxxxxxxxxxxxxxxxxxxxx
>> 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
>>
>
>


.

Relevant Pages

  • Re: Permissions???
    ... > Server: SQL Server 2000. ... > the user could Write/Read data from server and execute stored procedures. ... > The connection was made with DSNs. ... ADO gives the following message when using stored ...
    (microsoft.public.data.ado)
  • Re: Cross Transactions between ADO & ADO.Net
    ... MARS session pool limited to one connection?). ... > There is one thing that might help here, if you're using SQL Server. ... We even call ADO code from ... >> the DTC cordinator from COM+ to handle the transactions between ADO.Net ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problem with anonymous connections
    ... but you can use another user account instead-of IUSR_machine ... I'm using the ADO recordset object to get data and this is in an ASP ... the user can open a shared folder on the SSAS server first (so the user is ... > A connection cannot be made. ...
    (microsoft.public.sqlserver.olap)
  • Re: Stored Procedure from Access
    ... stored procedure via an ADO connection to the server, ... You have to specify a suitable connect string for ... Connection object, which will have to be opened with a suitable connect ...
    (microsoft.public.access.formscoding)
  • Re: Error -2147168227 Cannot create new transaction because capacity was exceeded.
    ... Try to run SQL profiler, which ships with SQL Server to see how many ... > object and there is only 1 instance per application of the connection ... Prior to that I've just closed the ADO recordset object. ...
    (microsoft.public.data.ado)