c# - SqlDataReader 返回 x 行，但 SQL 查询返回 y 行

Question

第一次发帖，因为我有点卡在这里。

我正在使用这段代码从 SQL Server 数据库返回一些行:

public static SqlDataReader SQLSelect(string sqlcommand, string[,] parameters, int length)
{
    try
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
        conn.Open();

        SqlDataReader reader;
        SqlCommand cmd = new SqlCommand(sqlcommand, conn);

        var allLength = parameters.Length;

        for (int i = 0; i < parameters.Length - length; i++)
        {
            string paramid = parameters[i, 0];

            if (paramid == "@date" || paramid == "@Date" || paramid == "@DATE")
            {
                string paramvalue = parameters[i, 1];
                DateTime date = Convert.ToDateTime(paramvalue);
                paramvalue = date.ToString("yyyy-MM-dd HH':'mm':'ss");

                cmd.Parameters.Add(new SqlParameter(paramid, paramvalue));
            }
            else
            {
                string paramvalue = parameters[i, 1];
                cmd.Parameters.Add(new SqlParameter(paramid, paramvalue));
            }
        }

        cmd.CommandType = CommandType.StoredProcedure;

        reader = cmd.ExecuteReader();

        return reader;
    }
    catch
    {
        return null;
    }
}

这个函数是这样调用的

string[,] parameters = new string[1, 2] { { "@studentid", studentid } };
SqlDataReader reader = Common.SQLSelect(Common.tblstudentprogressselectforprinting, parameters, 1);

现在一切正常，除了读取器只包含 13 行数据，而实际查询是

exec sp_tblstudentprogress_selectforprinting @studentid=N'87'

例如，返回 91 行。

我不知道为什么会这样。我唯一注意到的是，当使用 SQL Server 探查器时，从 SQL Server 运行查询时，有一个 RPC:Started 和 Completed，至于从我的 Web 应用程序运行，只有一个 RPC:Started。

对此有什么想法吗？

编辑:

这是我列举读者的方式

        protected void btnPrint_Click(object sender, EventArgs e)
        {
            string[,] parameters = new string[1, 2] { { "@studentid", studentid } };
            SqlDataReader reader = Common.SQLSelect(Common.tblstudentprogressselectforprinting, parameters, 1);

            string firstname = txtFirstName.Text;
            string lastname = txtLastName.Text;
            int i=0;
            string[] heading1 = new string[reader.FieldCount];
            string[] heading2 = new string[reader.FieldCount];
            string[] log = new string[reader.FieldCount];

            try
            {
                while (reader.Read())
                {
                    heading1[i] = "Progress Log for: Block: " + reader["block"].ToString() + " Lesson: " + reader["lesson"].ToString();
                    heading2[i] = "";
                    log[i] =
                        /*"PROGRESS LOG for " + reader["firstname"].ToString() + " " + reader["lastname"].ToString() + " Printed on " + DateTime.Today.ToShortDateString() + Environment.NewLine +*/
                        Environment.NewLine +
                        "Teacher: " + reader["teacher"].ToString() + Environment.NewLine +
                        "Date: " + reader["date"].ToString() + Environment.NewLine +
                        "Year: " + reader["year"].ToString() + Environment.NewLine +
                        "Block: " + reader["block"].ToString() + Environment.NewLine +
                        "Lesson: " + reader["lesson"].ToString() + Environment.NewLine +
                        "Warm Up: " + reader["warmup"].ToString() + Environment.NewLine +
                        "Range: " + reader["range"].ToString() + Environment.NewLine +
                        "Technique Sheet: " + reader["techniquesheet"].ToString() + Environment.NewLine +
                        "Technique Other: " + reader["techniqueother"].ToString() + Environment.NewLine +
                        Environment.NewLine +
                        "Notes: " + reader["notes"].ToString() + Environment.NewLine +
                        Environment.NewLine +
                        "Mark: " + reader["mark"].ToString()+ Environment.NewLine ;

                    i++;
                }
            }
            catch
            {

            }
            finally
            {
                if (Common.conn != null)
                {
                    Common.conn.Close();
                }
            }


            Common.PDFCreateProgressLog("Progress log for: " + firstname + " " + lastname, "Progress log for: " + firstname + " " + lastname, "PDF_" + firstname + " " + lastname + "-" + DateTime.Today.ToString("yyyy-MM-dd") + ".pdf", "Progress log for: " + firstname + " " + lastname, log, heading1, heading2);

        }

Answer 1

您混淆了 FieldCount 属性的含义。它标识列 的数量，而不是行 的数量。如果不首先枚举所有行(无论如何至少枚举一次)，则无法确定来自 Reader 等流式源的总行数。

因此，您每次从 Reader 读取一行并测试 Reader 以在没有更多行时进行 se 时，每次都需要扩展您的数组(对于此而言，列表可能更容易)。