错误:在 while 循环后关闭 reader 时调用 Read 的尝试无效? [英] Error: Invalid attempt to call Read when reader is closed after the while loop?

问题描述

你好，我有一个方法可以从 sql 中读取一些数据并将它们保存到数组中.

Hello i have a method which reads some data from the sql and saves them to arrays.

为了找出 sql 结果有多少行，我是这样写的:

to find out how many rows the sql result has i wrote this:

DataTable dt = new DataTable();
            dt.Load(rdr);
            count = dt.Rows.Count;

之后，sqldatareader 将结果保存到数组中.

after that, the sqldatareader saves the results to arrays.

这是我的完整代码:

 public BookingUpdate[] getBookingUpdates(string token)
{
    String command = "SELECT b.ID,b.VERANSTALTER, rr.VON ,rr.BIS, b.THEMA, b.STORNO, ra.BEZEICHNUNG from BUCHUNG b JOIN RESERVIERUNGRAUM rr on rr.BUCHUNG_ID = b.ID JOIN RAUM ra on ra.ID = rr.RAUM_ID WHERE b.UPDATE_DATE BETWEEN DATEADD (DAY , -20 , getdate()) AND getdate() AND b.BOOKVERNR = 0";
    SqlConnection connection = new SqlConnection(GetConnectionString());
    BookingUpdate[] bookingupdate = new BookingUpdate[1];
    connection.Open();
    try
    {
        SqlCommand cmd = new SqlCommand(command, connection);
        SqlDataReader rdr = null;
        int count = 0;
        int c = 0;

        rdr = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(rdr);
        count = dt.Rows.Count;
        bookingupdate = new BookingUpdate[count];

        while (rdr.Read())   // <--- HERE COMES THE ERROR
        {
                bookingupdate[c] = new BookingUpdate();
                bookingupdate[c].bookingID = Convert.ToInt32(rdr["ID"]);
                bookingupdate[c].fullUserName = rdr["VERANSTALTER"].ToString();
                bookingupdate[c].newStart = (DateTime)rdr["VON"];
                bookingupdate[c].newEnd = (DateTime)rdr["BIS"];
                bookingupdate[c].newSubject = rdr["THEMA"].ToString();
                bookingupdate[c].newlocation = rdr["BEZEICHNUNG"].ToString();
                if (rdr["STORNO"].ToString() != null)
                {
                    bookingupdate[c].deleted = true;
                }
                else
                {
                    bookingupdate[c].deleted = false;
                }
                c++;

        }
    }

    catch (Exception ex)
    {
        log.Error(ex.Message + "\n\rStackTrace:\n\r" + ex.StackTrace);
    }
    finally
    {
        connection.Close();
    }
    return bookingupdate;
}

例外情况是:阅读器关闭时调用 Read 的尝试无效

the exeption is : Invalid attempt to call Read when reader is closed

推荐答案

Load-Method 关闭了 DataReader，因此对 Read() 的后续调用失败了(好吧，这正是异常告诉你的).

The Load-Method closes the DataReader, hence a following call to Read() fails (well, that's excatly what the exception tells you).

一旦您将数据读入 DataTable，您就可以简单地查询它并使用 Select 投影来创建您的 BookingUpdate 实例(没有需要 while-loop/BookingUpdate[]).所以你的代码基本上可以精简到

Once you read the data into your DataTable, you could simply query it and use a Select projection to create your BookingUpdate instances (no need for the while-loop/BookingUpdate[]). So your code can basically trimmed down to

String command = "SELECT b.ID,b.VERANSTALTER, rr.VON ,rr.BIS, b.THEMA, b.STORNO, ra.BEZEICHNUNG from BUCHUNG b JOIN RESERVIERUNGRAUM rr on rr.BUCHUNG_ID = b.ID JOIN RAUM ra on ra.ID = rr.RAUM_ID WHERE b.UPDATE_DATE BETWEEN DATEADD (DAY , -20 , getdate()) AND getdate() AND b.BOOKVERNR = 0";
SqlCommand cmd = new SqlCommand(command, new SqlConnection(GetConnectionString()));
connection.Open();

DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());

var bookingupdate = dt.Rows.OfType<DataRow>().Select (row => 
                    new BookingUpdate
                    {
                        bookingID = Convert.ToInt32(row["ID"]),
                        fullUserName = row["VERANSTALTER"].ToString(),
                        newStart = (DateTime)row["VON"],
                        newEnd = (DateTime)row["BIS"],
                        newSubject = row["THEMA"].ToString(),
                        newlocation = row["BEZEICHNUNG"].ToString(),
                        deleted = row["STORNO"].ToString() != null // note that this line makes no sense. If you can call `ToString` on an object, it is not 'null'
                    }).ToArray();

return bookingupdate;

(注意:为了可读性，我省略了 try 块)

您可能还想查看 DataRowExtensions，尤其是 Field方法，使您的代码更具可读性.

You may also want to look into the DataRowExtensions, especially the Field method, to make your code more readable.

这篇关于错误:在 while 循环后关闭 reader 时调用 Read 的尝试无效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！