Does SqlConnection get disposed using this function

Question

public CategorieEquipement Select(int NoType)
{
        SqlConnection cx = new SqlConnection(WebConfigurationManager.ConnectionStrings["SQLConnect"].Connection    String);
        SqlDataReader reader;

        CategorieEquipement lstCategorie = new CategorieEquipement();
        try
        {
            cx.Open();
            SqlCommand com = new SqlCommand("SELECT_CategorieEquip", cx);
            com.CommandType = System.Data.CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@where",NoType);
            reader = com.ExecuteReader();

            while (reader.Read())
            {
                lstCategorie.CodeRef = reader["CodeRef"].ToString();
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine("SELECT ERROR : " + ex.ToString());
            return null;
        }
        finally
        {
            if (cx != null)
            {
                cx.Close();
            }
        }
        return lstCategorie;
    }
}

My question is if I remove the finally block of code, will the garbage collector close the connection when disposing of the SQlConnection object?

I know it is a better practice to be explicit but my coworker doesn't agree .

why not wrap the Sql Objects around a using(){} to take advantage of auto disposing.. the GC is not always immediate either.. I agree with @Steve as well perhaps you need to not rely on your cow-worker giving you flawed advice / information — MethodMan
– MethodMan, Commented Nov 13, 2015 at 16:32

Habib · Accepted Answer · 2015-11-13 16:29:39Z

will the garbage collector close the connection when disposing of the SQlConnection object?

Garbage collector is not responsible for calling Dispose on an object, usually Dispose is called in the Finalizer, only then GC would be able to properly dispose the object.

One important thing to note is that you cannot predict when the garbage collection process will run, so it is always better to explicitly dispose objects (which implements IDisposable).

As far as database connections are concerned the approach should be open as late as possible and close as early as possible.

In the case above cx.Close(); should be enough, instead, you can also call cx.Dispose, But a better approach would be to enclose the SqlConnection in using statement block.

That will translate into try/finally block and it will ensure the SqlConnection disposal.

user449689 · Accepted Answer · 2015-11-13 16:39:52Z

1

The garbage collection will dispose it, but since it is non-deterministic you don't know when it will do it.

C# provide the using structure to dispose unmanaged code and it is recommended to use it:

using (SqlConnection cx = new SqlConnection(WebConfigurationManager.ConnectionStrings["SQLConnect"].ConnectionString);)
{

}

Tell your co-workers they ought to wrap any instance of objects that implement the IDisposable interface in a using so that they will be disposed in a deterministic fashion to ensure a correct management of the application resources and avoid problems like memory leaks.

answered Nov 13, 2015 at 16:39

user449689

3,1544 gold badges22 silver badges37 bronze badges

Collectives™ on Stack Overflow

Does SqlConnection get disposed using this function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related