objectdatasource sqldatareader and conection pooling

[Doug Salomon]

Hi,

Recently, my site has been generating errors which say:
"Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached."

I'm wondering if this might have something to do with the way that we use
objectdatasource objects.

In an aspx page, we would put a gridview/dataview/whatever and bind it to an
objectdatasource.

The objectdatasource pulls data from a middle tier component which often
simply returns a sqldatareader object.

We're using the Enterprise Library's Data Access Application Block to handle
some of the data access functionality, and I believe that the DAAB
automatically sets the CommandBehavior.CloseConnection propperty.

However, because we are simply returning the datareader from the middle
tier, and passing them along to the objectdatasource on the aspx page, there
is no way (that I can think of) to call datareader.close or connection.close.

So, my question is: Does an objectdatasource close the datareader (and
connection) or is this all left open until the garbage collector comes along?

And if it is left open, is there simple way around this, or should I recode
the middle-tier to return datasets (or datatables or some other object) so
that I can close the connection manually?

thanks,
Doug

[Peter Bromberg [C# MVP]]

Seems clear to me that you've answered your own question. If you are getting
errors like this you can be pretty sure that those particular datareaders
aren't being closed and / or the connnections aren't being closed or Disposed.

DataSets, using a SqlDataAdapter which takes care of all this on its own, is
one good solution. There's a bit extra overhead over the straight reader but
in the big scheme of things you can find many other areas where optimization
can have much more effect - caching, etc.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Doug Salomon" wrote:

Hi,

Recently, my site has been generating errors which say:
"Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached."

I'm wondering if this might have something to do with the way that we use
objectdatasource objects.

In an aspx page, we would put a gridview/dataview/whatever and bind it to an
objectdatasource.

The objectdatasource pulls data from a middle tier component which often
simply returns a sqldatareader object.

We're using the Enterprise Library's Data Access Application Block to handle
some of the data access functionality, and I believe that the DAAB
automatically sets the CommandBehavior.CloseConnection propperty.

However, because we are simply returning the datareader from the middle
tier, and passing them along to the objectdatasource on the aspx page, there
is no way (that I can think of) to call datareader.close or connection.close.

So, my question is: Does an objectdatasource close the datareader (and
connection) or is this all left open until the garbage collector comes along?

And if it is left open, is there simple way around this, or should I recode
the middle-tier to return datasets (or datatables or some other object) so
that I can close the connection manually?

thanks,
Doug

[Doug Salomon]

Well, I hate answering my own question. :(

Uch, this is going to be a lot of very boring work.

Well, as long as I'm going to recode this, is there any reason to use a full
dataset, or could I save bit of memory overhead and just return a datatable
object?

thanks for answering,
Doug

"Peter Bromberg [C# MVP]" wrote:

Seems clear to me that you've answered your own question. If you are getting
errors like this you can be pretty sure that those particular datareaders
aren't being closed and / or the connnections aren't being closed or Disposed.

DataSets, using a SqlDataAdapter which takes care of all this on its own, is
one good solution. There's a bit extra overhead over the straight reader but
in the big scheme of things you can find many other areas where optimization
can have much more effect - caching, etc.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Doug Salomon" wrote:

Hi,

Recently, my site has been generating errors which say:
"Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached."

I'm wondering if this might have something to do with the way that we use
objectdatasource objects.

In an aspx page, we would put a gridview/dataview/whatever and bind it to an
objectdatasource.

The objectdatasource pulls data from a middle tier component which often
simply returns a sqldatareader object.

We're using the Enterprise Library's Data Access Application Block to handle
some of the data access functionality, and I believe that the DAAB
automatically sets the CommandBehavior.CloseConnection propperty.

However, because we are simply returning the datareader from the middle
tier, and passing them along to the objectdatasource on the aspx page, there
is no way (that I can think of) to call datareader.close or connection.close.

So, my question is: Does an objectdatasource close the datareader (and
connection) or is this all left open until the garbage collector comes along?

And if it is left open, is there simple way around this, or should I recode
the middle-tier to return datasets (or datatables or some other object) so
that I can close the connection manually?

thanks,
Doug

[heinz]

Doug Salomon wrote:

Well, I hate answering my own question. :(

Uch, this is going to be a lot of very boring work.

Well, as long as I'm going to recode this, is there any reason to use a full
dataset, or could I save bit of memory overhead and just return a datatable
object?

thanks for answering,
Doug

"Peter Bromberg [C# MVP]" wrote:

Seems clear to me that you've answered your own question. If you are getting
errors like this you can be pretty sure that those particular datareaders
aren't being closed and / or the connnections aren't being closed or Disposed.

DataSets, using a SqlDataAdapter which takes care of all this on its own, is
one good solution. There's a bit extra overhead over the straight reader but
in the big scheme of things you can find many other areas where optimization
can have much more effect - caching, etc.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Doug Salomon" wrote:

Hi,
>
Recently, my site has been generating errors which say:
"Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached."
>
I'm wondering if this might have something to do with the way that we use
objectdatasource objects.
>
In an aspx page, we would put a gridview/dataview/whatever and bind it to an
objectdatasource.
>
The objectdatasource pulls data from a middle tier component which often
simply returns a sqldatareader object.
>

seems imao to be a good case to use 'ASP.NET Output Caching with SQL
Server '
(not page caching).
wawens

We're using the Enterprise Library's Data Access Application Block to handle
some of the data access functionality, and I believe that the DAAB
automatically sets the CommandBehavior.CloseConnection propperty.
>
However, because we are simply returning the datareader from the middle
tier, and passing them along to the objectdatasource on the aspx page, there
is no way (that I can think of) to call datareader.close or connection.close.
>
So, my question is: Does an objectdatasource close the datareader (and
connection) or is this all left open until the garbage collector comes along?
>
And if it is left open, is there simple way around this, or should I recode
the middle-tier to return datasets (or datatables or some other object) so
that I can close the connection manually?
>
thanks,
Doug