C# - closing Sql objects best practice

Question

If you have a C# function with Sqlaccess, is it mandatory to close all objects/handles, or is everything cleaned up automatically once you exit the function

For example:

void DoSqlStuff()
{
    SqlConnection sqlConn = new SqlConnection(...);
    SqlCommand cmd = new SqlCommand(...);
    SqlDataReader sqlData= null;

    sqlConn,Open();
    sqlData = cmd.ExecutReader();


    while(sqlData.Read())
    {
         ...
    }
}

Is it optional, recommended or mandatory to close SqlConn and SqlData?

Thanks.

Answer 1

You should close the SqlConnection object as soon as you're done with it. If you don't then the connection will remain open, and will not be available to handle other requests.

The using statement is useful for this. It will call Dispose() on the object for you:

using (SqlConnection cn = new SqlConnection(connectionString))
{   
    SqlCommand cm = new SqlCommand(commandString, cn)
    cn.Open();
    cm.ExecuteNonQuery();       
}

Answer 2

You don't need to have a separate using statement for the SqlDataReader (as well as one using statement for the connection) unless you plan do perform other operations with the connection after the SqlDataReader has fully read through the row set.

If you are just opening a connection, reading some data using the reader, and then closing the connection, then one using statement for the entire block of code (surrounding the connection) will suffice as the garbage collector will clean up all resources tied to the connection that is disposed by the first using statement.

Anyway, here's a good article that describes it all...