SqlDataReader.Close() bug?

[Chris Gallucci]

I have a program that creates an instance of a SqlDataReader via the
Microsoft Application Blocks Data Access code.

I use a typical try{} catch{} finally{} pattern for using it...

<codesnippet>
SqlDataReader rdr = SqlManager.ExecuteReader(/* yada, yada, yada */);
try {
while ( rdr.Read() ) {
if ( _stop == true ) break;
// do work
}
}
catch ( Exception ex ) {
// handle error
}
finally {
if ( rdr != null && !rdr.IsClosed ) {
rdr.Close();
}
}
// other stuff to do
</codesnippet>

My problem is that when I exit my "while ( rdr.Read() )" loop before I've
finished reading the rest of the resultset, the call to close behaves
strangely.
Strangely is defined as causing the application to immediately exit. There
is no error thrown, no log written to the event log or to disk (that I can
find). Even in a debug environment there is nothing written to the output
window such as you would expect for a properly terminating and unloading
application.

Can anybody help or shed some light on this problem for me? Maybe something
I can do to better isolate the problem?

Thanks,

ChrisG

 

[Miha Markic [MVP C#]]

Hi Chris,

That's weird - the application shouldn't just disappear.
Try embedding the Close() method into try/catch block to see if there is an
exception thrown.

 

[Chris Gallucci]

Nope. Same behavior.

ChrisG

 

[Miha Markic [MVP C#]]

Hi Chris,

Can you reproduce it on another machine?
It sounds to me like a low level error.

 

[Chris Gallucci]

----- Original Message -----
From: "Miha Markic [MVP C#]" <miha at rthand com>
Newsgroups: microsoft.public.dotnet.framework.adonet
Sent: Thursday, July 29, 2004 5:09 PM
Subject: Re: SqlDataReader.Close() bug?

Hi Chris,

Can you reproduce it on another machine?
It sounds to me like a low level error.

Ugh. Yes it's reproducible on every machine it's run on...

1) Development/workstation environment (source code/trace run)
2) Development/server environment (compiled)
3) Production/server environment (compiled)

 

[Miha Markic [MVP C#]]

Hi Chris,

What is the configuration of those machines?
Is it the same?
Can you create a *simple* reproducibile sample.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

----- Original Message -----
From: "Miha Markic [MVP C#]" <miha at rthand com>
Newsgroups: microsoft.public.dotnet.framework.adonet
Sent: Thursday, July 29, 2004 5:09 PM
Subject: Re: SqlDataReader.Close() bug?

Hi Chris,

Can you reproduce it on another machine?
It sounds to me like a low level error.

Ugh. Yes it's reproducible on every machine it's run on...

1) Development/workstation environment (source code/trace run)
2) Development/server environment (compiled)
3) Production/server environment (compiled)

 

[Chris Gallucci]

Hi Chris,

What is the configuration of those machines?
Is it the same?
Can you create a *simple* reproducibile sample.

Development workstation is WinXP Pro; servers are Win2003 Server. I'll work
on a simple sample. In the interim, I recall reading somewhere that when you
close a reader that it will read to the end. Could that be at issue here?

ChrisG

 

[Miha Markic [MVP C#]]

Development workstation is WinXP Pro; servers are Win2003 Server. I'll work
on a simple sample. In the interim, I recall reading somewhere that when you
close a reader that it will read to the end. Could that be at issue here?

I don't think that it would read to the end. However, what bothers me is
that the applications just exits.
IMO this is very "hard" error in .net world.

 

[Chris Gallucci]

OK, my simple sample is returning an error...

<error>
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.
at System.Data.SqlClient.SqlDataReader.InternalClose(Boolean closeReader)
at System.Data.SqlClient.SqlDataReader.Close()
at SqlDataReaderSample.Class1.Main(String[] args) in c:\my
documents\visual studio
projects\sqldatareadersample\sqldatareadersample\class1.cs:line 40
</error>

This error occurrs after selecting 275,000 records then exiting after
reading the first 10...

<codesnippet>
[STAThread]
static void Main(string[] args)
{
Console.WriteLine("Starting...");
string sql = "SELECT MyColumn FROM MyTable WHERE MyID <= 275000";
SqlDataReader rdr = SqlHelper.ExecuteReader(CONN_STRING,
CommandType.Text, sql);
try
{
int i = 0;
while ( rdr.Read() )
{
if ( i++ > 10 ) break;
Console.WriteLine(rdr.GetString(rdr.GetOrdinal("MyColumn")));
}
if ( rdr != null && !rdr.IsClosed )
{
rdr.Close();
}
rdr = null;
}
catch ( Exception ex )
{
Console.WriteLine(ex.ToString());
}
finally
{
if ( rdr != null && !rdr.IsClosed )
{
rdr.Close();
}
}
Console.WriteLine("Finished. Press any key to exit.");
Console.Read();
}
}
</codesnippet>

ChrisG

 

[Marina]

I would guess this has something to do with your SQL Server or network
configuration and not .NET.

If everyone got this error after closing a datareader, then it would be a
very widespread, and it is not. So it is probably something particular to
your setup.

 

[William \(Bill\) Vaughn]

This makes sense. You did not cancel the operation. First, you asked for
100,000 gallons of water to be pumped into a tank. You opened the spigot and
took out a couple of gallons and then said forget it.

The server does not know you don't want the rest of the rows. You need to
use Command.Cancel first, then close the DataReader. I expect the server is
still fetching rows and ADO.NET is flushing them.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Miha Markic [MVP C#]]

Hi Bill,

This makes sense. You did not cancel the operation. First, you asked for
100,000 gallons of water to be pumped into a tank. You opened the spigot and
took out a couple of gallons and then said forget it.

The server does not know you don't want the rest of the rows. You need to
use Command.Cancel first, then close the DataReader. I expect the server is
still fetching rows and ADO.NET is flushing them.

I am not sure if this is the case because:
a) it really shouldn't just exit the application
b) this ugly piece of code works just fine (note that there are more than
two records)
sqlConnection1.Open();

SqlCommand cmd = new SqlCommand("SELECT * FROM Orders", sqlConnection1);

SqlDataReader rdr = cmd.ExecuteReader();

bool res = rdr.Read();

res = rdr.Read();

rdr.Close();

sqlConnection1.Close();



Well, just my feeling.

 

[Pablo Castro [MS]]

There is no need to empty the reader before closing it. When you call close,
we'll read all the rows and then close it (yes, you'll have to wait until we
stream the entire result-set to the client; I you don't need that many rows,
it's much better to filter on the query on the server side). Calling
Cancel() can help in some scenarios.

In any case, you should never see the process just dispear because of that.
There are a number of exeptional conditions such as some out-of-memory and
some stack-overflow scenarios where the CLR will "fail fast", meaning that
the process will terminate immediately because the integrity of the address
space cannot be guaranteed. You should see this only in extreme scenarios
(e.g. are you doing some form of very deep recusion?).

If you can come-up with a stand-alone reproduction of the problem that we
can run here at MS, I would be *very* interested on debugging the thing and
seeing what's going on. You can post the repro program here on the
newsgroups or feel free email me directly.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Chris Gallucci]

After more widespread testing, partly initiated by me trying
(unsuccessfully) to create a simple example that reproduces the problem,
this appears to be related to a particular table definition and/or a
particular view definition. It works fine when used against the North
schema. I'll follow this up in a SQL Server news group.

Thanks for all the help and feedback.

ChrisG