Should I use only one public DataReader, or multiple?

[Andrei Pociu]

In a typical ASP .NET Web Application (website), I'm currently using a class
where I declare some public static objects. For example there's the place
where I initialize the SqlConnection.
Also there's the place where I declare one public static DataReader, but I'm
not sure this is the best way to do it. I use that DataReader in all the
webforms and user controls, whenever I have to read data from the db.

So, should I do it this way or should I create a new DataReader each time I
need it?

By the way, I started to ask myself this questions since I'm experiencing
different problems with using a public DataReader, like for example "There
is already an open DataReader associated with this Connection which must be
closed first" errors, even if I'm closing all of them.

Thanks for your support.

 

[Paul Glavich [MVP - ASP.NET]]

In your situation, you should *always* create a new instance of the
datareader. I would also be using a new instance of the connection class
too, or at least make sure you have it closed asap after your DB operations.
Because you have the current datareader static, its single instance can be
used many many concurrent threads, and can experience serious concurrency
issues. You either need to make it thread safe by serialising calls to this
static object (bad for performance and you have to code for this) or simply
create a new instance each time. This way, you can ensure each conurrent
thread has its own copy.

 

[Andrei Pociu]

"Because you have the current datareader static, its single instance can be

used many many concurrent threads, and can experience serious concurrency
issues."

That's what I was thinking, also. For now I'll use different instances of
the DataReader. I think I can manage the SQLConnection (opening and closing)
but if I experience problems with it I'll use new instances for it too.

Thank you Paul.