data access layer and data reader

[Guest]

hey all,
how do you resolve this problem?

i have a public procedure in my DataAccessLayer that gets a SqlDataReader

how do i close the reader from inside the DataAccessLayer if I'm returning
the reader to get bound to my GridView? Any code after my return will be
unreachable.

thanks,
rodchar

 

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

rodchar,

There are a few ways to do this. The easiest way that I can think of to
do this would be when you call ExecuteReader on your command, you pass the
CloseConnection value from the CommandBehavior enumeration to the method.
Then, you make it a requirement that whatever routine gets the SqlDataReader
calls Dispose on the reader, so that the underlying connection is closed.

This won't clean up the command that is used to create the reader, but
it doesn't do much in its Dispose implementation anyways that is important
(it just releases a managed reference to some metadata, so that's no big
deal), so I think you can get away with not calling Dispose on the
connection (although I don't like the idea of not doing so, you can't call
Dispose on the connection before you return the reader).

If you REALLY want to be clean about things, what you can do is return a
class that implements IDisposable and exposes the SqlDataReader through a
property. You would then pass the command as well to the class (which would
hold onto it in a private field). You would then require that instead of
calling Dispose on the reader, you call Dispose on the class that returns
the reader. In the implementation of the Dispose method, you would call
Dispose on the data reader as well as the command.

Hope this helps.

 

[Guest]

Thank you for the thorough explanation, these are my favorite. I learn too.

rodchar,

There are a few ways to do this. The easiest way that I can think of to
do this would be when you call ExecuteReader on your command, you pass the
CloseConnection value from the CommandBehavior enumeration to the method.
Then, you make it a requirement that whatever routine gets the SqlDataReader
calls Dispose on the reader, so that the underlying connection is closed.

This won't clean up the command that is used to create the reader, but
it doesn't do much in its Dispose implementation anyways that is important
(it just releases a managed reference to some metadata, so that's no big
deal), so I think you can get away with not calling Dispose on the
connection (although I don't like the idea of not doing so, you can't call
Dispose on the connection before you return the reader).

If you REALLY want to be clean about things, what you can do is return a
class that implements IDisposable and exposes the SqlDataReader through a
property. You would then pass the command as well to the class (which would
hold onto it in a private field). You would then require that instead of
calling Dispose on the reader, you call Dispose on the class that returns
the reader. In the implementation of the Dispose method, you would call
Dispose on the data reader as well as the command.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

hey all,
how do you resolve this problem?

i have a public procedure in my DataAccessLayer that gets a SqlDataReader

how do i close the reader from inside the DataAccessLayer if I'm returning
the reader to get bound to my GridView? Any code after my return will be
unreachable.

thanks,
rodchar

 

[sloan]

You cannot close the datareader, until after it binds.

Thus why using an IDataReader is tricky (in the presentation layer).

This is one reason to go to either DataSets (strong) or Custom Business
Objects.

I'm not saying you have to, I'm saying its "a" reason.

What I usually do is use the IDataReader in the biz layer, and close it
there. And send up to the presentation layer a collection of custom
business objects.
I trust myself (and gui developers who might forget to close the
IDataReader) better this way.

However, on occasion, I close the IDataReader in the presentation layer,
after binding.
I'd put this in a finally statement to make sure its runs.

IDataReader idr = null;
try
{
idr = Something.GetReader();
GridView1.DataSource = idr;
GridView1.DataBind();
}
finally
{
if (null!=idr)
{
idr.Close();
}
}


is typical code in that arena.