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 .

Answer 1

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.