By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,888 Members | 1,561 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,888 IT Pros & Developers. It's quick & easy.

Database connection (using and close)

P: n/a
Hi,

I'm writing a data layer, and would like all components
to have a single place for retrieving a connection to the
(SQL Server) database. Currently, I'm treating the
connection as somewhat of a "singleton", that is, I have
a static reference to a single SqlConnection object.
However, I'm starting to think this is the _wrong_ thing
to do. From what I can gather about connection pooling, I
can simply create a "new" connection each time
(remembering to open before using it, then closing it
afterwards). That way, I can better handle simultaneous
access to the database (the pool will hand out more than
one connection iff necessary). Also, I'm thinking I
should _not_ be using the _using_ keyword (which I am to
a certain extent now), since this will dispose of the
connection altogether, forcing an expensive recreating of
the database connection for each access. Am I right? Any
help on architecture/usage would be much appreciated.
Nov 15 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Hi Ranier,

"Ranier Dunno" <an*******@discussions.microsoft.com> wrote in message
news:01****************************@phx.gbl...
Hi,

I'm writing a data layer, and would like all components
to have a single place for retrieving a connection to the
(SQL Server) database. Currently, I'm treating the
connection as somewhat of a "singleton", that is, I have
a static reference to a single SqlConnection object.
However, I'm starting to think this is the _wrong_ thing
to do.
Indeed.

From what I can gather about connection pooling, I can simply create a "new" connection each time
(remembering to open before using it, then closing it
afterwards). That way, I can better handle simultaneous
access to the database (the pool will hand out more than
one connection iff necessary).
Absolutely correct.

Also, I'm thinking I should _not_ be using the _using_ keyword (which I am to
a certain extent now), since this will dispose of the
connection altogether, forcing an expensive recreating of
the database connection for each access. Am I right?
Absolutely not :)
You are right to use using keyword - as it disposes the sqlconnection object
while the physicall connection is returned to pool and it is not closed.

Any help on architecture/usage would be much appreciated.


You've pretty much figured it out.
Keep a connection string global and create connections from it (when
needed).
Close them asap.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
Nov 15 '05 #2

P: n/a
Hi Miha,

Thanks a lot for your reply - sometimes it is necessary
to verify that I'm understanding things correctly. And
it's even better to identify MISunderstandings :)
-----Original Message-----
Hi Ranier,

"Ranier Dunno" <an*******@discussions.microsoft.com> wrote in messagenews:01****************************@phx.gbl...
Hi,

I'm writing a data layer, and would like all components
to have a single place for retrieving a connection to the (SQL Server) database. Currently, I'm treating the
connection as somewhat of a "singleton", that is, I have a static reference to a single SqlConnection object.
However, I'm starting to think this is the _wrong_ thing to do.
Indeed.

From what I can gather about connection pooling, I
can simply create a "new" connection each time
(remembering to open before using it, then closing it
afterwards). That way, I can better handle simultaneous
access to the database (the pool will hand out more than one connection iff necessary).


Absolutely correct.

Also, I'm thinking I
should _not_ be using the _using_ keyword (which I am to a certain extent now), since this will dispose of the
connection altogether, forcing an expensive recreating of the database connection for each access. Am I right?


Absolutely not :)
You are right to use using keyword - as it disposes the

sqlconnection objectwhile the physicall connection is returned to pool and it is not closed.
Any
help on architecture/usage would be much appreciated.
You've pretty much figured it out.
Keep a connection string global and create connections

from it (whenneeded).
Close them asap.

--
Miha Markic - RightHand .NET consulting & software developmentmiha at rthand com
.

Nov 15 '05 #3

P: n/a
Hi again,

I have one further question: would it be preferable to
use a static class or a singleton object as the source
for connection objects?

Best regards.
Nov 15 '05 #4

P: n/a
Hi Ranier,

I normally use a public static readonly property.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

"Ranier Dunno" <an*******@discussions.microsoft.com> wrote in message
news:06****************************@phx.gbl...
Hi again,

I have one further question: would it be preferable to
use a static class or a singleton object as the source
for connection objects?

Best regards.

Nov 15 '05 #5

P: n/a
-----Original Message-----
Hi Ranier,

I normally use a public static readonly property.


Ok, excellent, thanks again.
Nov 15 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.