asp.net Invalid attempt to FieldCount when reader

问题:

This question already has answers here:

Closed 7 years ago.

Possible Duplicate:
Invalid attempt to call FieldCount when reader is closed

im using asp.net with c#. Im atempting to close the connection on the finally statement on aspx2.cs.

In aspx1.cs:

private void BindDataGrids()
{
     try
     {
          DataGrid1.DataSource = instance.ShowDifferences(Convert.ToInt32(Request.QueryString

["CrewId"]), DateTime.Parse(Request.QueryString["StartDate"]), DateTime.Parse(Request.QueryString["EndDate"]));
          DataGrid1.DataBind();
      }
 }

In aspx2.cs:

 public static SqlDataReader ShowDifferences(int crewID, DateTime date1, DateTime date2)
 {
      Database db = DatabaseFactory.CreateDatabase();
      string sqlCommand = "stored_procedure";
      DBCommandWrapper command = db.GetStoredProcCommandWrapper(sqlCommand);
      try
      {
........code...........
         command.AddInParameter("@EndDate", DbType.Date, date2);
                IDataReader reader = db.ExecuteReader(command);
         return (SqlDataReader)reader;
      }
      finally
      {
          command.Command.Connection.Close();
      }

When it reaches DataGrid1.DataBind(); on aspx1.cs.

i get error:

"Invalid attempt to FieldCount when reader is closed error"

How to solve this?

回答1:

You are returning a reader that needs the connection to be open to be able to Read, but you close the connection before ShowDifferences exits. The reader is cursor that fetches data incrementally and has to be connected to the database for the whole process of reading. If you want to have an in-memory dataset (DataTable) disconnected from back-end database, consider using Fill method of SqlDataAdapter.

Update:

To "close the connection after the DataGrid1.DataBind();" move line

command.Command.Connection.Close();

to a separate method of instance's class (e.g. CloseConnection) and call

instance.CloseConnection();

after DataGrid1.DataBind();. For this, of course, you need to change DBCommandWrapper command into instance's class member.

回答2:

You have to open the connection when you use an DbDataReader, ExecuteReader does not open it automatically. Something like this:

using (DbConnection conn = factory.CreateConnection())
{
   conn.ConnectionString = connString;

   DbCommand cmd = conn.CreateCommand();
   cmd.CommandText = "...";

   conn.Open();
   DbDataReader reader = cmd.ExecuteReader();

   while (reader.Read())
   { ...
   }
}

Only when you use a DataSet you don't have to handle the connection. If you really open the connection in your ExecuteReader method, you have to keep it open as long as you keep the reader.