Disposing of SQL Connection

Question

I have a SQL class that connects to the DB and retreives a DataTable. I am aware that the SqlConnection must be disposed when finished. I know this can be done using a using block, but is it also acceptable to put the Dispose() call inside the destructor of this class?

Herre is my code:

public class SQLEng
{

    //Connection String Property
    //Must be set to establish a connection to the database
    public string ConnectionString{ get; set; }
    SqlConnection _Conn;

    //Overridden Constructor enforcing the Connection string to be set when created
    public SQLEng(string connectionString)
    {
        ConnectionString = connectionString;
        _Conn = new SqlConnection(connectionString);
    }

    //ensure the SqlConnection is disposed when destructing this object
    public ~SQLEng()
    {
        _Conn.Dispose();
    }

    //various other methods to get datatables etc...
}

Basically i wish to have a class variable SqlConnection, rather than instantiate the SqlConnection inside every method that accesses the DB. Is this sound practise?

Answer 1

Your design encourages hanging on to a (presumably open) SqlConnection for long periods of time. Best practice is to open a connection just before you need it and then release (close and dispose) it as soon as you are finished.

Yes, there is some overhead associated with creating new connections; connection pooling alleviates much of that processing time. Worse is keeping many connections alive on the server.