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/de...taProvider.asp http://msdn.microsoft.com/library/de...taProvider.asp http://msdn.microsoft.com/library/de...taprovider.asp http://msdn.microsoft.com/library/de...rfororacle.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
"mescano" <ma****@binarymediasystems.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
|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
|