how to close a sqlconnection in asp.net

Question

Hi guys, i would like to know if there's something wrong in this asp.net code:

mydatareader = mycmd.executeReader()
if myDataReader.HasRow then
      // Do something
end if
myConnection.Close()

If i avoid to call a "MyDataReader.Close()" does the connection close anyway ? I ask this because i'm assuming that if call a "MyConn.Close" it automatically close the associated datareader... or am i wrong ?

Thanks

Answer 1

The best practice to perform such operations is as follows:

using(SqlConnection connection = new SqlConnection(connStr)) {
    connection.Open();

    SqlCommand command = new SqlCommand(connection, "SELECT...");
    SqlDataReader reader = command.ExecuteReader();
    // Fill your container objects with data
}

The using statement:

Defines a scope, outside of which an object or objects will be disposed.

So you can be assured that your connection, command and reader variables will be closed and disposed accordingly when exiting the using block.

Answer 2

If you neither close the data reader nor the connection, garbage collector will do it for you. On the other side, this will probably affect the performance of your application.

Answer 3

You have to close your reader instead of closing the connection. Have a look here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.close.aspx

Answer 4

I'm not absolutely sure but I'm always using try-catch-finally block for db actions:

using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
    using (SqlCommand cmmnd = new SqlCommand("SELECT Date();", cnn))
    {
        try
        {
            cnn.Open();
            using (SqlDataReader rdr = cmmnd.ExecuteReader())
            {
                if (rdr.Read()) { result = rdr[0].ToString(); }
            }
        }
        catch (Exception ex) { LogException(ex); }
        finally { cnn.Close(); }
    }
}