Proper Way to work with SQL Connections

[Brandon Potter]

Hi,

Basic SQL question.

I have your typical dynamic-content, about-10-SQL-requests-per-page web
site. However, I have run into some problems in the way that I'm doing
things in that sometimes the connection just fails, closes for no apparent
reason, or there's an open SqlDataReader somewhere, even though the code
does close every SqlDataReader when reading is finished.

I have created a SqlConnection object and opened the connection when the
user's session is created. For all the SQL requests that the pages make,
they just execute SqlCommands and SqlReaders using the one instance of
SqlConnection. When the session is done, I close the connection.

Should I be using the one instance of SqlConnection (my reasoning being that
it would be more efficient to hold the connection open rather than have the
extra load of opening and closing the connection), or should I be opening
and closing the connection for each SQL request? Or somewhere in between?

Thanks,
Brandon

 

[Kevin Spencer]

You should open and close your Connections as quickly as possible.
Connection Pooling will handle the performance for you. Most likely this is
the cause of your problems. An additional note: Leverage the various caching
mechanisms afforded to you to minimize the number of hits to the database.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.