470,619 Members | 1,465 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,619 developers. It's quick & easy.

Closed DataReader

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.CloseConne ction);
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

Nov 19 '05 #1
3 1761
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

--
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!)
"Lubomir" <Lu*****@discussions.microsoft.com> wrote in message
news:0C**********************************@microsof t.com...
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.CloseConne ction);
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

Nov 19 '05 #2
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

"Lubomir" <Lu*****@discussions.microsoft.com> wrote in message
news:0C**********************************@microsof t.com...
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.CloseConne ction);
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


Nov 19 '05 #3
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!)
"Matt Berther" <mb******@hotmail.com> wrote in message
news:22***************************@news.microsoft. com...
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

"Lubomir" <Lu*****@discussions.microsoft.com> wrote in message
news:0C**********************************@microsof t.com...
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.CloseConne ction);
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


Nov 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by jazzandlatin | last post: by
10 posts views Thread by Brent | last post: by
2 posts views Thread by Patrick Olurotimi Ige | last post: by
3 posts views Thread by Patrick Olurotimi Ige | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.