Passing around open readers

  • Thread starter Thread starter blue
  • Start date Start date
B

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
 
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
 
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.
 
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

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.
 
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


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

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.
 
Thanks for the important correction.

Teemu Keiski said:
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

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.
Click to expand...
 
BTW, for anyone interested in a technical explanation of this. A reference
type is a pointer, which means that the variable itself holds the address in
memory of the value it is pointing to (in this case, an instance of a
class). Therefore, a pointer passed by value is a copy of the original
pointer, which points to the same location in memory as the original pointer
(that is, the instance of the class which the original pointer is pointing
to). Therefore, any operations you perform on the class passed will affect
the original class itself, regardless of whether the class instance is
passed by reference or by value, since the class itself is not really
passed, but a pointer to it.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
Back
Top