c# sql what to dispose

Question

I have the code below to query records from a stored procedure but am concerned I may not be disposing what I need to or am disposing when the object would be cleared by the Garbage Collector shortly afterward anyway.

Do I need to dispose the SqlDataReader since it is within the try catch block?

Do I need to run both cmd.Dispose and cmd.Connection.Close or does one infer the other?

Would the garbage collector eventually dispose of all these objects anyway (maybe not timely enough) or do these objects implictly require dispose possibly because of using unmanaged code?

public void GetData(string studentID)
    {
        SqlCommand cmd = new SqlCommand("sp_stored_proc", 
                new SqlConnection(Settings.Default.connectionString)) 
                { CommandType = CommandType.StoredProcedure };
        try
        {
            cmd.Connection.Open();
            cmd.Parameters.AddWithValue("@student_id", studentID);
            SqlDataReader dr = cmd.ExecuteReader();

         //do something with the data

            if (dr != null)
                dr.Dispose();
        }
        catch
        {
            //error handling
        }
        finally
        {
            if (cmd != null)
            {
                cmd.Dispose();
                cmd.Connection.Close();
            }

        }

    }

Answer 1

You should dispose the data reader, and the command. No need to separately close the connection if you dispose the command. You should ideally do both using a using block:

using (SqlCommand cmd = new...)
{
    // do stuff
    using (SqlDataReader dr = cmd.ExecuteReader())
    {
        // do stuff
    }
}

If you need exception handling do that separately either inside or around the using blocks - no need for the finally for the Dispose calls though with using.

Answer 2

If you use something like this:

public void GetData(string studentID)
{
    using (SqlConnection connection = new SqlConnection(Settings.Default.connectionString))
    {
        connection.Open();

        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "sp_stored_proc";
            command.Parameters.AddWithValue("@student_id", studentID);

            using (SqlDataReader dataReader = command.ExecuteReader())
            {
                // do something with the data
            }
        }
    }
}

then all of your Disposable objects will get disposed of correctly. Calling Dispose() on the SqlConnection, SqlCommand and SqlDataReader objects (which is what the using block does when it exits) closes them correctly.

Additionally, this approach keeps all of your variables scoped to where they are used.

The downside to this approach is that if you need error handling using a try/catch, you have to either wrap it around the whole method body, or use several of them to handle connection errors differently from reading errors, etc...

Answer 3

Do I need to dispose the SqlDataReader since it is within the try catch block?

-- Yes, as being inside of the try catch will not call the dispose method.

Do I need to run both cmd.Dispose and cmd.Connection.Close or does one infer the other?

-- Yes, you need to run both. Calling Cmd.dispose does not close the connection.

The dispose method is meant to be used by the programmer to clean up resources which either aren't directly managed by the garbage collector, or the need to be cleared out after the program is done using them to free up space. Technically, one could set up the program so the GC would handle it's disposal, but that's an assumption I wouldn't make, especially since the programmer writing the class exposed the dispose method for you. Putting the Command in a using statement is probably the easiest route, because you know it will get disposed when the code leaves the declaration space.

using (var connection  = new Connection ())
{
   using (var cmd = new Command())
   {



   }
}