Managing database connections

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have a question about opening and closing OleDB connections
explicitly. I have a form that, when loaded, makes several calls to
the database (uses several Data Adapters). Should I open the
connection to the database in the form's Load event handler and just
leave it open until the form is closed? Or should I open and close a
connection with each call to the database?

I am concered about two potential issues here:

1) performance
2) stability of the application (can the application become unstable
if i am not managing db connections properly?)

Many thanks in advance for any help.

Brian
 
On 7 Oct 2003 10:35:08 -0700, (e-mail address removed) (Brian) wrote:

¤ I have a question about opening and closing OleDB connections
¤ explicitly. I have a form that, when loaded, makes several calls to
¤ the database (uses several Data Adapters). Should I open the
¤ connection to the database in the form's Load event handler and just
¤ leave it open until the form is closed? Or should I open and close a
¤ connection with each call to the database?
¤
¤ I am concered about two potential issues here:
¤
¤ 1) performance
¤ 2) stability of the application (can the application become unstable
¤ if i am not managing db connections properly?)

Depends upon the type of database and whether it's a multi-user environment. For desktop or ISAM
type databases you can open and close connections at will with little or no overhead.

Server type databases typically incur more overhead when requesting a connection. This is the reason
why connection pooling was implemented in ODBC and OLEDB. Connection pooling enables an application
to frequently (request) and close (return) connections to a "pool" where they can be re-used in
subsequent connection requests (using the same connection string) to the database..


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

Depends, if you have a local Access DB or something like that, It would work
with a global connection quite nicly, but if you are accessing a SQL server
over a network, open and close the connection in every function, else you
will get gray hair quickly.

Note: Always remeber to close a database connection (i do it finally
statement in my try catch blocks), it will not be closed when code is
leaving your function.

Regards
Fredrik Melin
 
In order to reduce the load on the database server and increase the
scalability of your application, you should try to keep the connection open
only when you actually need to comunicate with the server. If you're using
the ADO.NET OleDb classes, you get connection pooling by default. What that
means is that next time you try to open a connection, it'll look in the
pool of connections to see if there is already one that can be used (w/
same server and credentials). This greatly reduces the cost of opening a
connection.

There is some information at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconconnectionpoolingforoledbnetdataprovider.asp

For your application, you could then open a connection in the form load
event, get the data you need initially and then call Close on the
connection. Later, when the user does something on the form that requires
you to communicate with the server again, you just need to reopen the
connection and ADO.NET/OleDb will do the work of retrieving the connection
from the pool.

HTH

Antoine
Microsoft Visual Basic .NET

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

--------------------
 
Thanks Antoine.

So are you saying that when I make a call to:

myConnection.Open()

this really just looks to the connection pool?
 
Yes, unless you turn off the OLE DB services (see the link below).

Antoine
Microsoft Visual Basic .NET

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

--------------------
From: (e-mail address removed) (Brian)
Newsgroups: microsoft.public.dotnet.languages.vb
Subject: Re: Managing database connections
Date: 8 Oct 2003 14:17:32 -0700
Organization: http://groups.google.com
Lines: 77
Message-ID: <[email protected]>
References: <[email protected]>
Click to expand...
NNTP-Posting-Host: 64.170.104.17
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1065647853 3940 127.0.0.1 (8 Oct 2003 21:17:33 GMT)
X-Complaints-To: (e-mail address removed)
NNTP-Posting-Date: Wed, 8 Oct 2003 21:17:33 +0000 (UTC)
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!newsfeed.freenet.de!194.168.222.22.MISMATCH!newspeer1-gui.server.ntli.n
et!ntli.net!sn-xit-02!sn-xit-04!sn-xit-01!sn-xit-09!supernews.com!postnews1.
google.com!not-for-mail
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.vb:145126
X-Tomcat-NG: microsoft.public.dotnet.languages.vb

Thanks Antoine.

So are you saying that when I make a call to:

myConnection.Open()

this really just looks to the connection pool?


(e-mail address removed) (Antoine Cote [MSFT]) wrote in message
In order to reduce the load on the database server and increase the
scalability of your application, you should try to keep the connection open
only when you actually need to comunicate with the server. If you're using
the ADO.NET OleDb classes, you get connection pooling by default. What that
means is that next time you try to open a connection, it'll look in the
pool of connections to see if there is already one that can be used (w/
same server and credentials). This greatly reduces the cost of opening a
connection.

There is some information at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconconnectionpoolingforoledbnetdataprovider.asp

For your application, you could then open a connection in the form load
event, get the data you need initially and then call Close on the
connection. Later, when the user does something on the form that requires
you to communicate with the server again, you just need to reopen the
connection and ADO.NET/OleDb will do the work of retrieving the connection
from the pool.

HTH

Antoine
Microsoft Visual Basic .NET

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

--------------------
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!newsfee
d01.sul.t-online.de!t-online.de!newspeer1-gui.server.ntli.net!ntli.net!sn-xi
t-02!sn-xit-04!sn-xit-06!sn-xit-09!supernews.com!postnews1.google.com!not-fo
r-mail
Click to expand...
Click to expand...
 
Back
Top