Database Connection clarification

[Jon Booth]

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(c onnectionString );

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

[Ken Cox [Microsoft MVP]]

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

http://support.microsoft.com/default...b;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. "
"Jon Booth" <jo*******@nusy stems.com.au> wrote in message
news:bq******** **@perki.connec t.com.au...

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(c onnectionString );

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

[Steve C. Orr [MVP, MCSD]]

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 hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com


"Jon Booth" <jo*******@nusy stems.com.au> wrote in message
news:bq******** **@perki.connec t.com.au...

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(c onnectionString );

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

[Jon Booth]

Excellent thanks for your help. Thats exactly what I wanted to hear.

"Steve C. Orr [MVP, MCSD]" <St***@Orr.ne t> wrote in message
news:eL******** ******@TK2MSFTN GP10.phx.gbl...

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 hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com


"Jon Booth" <jo*******@nusy stems.com.au> wrote in message
news:bq******** **@perki.connec t.com.au...

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(c onnectionString );

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