Best practice for using connection throughout page?

  • Thread starter Thread starter jeremystein
  • Start date Start date
J

jeremystein

When I drop a SqlConnection on the web form designer, it's created in
InitializeComponent. I'd like to use this connection in my Page_Load
method, but I don't see anywhere that it's opened. Do I need to open
it myself? If so, should I close it also, or will I be interfering
with databound controls that use it? If I want to use it in PostBack
methods should I re-open it, or leave it open in Page_Load?

It seems that I would like to have a connection open for the duration
of the page, but I don't know any way to do that while guaranteeing
that it gets closed in case of an error. What is the best practice for
using a connection in Page_Load, PostBacks, and databound controls?
 
When I drop a SqlConnection on the web form designer, it's created in
InitializeComponent. I'd like to use this connection in my Page_Load
method, but I don't see anywhere that it's opened. Do I need to open
it myself? If so, should I close it also, or will I be interfering
with databound controls that use it? If I want to use it in PostBack
methods should I re-open it, or leave it open in Page_Load?

It seems that I would like to have a connection open for the duration
of the page, but I don't know any way to do that while guaranteeing
that it gets closed in case of an error. What is the best practice for
using a connection in Page_Load, PostBacks, and databound controls?
Click to expand...

In most opinions, it is not wise to use a "global" connection object.
Create the object, open it, use it, close it. If done correctly, the
connection will be pooled and can quickly be created/opened again (by
retrieving the object from the pool automatically) and re-used. In this
case, you would just need to make sure the object is closed after an error
by:

' Create connection.
Dim conn As SqlConnection = New SqlConnection(...)
conn.Open()
Try
... fill dataset, update database, or something else ...
Finally
conn.Close()
End Try

' Use data retrieved from the database. Connection is no longer needed
here.



HTH,
Mythran
 
There's not much advantage in trying to keep the connection open for the
life of the page, and NEVER try to keep it open across page requests.
ASP.NET's efficient built-in connection pooling makes this kind of thing
unnecessary. The rule of thumb is to open a connection just before you need
it, and close it as soon as you've retrieved the data you need.
 
Back
Top