Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

getting db connection through singleton class

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;
    }
}
like image 215
Sharique Avatar asked May 02 '09 05:05

Sharique


People also ask

Should DB connection be Singleton?

A DB connection should not normally be a Singleton. Two reasons: many DB drivers are not thread safe. Using a singleton means that if you have many threads, they will all share the same connection.

Why you should not use singletons?

By using singletons in your project, you start to create technical debt. Singletons tend to spread like a virus because it's so easy to access them. It's difficult to keep track of where they're used and getting rid of a singleton can be a refactoring nightmare in large or complex projects.

Can we inherit from singleton class?

Unlike static classes, Singleton classes can be inherited, can have base class, can be serialized and can implement interfaces. You can implement Dispose method in your Singleton class. So, static classes are much less flexible compared to Singleton classes.

Which type of class is useful for creating a database connection?

The getConnection() method of DriverManager class is used to establish connection with the database.


2 Answers

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

like image 118
Chad Grant Avatar answered Oct 02 '22 12:10

Chad Grant


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;
      }
   }
}
like image 43
Egil Andre Greaker Avatar answered Oct 02 '22 13:10

Egil Andre Greaker