Database Connection clarification

  • Thread starter Thread starter Ken Cox [Microsoft MVP]
  • Start date Start date
K

Ken Cox [Microsoft MVP]

There's an interesting clarification here that seems to relate to your
question:

http://support.microsoft.com/default.aspx?scid=kb;en-us;315461

"You should always close the Connection when you are finished using it. This
can be done using either the Close or Dispose methods of the Connection
object. Connections that are not explicitly closed may not be added or
returned to the pool. For example, a connection that has gone out of scope,
but has not been explicitly closed, is returned to the connection pool only
if the maximum pool size has been reached and if the connection is still
valid. "
 
Your syntax looks fine.
ADO.NET has built in connection pooling.
Open your db connection just before you need it, and close it as soon as you
are done with it.
As long as you use the same connection string everywhere the sharing will be
automatic - you will not actually open a new database connection each time
in most cases despite what the syntax suggests. The connections will be
recycled for you.
Purposely keeping connections open or trying to pass them to other pages
will result in inefficient, poorly performing code.
 
I have just started programming .NET having come from ASP.

We used to create a connection and store it in a session.This meant for each
user logging into the site there was only one connection

Now with ASP.NET using the SqlClient libraries this is not possible (No
doubt for good reasons).

So for each page before I need to access the DB I call
objConnect = new SqlConnection(connectionString);

Is this the right way? Is there more overhead or time needed due to the new
connection being made each time?

If someone could clarify it would be greatly appreciated.
Thanks
Jon
 
Back
Top