What is the best practice to connect to SQL database?

Question

I want to know what is a good practice of connecting to database, i design a Connection Class, and always use it for connect to SQL Server :

public  class Connection
{
    private SqlConnection conn;
    private static Connection myConnection; 

    private Connection()
    {
        try
        {
            conn = new SqlConnection("Data Source=.;Initial Catalog=Padideh;Integrated Security=True");
            conn.Open();
        }
        catch (Exception ex)
        {
            new ErrorHandler().ErrorLoging("", "Connection", ex.Message, "Cannt Connect To DB.");
        }

    }
    public static SqlConnection GetConnection
    {
        get 
        {
            if (myConnection == null || myConnection.conn.State==System.Data.ConnectionState.Closed)
            {
                myConnection = new Connection();
            }
            return myConnection.conn;
        }
    }

    ~Connection()
    {
        try
        {
            myConnection.conn.Close();
        }
        catch (Exception ex)
        {
            new ErrorHandler().ErrorLoging("", "~Connection", ex.Message, "Cannt Close DB Connection.");
        }
    }
}

It's a singleton Class and i used sqlConnection, and i always use this class to connect to SQLserver, i want to know it's a good way and i can use it in every project? and what is a good practice of connecting to database?

A "best way" is a problematic type of question. There are multiple ways, with pros and cons which depend on your task at hand. I personally do not like singletons. You can also consider using an ORM framework. — Vadim, Jan 26 '14 at 07:50

user2864740 · Accepted Answer · 2014-01-26T08:14:49.390

4

I would not create a singleton as shown.

Just create a new Connection per usage context, use using to make dealing with the IDisposable nature easier, and rely on Connection Pooling - which already takes care of the appropriate details.

Overall, I believe this use of a singleton will make dealing the connections - even without threading - harder overall with no/minimal gain.

While I do advocate the use of DI/IoC (although a simple static helper method will do in a pinch), this should be used to obtain a new Connection per usage context (or the appropriate lifetime) which is disposed of promptly when done.

edited Jan 26 '14 at 08:14

answered Jan 26 '14 at 08:09

user2864740

60,010
15
145
220

As long as you always open and close the connection within the internal methods (rather than globally in the constructor/destructor) then presumably the singleton method is then OK? I ask because I think the singleton has the great advantage that you can check it's closed in a single place (the dispose method) - even if the dev makes a mistake and forgets to close it in the other internal methods. – NickG Aug 28 '14 at 16:52

What is the best practice to connect to SQL database?

1 Answers1