Singleton pattern in database connection in .net

Question

Have a doubt in my project the database connection object is created via singleton pattern as opening and closing connection is heavy operation but i have doubt shouldn't we dispose of the object what is the use of keeping it alive as closing connection frees memory

Answer 1

Exactly, there is no point in holding onto a connection to your db server. All DB clients[ADO.NET, ODBC, Jdbc,etc] support connection pool mechanism. You are better off to rely on that and use that properly, than to keep a singleton connection object to your database.

Answer 2

It is not good idea as if singleton is not correctly implemented, then one thread can dispose the connection, however the another thread can try to execute some command.

There is connection pooling when you call new SqlConnection() or connection.Open()

So it is better to create new connection to take advantage of connection pooling.