Passing around open readers

[blue]

We often get connection pooling errors saying that there are no available
connections in the pool.

I think the problem is that we are passing around open readers all over the
place. I am planning on changing this in our code and I expect this to fix
our problem.

We have our connection pooling set to the default number of connections
open. We probably have about 3-7 users concurrently using our web site.
So, the problem isn't that we have too many users.

If passing open readers to methods is causing our problem, my question is
why? Is it making a copy of the reader when it gets passed around and the
copy isn't getting closed?

We are doing this:

public void method1()
{
SqlDataReader reader = SqlHelper.ExecuteReader(...);

method2(reader);

reader.Close();
}

We are closing the reader on the calling side but I suspect that a new copy
of the open reader is staying open in the called method.

What do you think? Am I on the right track?

Thanks,

blue

[Tu-Thach]

blue,
Did you close the SqlConnection object as well?
Tu-Thach

----- blue wrote: -----

We often get connection pooling errors saying that there are no available
connections in the pool.

I think the problem is that we are passing around open readers all over the
place. I am planning on changing this in our code and I expect this to fix
our problem.

We have our connection pooling set to the default number of connections
open. We probably have about 3-7 users concurrently using our web site.
So, the problem isn't that we have too many users.

If passing open readers to methods is causing our problem, my question is
why? Is it making a copy of the reader when it gets passed around and the
copy isn't getting closed?

We are doing this:

public void method1()
{
SqlDataReader reader = SqlHelper.ExecuteReader(...);

method2(reader);

reader.Close();
}

We are closing the reader on the calling side but I suspect that a new copy
of the open reader is staying open in the called method.

What do you think? Am I on the right track?

Thanks,

blue

[Sharon]

In C# the default for mathod parameter is "by value".
This means that the reader is copyed and holds the connection open.
To pass the parameter "by reference" add ref to the method definition:
public void method2(ref SqlDataReader dr)
Hope this helps.
Sharon.

"blue" <bl**@arizona.edu> wrote in message
news:uE**************@TK2MSFTNGP12.phx.gbl...

We often get connection pooling errors saying that there are no available
connections in the pool.

I think the problem is that we are passing around open readers all over the place. I am planning on changing this in our code and I expect this to fix our problem.

We have our connection pooling set to the default number of connections
open. We probably have about 3-7 users concurrently using our web site.
So, the problem isn't that we have too many users.

If passing open readers to methods is causing our problem, my question is
why? Is it making a copy of the reader when it gets passed around and the
copy isn't getting closed?

We are doing this:

public void method1()
{
SqlDataReader reader = SqlHelper.ExecuteReader(...);

method2(reader);

reader.Close();
}

We are closing the reader on the calling side but I suspect that a new copy of the open reader is staying open in the called method.

What do you think? Am I on the right track?

Thanks,

blue

[Teemu Keiski]

Hi,

Reference types (which SqlDataReader is) passed as parameter are always
passing a copy of reference to the method, despite do you have ref keyword
or not With reference type the ultimate result is the same if you call
methods or change properties, though ref keyword has impact on if you can
reassign the original reference (without ref keyword you can impact on the
object by accessing members but you can't reassign the reference e.g it has
no effect outside the method, with ref keyword, you can do that too).

In this case it means that the SqlDataReader object is *not* copied but the
reference to it is. When accessing the copied reference, the copy is
indistinguishable from the original reference. So if you call Close on
another reference, it is closed for all references (as it is one and the
same underlying object). So it shouldn't be the reason here.

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist

"Sharon" <ta*******@hotmail.com> wrote in message
news:Ov**************@TK2MSFTNGP10.phx.gbl...
In C# the default for mathod parameter is "by value".
This means that the reader is copyed and holds the connection open.
To pass the parameter "by reference" add ref to the method definition:
public void method2(ref SqlDataReader dr)
Hope this helps.
Sharon.

"blue" <bl**@arizona.edu> wrote in message
news:uE**************@TK2MSFTNGP12.phx.gbl...

We often get connection pooling errors saying that there are no available
connections in the pool.

I think the problem is that we are passing around open readers all over the place. I am planning on changing this in our code and I expect this to fix our problem.

We have our connection pooling set to the default number of connections
open. We probably have about 3-7 users concurrently using our web site.
So, the problem isn't that we have too many users.

If passing open readers to methods is causing our problem, my question is
why? Is it making a copy of the reader when it gets passed around and the
copy isn't getting closed?

We are doing this:

public void method1()
{
SqlDataReader reader = SqlHelper.ExecuteReader(...);

method2(reader);

reader.Close();
}

We are closing the reader on the calling side but I suspect that a new copy of the open reader is staying open in the called method.

What do you think? Am I on the right track?

Thanks,

blue

[Teemu Keiski]

Just to add, that I still wouldn't pass DataReaders through methods just
like that, because it might make things more complicated from error handling
standpoint (reader and db connection must be closed in error situations as
well). Therefore at least try...catch...finally block would be good when
passing the DataReader to the method and make sure that Close is called in
finally.

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist
"Teemu Keiski" <jo****@aspalliance.com> wrote in message
news:uf**************@TK2MSFTNGP12.phx.gbl...
Hi,

Reference types (which SqlDataReader is) passed as parameter are always
passing a copy of reference to the method, despite do you have ref keyword
or not With reference type the ultimate result is the same if you call
methods or change properties, though ref keyword has impact on if you can
reassign the original reference (without ref keyword you can impact on the
object by accessing members but you can't reassign the reference e.g it has
no effect outside the method, with ref keyword, you can do that too).

In this case it means that the SqlDataReader object is *not* copied but the
reference to it is. When accessing the copied reference, the copy is
indistinguishable from the original reference. So if you call Close on
another reference, it is closed for all references (as it is one and the
same underlying object). So it shouldn't be the reason here.

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist

"Sharon" <ta*******@hotmail.com> wrote in message
news:Ov**************@TK2MSFTNGP10.phx.gbl...
In C# the default for mathod parameter is "by value".
This means that the reader is copyed and holds the connection open.
To pass the parameter "by reference" add ref to the method definition:
public void method2(ref SqlDataReader dr)
Hope this helps.
Sharon.

"blue" <bl**@arizona.edu> wrote in message
news:uE**************@TK2MSFTNGP12.phx.gbl...

We often get connection pooling errors saying that there are no available
connections in the pool.

I think the problem is that we are passing around open readers all over the place. I am planning on changing this in our code and I expect this to fix our problem.

We have our connection pooling set to the default number of connections
open. We probably have about 3-7 users concurrently using our web site.
So, the problem isn't that we have too many users.

If passing open readers to methods is causing our problem, my question is
why? Is it making a copy of the reader when it gets passed around and the
copy isn't getting closed?

We are doing this:

public void method1()
{
SqlDataReader reader = SqlHelper.ExecuteReader(...);

method2(reader);

reader.Close();
}

We are closing the reader on the calling side but I suspect that a new copy of the open reader is staying open in the called method.

What do you think? Am I on the right track?

Thanks,

blue

[Sharon]

Thanks for the important correction.

"Teemu Keiski" <jo****@aspalliance.com> wrote in message
news:uf**************@TK2MSFTNGP12.phx.gbl...

Hi,

Reference types (which SqlDataReader is) passed as parameter are always
passing a copy of reference to the method, despite do you have ref keyword
or not With reference type the ultimate result is the same if you call
methods or change properties, though ref keyword has impact on if you can
reassign the original reference (without ref keyword you can impact on the
object by accessing members but you can't reassign the reference e.g it has no effect outside the method, with ref keyword, you can do that too).

In this case it means that the SqlDataReader object is *not* copied but the reference to it is. When accessing the copied reference, the copy is
indistinguishable from the original reference. So if you call Close on
another reference, it is closed for all references (as it is one and the
same underlying object). So it shouldn't be the reason here.

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist

"Sharon" <ta*******@hotmail.com> wrote in message
news:Ov**************@TK2MSFTNGP10.phx.gbl...
In C# the default for mathod parameter is "by value".
This means that the reader is copyed and holds the connection open.
To pass the parameter "by reference" add ref to the method definition:
public void method2(ref SqlDataReader dr)
Hope this helps.
Sharon.

"blue" <bl**@arizona.edu> wrote in message
news:uE**************@TK2MSFTNGP12.phx.gbl...

We often get connection pooling errors saying that there are no available connections in the pool.

I think the problem is that we are passing around open readers all over

the

place. I am planning on changing this in our code and I expect this to

fix

our problem.

We have our connection pooling set to the default number of connections
open. We probably have about 3-7 users concurrently using our web site.
So, the problem isn't that we have too many users.

If passing open readers to methods is causing our problem, my question is why? Is it making a copy of the reader when it gets passed around and the copy isn't getting closed?

We are doing this:

public void method1()
{
SqlDataReader reader = SqlHelper.ExecuteReader(...);

method2(reader);

reader.Close();
}

We are closing the reader on the calling side but I suspect that a new

copy

of the open reader is staying open in the called method.

What do you think? Am I on the right track?

Thanks,

blue