Database connection (using and close)

[Ranier Dunno]

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.

[Miha Markic]

Hi Ranier,

"Ranier Dunno" <an*******@disc ussions.microso ft.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

[Ranier Dunno]

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 MISunderstandin gs :)

-----Original Message-----
Hi Ranier,

"Ranier Dunno" <an*******@disc ussions.microso ft.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
.

[Ranier Dunno]

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.

[Miha Markic]

Hi Ranier,

I normally use a public static readonly property.

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

"Ranier Dunno" <an*******@disc ussions.microso ft.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.

[Ranier Dunno]

-----Original Message-----
Hi Ranier,

I normally use a public static readonly property.

Ok, excellent, thanks again.