How to close connection / datareader when using SqlDataSource or ObjectDataSource

Question

during beta testing, we found connection pool error messages. Therefore, I review the code and close the SqlDataReader objects, where they remain unclosed. I need to know how to close the datareader (or if there is a need to close it at all), which is specified in the SelectStatement attribute of the SqlDataSource or ObjectDataSource tags. Could a connection leak if they are not handled?

Thanks in advance!

Answer 1

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

Answer 2

To improve the performance of Close () / Dispose (), call the Cancel () function on the associated command object before deleting or closing the device, especially when you have not reached the end of the recordset.

For example:

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

Answer 3

I understand that with SqlDataSource you are managing the connection and you have nothing to fear.

ObjectDataSource does not access the database directly, so it will be safe if the underlying object correctly manages the connection and read.

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

My guess is that if you cleaned the codebase efficiently, you probably fixed the problem.

Answer 4

We had the same problems here in the production environment.

Solved a problem. At first the problem was that there were no statements in my code. (It was built a few years ago, with some lesser knowledge).

Then I tried putting SqlDataSource in a use clause. But that didn't help either.

The trick here, like tvanfosson and Mischa, offers, placing the reader at the point of use. This is the object that actually closes the connection.

The number of connections is reduced to a minimum pool size of 10 at medium load.

Answer 5

The .Dispose () call should handle clearing and releasing any held resources, but . The Close () method should also be called when the object is being read by the reader .

Answer 6

I believe that SqlDataSource will handle its own connect / read problems, so no worries. Regarding your manual connections, I found this template 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 7

I agree that for an ObjectDataSource object, the closure should be handled by its Select method. My selection method ObjectDataSource returns a SqlDataReader. My concern is that SqlDataReader will be useless when closing after it returns to the user interface. For example, see the following code example. I have not tried and do not want to do this at this stage of development.

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

Thanks for all the data!

...........

I understand that with SqlDataSource, connection management is done for you, and you have nothing to fear.

ObjectDataSource does not talk to the database in the first place, so it will be safe - as long as the base object performs communication and reader control correctly.

As already mentioned, Close () and using your friends for classes you use with ObjectDataSource

.