Best practice for using connection throughout page?

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?
 
M

Mythran

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?

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
 
S

Steve C. Orr [MVP, MCSD]

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top