SqlConnection Singleton

Question

Greetings, I would like to ask if creating Singleton is the only active db connection - a good idea. I would like: 1) I have a wcf service 2) the wcf service receives data from db 3) I would like to create a singleton like this in order to have only one connection to db:

private static PersistanceSingleton _Instance; public static PersistanceSingleton Instance { get { if (_Instance == null) { _Instance = new PersistanceSingleton(); } return _Instance; } } 

I know this is not a perfect singleton, but I just wrote it for this purpose. I would like to have some persistent repositories here, and I will create them in the constructor. Inside my service class, I will have the following code inside the constructor

 _DBPersistanceSingleton = PersistanceSingleton.Instance; 

Then, when some kind of request arrives (e.g. GetUsersRequest), I would like to do something like:

 _DBPersistanceSingleton.GetUsers() 

Before making every db call, I will also check if SqlConnection is open or not. Please let me know if this is a good practice. The reason I think of this solution is due to the large number of users who will connect to this service through the client application.

Answer 1

Bad practice reusing SqlConnection . Open it when you need it and close it as soon as you are done with it. The connection pool will work for you under the hood reusing the connection.

Answer 2

No, I would highly recommend that you do not. What happens if multiple requests appear at the same time? They cannot all use the same connection in the same, at best you just enter a big bottleneck.

The connection pool is automatically processed for you, and it gets rid of you, so you do not need to worry about it. Just open and close connections as needed.

Answer 3

Enabling sql connection ...

This singleton pattern is not thread safe and represents a bad idea for use in a multi-threaded application (since your WCF service is likely to be).

Using this code, when several simultaneous requests arrive, it is possible to create multiple instances.