I have a question on when and where we should create connections to SQL.

I have a base class that handles all data access and dishes up connections
etc as needed. I initially had a class level connection object that all
general dataacess methods used to execute against. This introduced a problem
when using transactions and executing more than one on a connetion at a
time. I over cone this by creating a new connection each time I exeute a
method. When I do a trace on SQL I get many 'Audit login' events occuring as
the connections are being created. When I log out of my app and the
connections are being disposed I get many, many, many audit logouts. Is it
normal to create a new connection every time it is needed or is it better to
try and reuse an existing connection object if possible?. Maybe I can use IF
@@TRANCOUNT = 0 then use connection else create new one?


Thanks
Devron

It kind of depends on your application I believe... I mean, if you are opening several datasets then by all means, open one connection, open all of the datasets you need to at that
point and do any other business like updates you need to do on that connection then close it and only reopen a connection as needed to do more access. However, if you are
questioning whether you should just open a connection then let it lay around waiting until you use it, I would not recommend that because there may be other processes or
applications that need to have that resource and you are tying it up waiting in case you need it. The Audit Login/Logouts are just a part of the housecleaning when you are
connecting to the database.

Keep in mind, these are just my opinions on this, I prefer to only have a connection when needed (This is also considered one of the best practices). Some may prefer the other
method to save the 'time' (whats a few milliseconds between friends) it takes to create the connection.

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (E-Mail Removed) <Remove word online. from address>

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




--------------------
>From: "Devron Blatchford" <(E-Mail Removed)>
>Subject: Connections (Audit Login/Logout)
>Date: Tue, 29 Jul 2003 13:33:14 +0930
>Lines: 19
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <(E-Mail Removed)>
>Newsgroups: microsoft.public.dotnet.framework.adonet,microsoft.public.sqlserver.clients,microsoft.public.sqlserver.connect,microsoft.public.sqlserver.programming
>NNTP-Posting-Host: 203.108.249.179
>Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.clients:25039 microsoft.public.sqlserver.connect:36849 microsoft.public.sqlserver.programming:377236
microsoft.public.dotnet.framework.adonet:56925
>X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
>
>I have a question on when and where we should create connections to SQL.
>
>I have a base class that handles all data access and dishes up connections
>etc as needed. I initially had a class level connection object that all
>general dataacess methods used to execute against. This introduced a problem
>when using transactions and executing more than one on a connetion at a
>time. I over cone this by creating a new connection each time I exeute a
>method. When I do a trace on SQL I get many 'Audit login' events occuring as
>the connections are being created. When I log out of my app and the
>connections are being disposed I get many, many, many audit logouts. Is it
>normal to create a new connection every time it is needed or is it better to
>try and reuse an existing connection object if possible?. Maybe I can use IF
>@@TRANCOUNT = 0 then use connection else create new one?
>
>
>Thanks
>Devron
>
>
>