Optimising Connections & DataAdapters

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jon Maz (jonmaz_at_surfeu.de.no.spam)
Date: 05/27/04 

Date: Thu, 27 May 2004 12:20:11 +0100

Hi,

I just read the following in an old NG thread:

"when you use the adapter with a closed connection it will open it, do your
requested database access, and close it immediately. If you use the adapter
with an open connection the connection will continue to be open after the
adapter is done."

My DataLayer makes extensive use of DataAdapters to fill DataSets, and
currently each DataLayer function creates its own, new, SqlConnection, and I
do not explicitly open/close the SqlConnection, as the DataAdapter takes
care of that. As a result of reading the above quote, I think I can improve
performance by better use of SqlConnections.

Specifically, I'm now thinking about manually opening an SqlConnection at
the beginning of a DataLayer function before filling the DataSet, so that I
have the option of passing the still-open connection to another function
where it could be used to fill another DataSet, before passing this
still-open connection back to the original function to be manually closed.

Would this be (much?) more efficient than each separate function creating a
new SqlConnection?
Would such an approach create any new problems?

Thanks,

JON

PS Also: can anyone compare the performance of re-opening an
existing-but-closed SqlConnection with the cost of creating and opening a
brand new one?

Relevant Pages

  • Optimising Connections & DataAdapters
    ... "when you use the adapter with a closed connection it will open it, ... If you use the adapter ... currently each DataLayer function creates its own, new, SqlConnection, and I ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Optimising Connections & DataAdapters
    ... That quote is referring to the actual mechanics of Connection pooling behind ... different then letting the Adapter do it for you, at least in this regard so ... > do not explicitly open/close the SqlConnection, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Optimising Connections & DataAdapters
    ... That quote is referring to the actual mechanics of Connection pooling behind ... different then letting the Adapter do it for you, at least in this regard so ... > do not explicitly open/close the SqlConnection, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ipconfig and network connections empty - Device Manager okay
    ... adapter when the problem occurred. ... degree that in KB825826 the network connections _are_ working and just ... local area connection in the Network and Dial-up Connections folder. ... Trying to install a connection gets one to a screen where one can ...
    (microsoft.public.windowsxp.network_web)
  • Re: ipconfig and network connections empty - Device Manager okay
    ... adapter when the problem occurred. ... degree that in KB825826 the network connections _are_ working and just ... local area connection in the Network and Dial-up Connections folder. ... Trying to install a connection gets one to a screen where one can ...
    (microsoft.public.windowsxp.network_web)