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,

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.

 

[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 MISunderstandings

 

[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.

 

[Ranier Dunno]

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

I normally use a public static readonly property.

Ok, excellent, thanks again.