SQLConnection asp.net Best Practices?

[mid]

Hello!,

What is the Best Practice about dealing with a sqlconnection while page
executes?
Let me explain:
Supose you have several dropdownlists, datagrids, etc, in the same page,
that need to retreive data from sql server. Right now I open/close a sql
connection for each object that I want to populate, but I think this is not
the good way...

If I open the connection at page.load and close at page.unload and
use/reuse that connection in all the subs that get executed while page...
executes , is this a good idea??

How do you gurus deal with this question?

Thanks
mid

 

[Guest]

Connection pooling takes care of connection reuse and the like. Open a
connection at the last possible second and close it as soon as possible
afterwards. Don't open at the beginning and close at the end.

 

[Kevin Spencer]

Ideally, you shouldn't be using any database logic in your Page class.
Instead, build a business class that does your database operations, and have
that class do the work. The Page should only contain interface logic that
uses the business class(es) to manipulate data, and displays the data in the
browser interface. The business class should open and close Connections as
quickly as possible.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Neither a follower
nor a lender be.

 

[mid]

Thanks Scott,

I am aware of connection pooling but thought it was helpfull when several
users are executing the page (in different threads). Do you mean that when
I open a connection to populate a dropdown then close it then open it again
to populate another object there is no overhead? Behind the scenes the
connection is still open?

Where to declare the connection (not open)? At page Load?

Thanks
mid

 

[Scott Allen]

I'd keep the connection as a local variable inside of a method. If
down the road you decide to use some common code as a data access
layer this approach keeps things simple to convert.