DataReader Close Issue

  • Thread starter Thread starter jjmraz
  • Start date Start date
J

jjmraz

Hi,

I have a situation where in a dll a SqlDataReader is created on a
function call but is never closed. The datareader gets passed back to
the asp.net page calling it. How should I close the SqldataReader in
the function where it is being created properly? If I close the
SqlDataReader in the function my datareader returns nothing of course.
So how do I code it properly to pass back a datareader and close it in
the function?

Thanks,
J
 
Can you return the reader in a try statement and close it in the finally.
I believe the finally will be run when the reader is no longer referenced,
but not sure.

public SqlDataReader GetDataReader()
{
SqlDataReader retVal = nul;
try
{
// Initialize data reader
retVal = new SqlDataReader();
return retVal;
}
finally
{
retVal.Close();
}

// in case something happens
return null;
}
 
You don't close the datareader in the function. But you do pass
CommandBehavior.CloseConnection to the ExecuteReader.

Then, whatever code is calling the function is responsible for calling Close
on the data reader. Since you passed in CloseConnection to ExecuteReader,
closing the datareader will close the connection as well.

Or, you can rethink this, and see if your function can return a DataTable
instead, then you dont' have to deal with any of this, or worry about
remember to close the datareader when you are done.
 
Hi,

Hi,

I have a situation where in a dll a SqlDataReader is created on a
function call but is never closed. The datareader gets passed back to
the asp.net page calling it. How should I close the SqldataReader in
the function where it is being created properly? If I close the
SqlDataReader in the function my datareader returns nothing of course.
So how do I code it properly to pass back a datareader and close it in
the function?
Click to expand...


You should call Close once you are done with the DataReader this will
effectively close it. You can it from anywhere you have access to it.

Now, depending of how it was created ( if a CommandBehavior.CloseConnection
was passed as a parameter) the underlying connection will get closed too.

Use this overload to create the datareader and you should be ok
 
This won't work. The reason is that the reader will be closed before
the function returns. Remember, the finally statement gets called no matter
what.
 
No, the finally block will run after either the try finishes, or the catch
does (if an exception is thrown). This will close the datareader before it
is returned. You might want to look over how try/catch/finally blocks work.
 
Back
Top