getting db connection through singleton class

Question

I have created an singleton class, this class returns a database connection. So my question is this connection is also satisfying singleton criteria?
If no, than how I can make it singleton.
Here is the code.

public sealed class SingletonDB
{
    static readonly SingletonDB instance = new SingletonDB();
    static SqlConnection con =new SqlConnection(ConfigurationManager.ConnectionStrings["mydb"].ConnectionString);

    // Explicit static constructor to tell C# compiler
    // not to mark type as beforefieldinit
    static SingletonDB()
    {
    }

    SingletonDB()
    {
    }

    public static SingletonDB Instance
    {
        get
        {
            return instance;
        }
    }

    public static SqlConnection GetDBConnection()
    {
        return con;
    }
}

have you thought about injecting the SQLConnection dependency to your singleton class? — The Light, Jul 09 '11 at 21:33

score 41 · Accepted Answer · answered May 02 '09 at 10:27

Your Singleton is still off.

As far as the singleton pattern goes, please see Jon Skeet's very good and detailed description here : http://www.yoda.arachsys.com/csharp/singleton.html

Using a Singleton for a SqlConnection object is a really, really bad idea. There is no reason to do this whatsoever.

If you are attempting to avoid a performance hit of "new SqlConnection()" or "connection.Open()" be advised that there really is no performance hit there because of the connection pooling going on behind the scenes. Connection Pooling handles the opening/closing of the expensive connections. Not the SqlConnection object.

You won't be able to open multiple SqlDataReaders/Commands with the connection at the same time and will run into thread blocking issues if you are trying to share the same connection object with multiple threads.

The Singleton pattern is the most over used and abused pattern and there are many side effects of the singleton that you may not be aware of. Very good talk about the dangers of singletons here http://www.youtube.com/watch?v=-FRm3VPhseI

All my life I've been trick with don't make a connection on every call because "it's kill the application performance". — BlaShadow, May 09 '14 at 05:08
A link to [updated Jon Skeet's article](http://csharpindepth.com/Articles/General/Singleton.aspx) on singleton pattern can come in handy. — Corio, Oct 26 '17 at 14:31
what about command =MySqlCommand()? Is it good, when be used through Singleton? — x19, Feb 25 '19 at 09:55
@Corio I've proposed an edit to the answer to change the link to the one you referenced. — Luke Chadwick, Aug 27 '19 at 07:45

score 5 · Answer 2 · answered Nov 30 '12 at 11:49

In .NET C# you can wrtie your singleton like this

    public class Singleton{
public static readonly Singleton Instance= new Singleton();
private Singleton(){}

or for multi threaded environment:

using System;

public sealed class Singleton
{
   private static volatile Singleton instance;
   private static object syncRoot = new Object();

   private Singleton() {}

   public static Singleton Instance
   {
      get 
      {
         if (instance == null) 
         {
            lock (syncRoot) 
            {
               if (instance == null) 
                  instance = new Singleton();
            }
         }

         return instance;
      }
   }
}

Please see this MSDN Page for more info: https://msdn.microsoft.com/en-us/library/ff650316.aspx — Trevor.Screws, Sep 18 '17 at 12:41

score 4 · Answer 3 · edited Oct 15 '20 at 11:23

The connection itself is not satisfying the Singleton criteria because you can create multiple instances of a database connection object. A singleton by definition can only be instantiated once.

You can make the SqlConnection a part of the Singleton, by changing your example to this:

public sealed class SingletonDB
{
    private static readonly SingletonDB instance = new SingletonDB();
    private readonly SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["mydb"].ConnectionString);

    // Explicit static constructor to tell C# compiler
    // not to mark type as beforefieldinit
    static SingletonDB()
    {
    }

    private SingletonDB()
    {
    }

    public static SingletonDB Instance
    {
        get
        {
            return instance;
        }
    }

    public SqlConnection GetDBConnection()
    {
        return con;
    }
}

This way the SqlConnection used by your SingletonDB class would have one and only one SqlConnection, thus follow the Singleton pattern.

score 2 · Answer 4 · edited Nov 29 '11 at 09:21

Singleton means that the class that you have made can be instantiated only once. So if you want that to happen, do two things:

Make the constructor private.(This is to prevent other classes from accessing it.)

instantiate the class as:

get
{
 if(instance == null) //important coz, the class will be instantiated only on the first call
 {
   instance = new singletonDb;
 }
 return instance;
}

score 1 · Answer 5 · answered May 02 '09 at 05:35

If there's no other way to get a connection to the DB, and if this attribute cannot be overwritten, I would say yes. If that's what you're doing, you're probably taking this singleton thing too far. What if the the DB goes down temporarily and your app loses its connection? Then you'll have to restart your app in order for it to be able to use the DB again.

score 1 · Answer 6 · answered May 02 '09 at 05:35

I can't answer that question without seeing some code, I think. If you are saying that you will have only one DB connection instance in your application, that might work if you can guarantee that your application will run on only one thread (or at least that all operations using the DB connection does), since you can't (as far as I know anyway) run several operations in parallell on the same connection.

Also, if it means that your application will keep the connection open in between uses, I would advise against it. DB connections are limited resources on the DB server, so you should keep them open only when they are needed, and then close them.

score 0 · Answer 7 · answered May 02 '22 at 22:07

You don't have to discard the idea of a singleton just because it will need an IDbConnection. You can solve the problem similarly to how efcore does with its IDbContextFactory.

Rather than using IDbConnection directly, you use a wrapper class around a function that will create an IDbConnection on the fly instead.

public class DbConnectionFactory
{
    private readonly Func<IDbConnection> _connectionCreator;

    public DbConnectionFactory(Func<IDbConnection> connectionCreator)
    {
        _connectionCreator = connectionCreator;
    }

    public IDbConnection CreateConnection()
    {
        return _connectionCreator();
    }
}

You can create an extension method to add it to services, if you'd like.

    public static IServiceCollection AddDbConnectionFactory(
        this IServiceCollection collection,
        Func<IDbConnection> connectionCreator
    )
    {
        return collection.AddSingleton(new DbConnectionFactory(connectionCreator));
    }

Then in your Startup.cs, add it to your services.

//services.AddTransient<IDbConnection>(db => new Microsoft.Data.SqlClient.SqlConnection(connectionString));
services.AddDbConnectionFactory(() => new Microsoft.Data.SqlClient.SqlConnection(connectionString));

Let's say you want a singleton that's supposed to ReadData on command.

services.AddSingleton<MySingleton>();

If your class looks something like this...

public class MySingleton(){

  private readonly IDbConnection _connection;

  public MySingleton(IDbConnection connection){
    _connection = connection;
  }

  public void ReadData(){
    var command = _connection.CreateCommand();
    //...
  }

}

change it to this...

public class MySingleton(){

  private readonly DbConnectionFactory _connectionFactory;

  public MySingleton(DbConnectionFactory connectionFactory){
    _connectionFactory = connectionFactory;
  }

  public void ReadData(){
    using var connection = _connectionFactory.CreateConnection();
    var command = connection.CreateCommand();
    //...
  }
}

Now you are opening and disposing a connection every time you call ReadData(), rather than using a connection that tries and fails to stay open.

getting db connection through singleton class

7 Answers7

Linked