Connection Pooling

Question

I have the following code, If I use conn == null in finally do I still use connection pooling? I know it is a good practice to close your connection but how about disposing the whole connection object?

    public void ExecuteNonQuery(SqlCommand Cmd)
    {
        //========== Connection ==========//
        SqlConnection Conn = new SqlConnection(strConStr);

        try
        {
            //========== Open Connection ==========//
            Conn.Open();

            //========== Execute Command ==========//
            Cmd.Connection = Conn;
            Cmd.CommandTimeout = 180;
            Cmd.ExecuteNonQuery();
        }
        catch (Exception Exc)
        {
            throw Exc;
        }
        finally
        {
            //======== Closing Connection ========//
            if (Conn.State == ConnectionState.Open)
            { Conn.Close(); }

            //======== Disposing object ========//
            Conn = null;
        }
    }

Answer 1

Setting it to null is redundant as it will go out of scope anyway at the end of the function. Yes, you are still using connection pooling if you do so.

Answer 2

First, consider using using, second, let the framework handle disposal. The managed providers will do pooling based on the connection string.

public void ExecuteNonQuery(SqlCommand Cmd) 
{ 
    //========== Connection ==========// 
    using(SqlConnection Conn = new SqlConnection(strConStr))
    { 
        //========== Open Connection ==========// 
        Conn.Open(); 

        //========== Execute Command ==========// 
        Cmd.Connection = Conn; 
        Cmd.CommandTimeout = 180; 
        Cmd.ExecuteNonQuery(); 
    } 
} 

Answer 3

• You don't need to set anything to null or nothing in .NET. That is handled automatically by the Garbage Collection.

• It is imperative that you call Conn.Dispose() in both your error handling and also if everything goes as planned. The Try Catch Finally block is great for this. Another alternative is to use the Using keyword when declaring your connection, so that the Connection object will be properly disposed of whatever happens.

• Don't worry about pooling your connections. If you open a new one every time you need to use one, this is fine. ADO.NET pools connections for you behind the scenes. There is a minor performance if you reuse the same open connection object, but to keep things simple don't worry about it.

You could do the same with the following code. The result would be the same. Whether it errors off or not, Conn will be handled properly. Any error will percolate up, as before.

public void ExecuteNonQuery(SqlCommand Cmd)
{
    Using (SqlConnection Conn = new SqlConnection(strConStr));
    {
        //========== Open Connection ==========//
        Conn.Open();

        //========== Execute Command ==========//
        Cmd.Connection = Conn;
        Cmd.CommandTimeout = 180;
        Cmd.ExecuteNonQuery();
    }
}

As you can see, when the only error handling you want/need is to make sure your Connection Objects are disposed of properly, the Using syntax can make things tidy.

Answer 4

You can dispose your object and still use pooling.

And also ff its

1. A Managed application
2. Is a webapplication
3. Has heavy traffic

Then disposing of the object will do you a great favor as the garbagecollector usually won't keep up enough to save you performance.