Singleton Pattern for Database Access --- Leave Open or Close Connection

[mescano]

I am currently implementing a singleton pattern for accessing a
database. Is it advisable to close the connection to the database at
all -- thus leaving it open or should it be closed. If closed, when
should I it close it -- after the execution of the each command to the
database? If leaving open, what impacts does it have. Imagining that
it is one connection to the database.

Thanks,
mescano

 

[Beth Massi [Architect MVP]]

That depends. What is your application's architecture? How many users does
it need to support? What database are you using?Leaving the connection open
is not scalable but if you only have a couple connections and you need the
performance then it may work for you. This is how many client/server
applications have been architected in the past. If you need to move to a
more scalable, distributed design then you should open the connection only
when you need it and then close it when you are done. Furthermore, if you
use the same connection string each time, you can take advantage of
connection pooling which provides even more scalability. Different providers
handle connection pooling differently but in general it is transparent to
the programmer. For more info, take a look at :
http://msdn.microsoft.com/library/en-us/dnbda/html/daag.asp?frame=true

HTH,
-B

 

[Joseph MCAD]

April 12, 2005

I would Definitely Close It! For each connection you have open requires
a SQL Server Client Access License. These "CAL"s cost big $$s! I cannot
remember how much they are, but I do know that most of the time they costs
thousands of dollars! Hope this helps!

Joseph MCAD

 

[Cor Ligthert]

Mescano,

In addition to the others.

It depends from your database. For a database server like SQLserver it is
adviced to close (dispose in this special case) as soon as possible.
For a database like Access it can be better to leave it open because
otherwise the user can delete/replace the file maybe while it is running and
than gives than an unpredicted error.

I hope this helps,

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Mescano,
In addition to the other comments.

Which database client are you using? (System.Data.Odbc, System.Data.OleDb,
System.Data.SqlClient, System.Data.OracleClient, System.Data.SqlServerCe, or
other (MySQL))

As each may or may not support connection pooling, I know the first 4 (Odbc,
OleDb, SqlClient, & OracleClient do). Although you Open & Close the
connection object (SqlConnection for example) the physical connection to the
database is kept open for a short period of time in anticipation of being
reused.

http://msdn.microsoft.com/library/d...nectionPoolingForSQLServerNETDataProvider.asp
http://msdn.microsoft.com/library/d...nConnectionPoolingForOLEDBNETDataProvider.asp
http://msdn.microsoft.com/library/d...onconnectionpoolingforodbcnetdataprovider.asp
http://msdn.microsoft.com/library/d...nectionpoolingfornetdataproviderfororacle.asp


Are you writing a server side app or a client side app?

Leaving the connection object open on a server side app will hurt
scalability of your server app itself, as each client request will be
conflicting with the single connection object. For client side apps it will
hurt scalability of the database server itself, as all those physical
connections are held open.

In both cases I would consider opening & closing the connection for each
"unit of work" that I am doing. The trick is ensuring you close the
connection when the "unit of work" is finished. By "unit of work" I mean a
group of related transactions. For example retrieving an Order, from both
Order Header & Order Detail, would be a unit of work. Saving an Order, to
both Order Header & Order Detail, would be another unit of work. Otherwise I
would simply close the connection when I am done with it.

Hope this helps
Jay

|I am currently implementing a singleton pattern for accessing a
| database. Is it advisable to close the connection to the database at
| all -- thus leaving it open or should it be closed. If closed, when
| should I it close it -- after the execution of the each command to the
| database? If leaving open, what impacts does it have. Imagining that
| it is one connection to the database.
|
| Thanks,
| mescano
|