DataReader Close Issue

[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

[Josh Belden]

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;
}

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

[Marina]

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.

<jj****@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

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

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

<jj****@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

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?

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

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

[Nicholas Paldino [.NET/C# MVP]]

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.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Josh Belden" <jo**********@hotmail.com> wrote in message
news:de*************************@news.microsoft.co m...

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;
}

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

[Marina]

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.

"Josh Belden" <jo**********@hotmail.com> wrote in message
news:de*************************@news.microsoft.co m...

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;
}

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