Skip to main content
 首页 » 编程设计

c#之SqlDataReader 返回 x 行,但 SQL 查询返回 y 行

2024年08月12日51duanxz

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

我正在使用这段代码从 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); 
 
        } 

请您参考如下方法:

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

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