Closed DataReader

[Guest]

Hi,

I have a class A with a public method GetReader:
public OleDbDataReader GetReader()
{
SqlConnection myConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnStr"]);
SqlCommand myCommand = new SqlCommand("CMRC_Top10List", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

myConnection.Open();
SqlDataReader result =
myCommand.ExecuteReader(CommandBehavior.CloseConnection);


return result;
}


Class A is used as following:

DataGrid TopTen;
A top10 = new A();

SqlDataReader result = null;

try
{
result = top10.GetReader();
TopTen.DataSource = result;
TopTen.DataBind();
result.Close();
}
catch(Exception ex)
{
if (!result.IsClosed)
{
result.Close();
}
return;
}

The grid is not bind to the data in database, and I receive the following
error:
“Invalid attempt to FieldCount when reader is closed."

Why is reader closed after the return from the method GetReader? I would
expect the reader being open till I explicitly close him.

Thanks for a help.

Lubomir

 

[Karl Seguin]

The reader is closed because you are closing the underlying connection....a
reader always connected to the database and thus requires an open
connection. The connection is closing because you've specified the
CloseConnection behavior....

You could remove the commandBehavior ,but then when/how will you close the
connection? Your two options are

(a) use a dataset instead which is disconnected form the database (Doesn't
require an open connection after the data is fetched)
(b) Pass the connection into the method, so that your calling code has a
reference to it which IT can be responsible for closing..

Karl

 

[Matt Berther]

Hello Karl,

CommandBehavior.CloseConnection closes the connection when the DataReader
is closed. Are you suggesting that the because the connection goes out of
scope, it is closed? I would think that if a reference was still being used
it wouldnt be gc'd. My GC knowledge is pretty limited though.

The thing that I find interesting is that OleDbDataReader is the type returned
from the GetReader method, however the variable result (which is what's returned)
is a type of SqlDataReader.

I'm not sure that this could should even compile, but if it does, I would
think this has something to do with it.

Of course, this whole thing could be cited as a reason for not returning
SqlDataReader from your methods (which FxCop flags as an error).

--
Matt Berther
http://www.mattberther.com

The reader is closed because you are closing the underlying
connection....a reader always connected to the database and thus
requires an open connection. The connection is closing because you've
specified the CloseConnection behavior....

You could remove the commandBehavior ,but then when/how will you close
the connection? Your two options are

(a) use a dataset instead which is disconnected form the database
(Doesn't
require an open connection after the data is fetched)
(b) Pass the connection into the method, so that your calling code has
a
reference to it which IT can be responsible for closing..
Karl

Hi,

I have a class A with a public method GetReader:
public OleDbDataReader GetReader()
{
SqlConnection myConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnStr"]);
SqlCommand myCommand = new SqlCommand("CMRC_Top10List",
myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

myConnection.Open();
SqlDataReader result =
myCommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
Class A is used as following:

DataGrid TopTen;
A top10 = new A();
SqlDataReader result = null;

try
{
result = top10.GetReader();
TopTen.DataSource = result;
TopTen.DataBind();
result.Close();
}
catch(Exception ex)
{
if (!result.IsClosed)
{
result.Close();
}
return;
}
The grid is not bind to the data in database, and I receive the
following
error:
"Invalid attempt to FieldCount when reader is closed."
Why is reader closed after the return from the method GetReader? I
would expect the reader being open till I explicitly close him.

Thanks for a help.

Lubomir

 

[Karl Seguin]

Well, you learn something every day...that's certainly something I've gotten
wrong in the past. Thaxn for clearing it up....It certainly makes more
sense (and has more value) this way....

Now that you point that out, I'd guess the same thing you did..the
inconsistancy between OleDb and Sql...

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

Hello Karl,

CommandBehavior.CloseConnection closes the connection when the DataReader
is closed. Are you suggesting that the because the connection goes out of
scope, it is closed? I would think that if a reference was still being
used it wouldnt be gc'd. My GC knowledge is pretty limited though.

The thing that I find interesting is that OleDbDataReader is the type
returned from the GetReader method, however the variable result (which is
what's returned) is a type of SqlDataReader.

I'm not sure that this could should even compile, but if it does, I would
think this has something to do with it.

Of course, this whole thing could be cited as a reason for not returning
SqlDataReader from your methods (which FxCop flags as an error).

--
Matt Berther
http://www.mattberther.com

The reader is closed because you are closing the underlying
connection....a reader always connected to the database and thus
requires an open connection. The connection is closing because you've
specified the CloseConnection behavior....

You could remove the commandBehavior ,but then when/how will you close
the connection? Your two options are

(a) use a dataset instead which is disconnected form the database
(Doesn't
require an open connection after the data is fetched)
(b) Pass the connection into the method, so that your calling code has
a
reference to it which IT can be responsible for closing..
Karl

Hi,

I have a class A with a public method GetReader:
public OleDbDataReader GetReader()
{
SqlConnection myConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnStr"]);
SqlCommand myCommand = new SqlCommand("CMRC_Top10List",
myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

myConnection.Open();
SqlDataReader result =
myCommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
Class A is used as following:

DataGrid TopTen;
A top10 = new A();
SqlDataReader result = null;

try
{
result = top10.GetReader();
TopTen.DataSource = result;
TopTen.DataBind();
result.Close();
}
catch(Exception ex)
{
if (!result.IsClosed)
{
result.Close();
}
return;
}
The grid is not bind to the data in database, and I receive the
following
error:
"Invalid attempt to FieldCount when reader is closed."
Why is reader closed after the return from the method GetReader? I
would expect the reader being open till I explicitly close him.

Thanks for a help.

Lubomir