1

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

Gabe
  • 49,577
  • 28
  • 142
  • 181
stighy
  • 7,260
  • 25
  • 97
  • 157

4 Answers4

3

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.

Will Marcouiller
  • 23,773
  • 22
  • 96
  • 162
  • Will the reader and command also be disposed in this case? I've always wrapped all three in separate using statements which I've always found annoying. Great readability shortcut if that's the case. – MisterZimbu May 17 '10 at 15:37
  • 1
    The reader will *not* be closed with this code, until the garbage collector gets to it. Use a nested 'using' to deal with the reader. There is no need to do this with the conmmand object. – Ray May 17 '10 at 15:45
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

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
0

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.

Cagdas
  • 819
  • 6
  • 6
  • The garbage collector does not call `Dispose()` implicitly. See http://stackoverflow.com/questions/1691846/does-garbage-collector-call-dispose – Robert Harvey May 17 '10 at 15:43
0

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(); }
    }
}
HasanG
  • 12,734
  • 29
  • 100
  • 154
  • 1
    the catch with the log is fine, the close in the finally is not required because the using calls dispose which calls close. – Davide Piras Sep 16 '11 at 08:21