how to close connection/datareader when using SqlDataSource or ObjectDataSource

Question

Hi,

during beta testing we discovered connection pooling error messages . Therefore I have been going through the code and closing down the SqlDataReader objects wherever they have been left unclosed. What I need to know is how to close a datareader (or if there is a need at all to close) that is specified in the SelectStatement attribute of the SqlDataSource or ObjectDataSource tags. Could there be connection leak if they are not handled?

Thanks in advance !

Answer 1

I tend to use the "using" keyword, especially when dealing with opening and closing connections to a database. "using" is a shortcut to the Dispose pattern - here is a link to the MSDN writeup, and here is a link to a useful blog entry with an overview.

Answer 2

Calling .Dispose() should handle clean up and release any held resources, but the .Close() method should be getting called as well when an object is done reading from the reader.

Answer 3

My understanding is that with SqlDataSource, connection management is performed for you, and you have nothing to fear.

ObjectDataSource doesn't talk to the database directly in the first place, so it will be safe -- as long as the underlying object performs its connection and reader management correctly.

As others have mentioned, Close() and using are your friends for the classes you use with ObjectDataSource.

My hunch is that if you've scrubbed the codebase effectively, you've probably eradicated the issue.

Answer 4

I believe that the SqlDataSource will handle it's own connection/reader issues, so no worries there. As for your manual connections, I've found this pattern useful in the past:

   using (SqlConnection connection = new SqlConnection(connectionString))
   {
      try
      {
         SqlCommand command = connection.CreateCommand();
         command.CommandText = ...

         connection.Open();
         using (SqlDataReader reader = command.ExecuteReader())
         {
            do
            {
               while (reader.Read())
               {
                  ... handle each row ...
               }
            } while (reader.NextResult());
         }
      }
      catch (Exception ex)
      {
          ... error handling ...
      }
      finally
      {
         if (connection != null && connection.State == ConnectionState.Open)
         {
            connection.Close();
         }
      }
  }

Answer 5

I agree, that for the ObjectDataSource the closing should be handled by its Select method. My ObjectDataSource Select method returns a SqlDataReader. My concern is ... will the SqlDataReader be rendered useless when closed after returning it to the UI. e.g. see the following sample code. I have not tried it and don't want to do it at this stage of development.

SqlDataReader MySelectMethod(){
   SqlDataReader dr = null;
   try{
      dr = DBObject.GetDataReader();
      return dr;
   }
   finally{
      dr.Close();
   }
}

Thanks for all the inputs received so far !

...........

My understanding is that with SqlDataSource, connection management is performed for you, and you have nothing to fear.

ObjectDataSource doesn't talk to the database directly in the first place, so it will be safe -- as long as the underlying object performs its connection and reader management correctly.

As others have mentioned, Close() and using are your friends for the classes you use with ObjectDataSource

.

Answer 6

To improve performance of Close()/Dispose() consider calling Cancel() on the associated command object before disposing or closing the reader, especially when you didn't reach the end of the record set.

For example:

            using (var cmd = ... ))
            {
                using (var reader = (DbDataReader) cmd.ExecuteReader())
                {
                    try
                    {
                        ConsumeData(reader); // may throw
                    }
                    catch(Exception)
                    {
                        cmd.Cancel();
                        throw;
                    }
                }
            }

Answer 7

I don't believe SqlDataSource closes the connection automatically. After running through our code in debug I can see a connection (MySQL) being opened when binding but even after calling Dispose() the connection exists. Any ideas how to close these connections manually since the SqlDataSource does not have a way to access the reader or connection directly?