Using block for Closing/Disposing Data Connection.

Hi,

 

using (SqlConnection cn = new SqlConnection(connectionString))
{
    cn.Open();
}

 

 

Issues With Above Approach:

 

1. “Using” block works with auto dispose of connection and not closing of connection explicitly.
2. The object is marked as disposed, but that does not mean it is cleaned up yet.
3. It leaves for GC to dispose the connection object, doesn’t return the connection to pool immediately and when calling a lot of SP’s, we get connection or DB timeout Error.
4. Ideally object is release when the object is closed, thus relying on implict Dispose() to close() is not good.
5. Thus we can definitely see some improvement if we explicitly close the connection on completion of our DB query.
6. If any error occur within function block database connections are not closed

 

Possible Resolution Solution:

 

1. Create your own connection object and open close them within try-finally explicitly to assure definite close of open connections which are then immediately released to pool of connection.Sample code as below-

 

   SqlConnection conn = new SqlConnection(DatabaseConnection.CONNECTION_STRING);

 

            try

            {

                conn.Open();

            }

            catch (SqlException sqlExp)

            {

                Tracer.TraceExceptionAndClose(sqlExp, TracerEventTypes.Critical, 100);

                return null;

            }

            finally

            {

                if (conn.State == ConnectionState.Open)

                {

                    conn.Close();

                    conn.Dispose();//is good practice 

                    conn=null; //is good practice not must

                }

            }

 

 

The ‘Using’ block will not work for DataReader to close connections:

 

Often, we also use the using statement with an ExecuteReader. Which means at the end of the using block (in case of exceptions as well), the Dipose method on the DataReader will get called. This does not explicitly close the connection. It requires the use of CommandBehavior.CloseConnection option, which is an indication that the connection should be closed when the DataReader is closed.

 

 

Thanks & Regards,

Arun Manglick

SMTS || Microsoft Technology Practice || Bridgestone - Tyre Link || Persistent Systems || 3023-6258

 

DISCLAIMER ========== This e-mail may contain privileged and confidential information which is the property of Persistent Systems Pvt. Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Pvt. Ltd. does not accept any liability for virus infected mails.