使用ExecuteReader而不是SQLDataAdapter

Question

我有一个C#项目，在这个项目中，我试图导出一个数据集的结果。有时数据会变得相当大，所以我不想重新执行代码，而是将数据集转储到会话变量中。

这在我的大多数项目中都是完美的。我使用这个项目的一个例子是：

    protected void Page_Load(object sender, EventArgs e)
    {
            SqlConnection sqlconnectionStatus = new SqlConnection(str);
            string DDL_Value = Convert.ToString(Request.QueryString["DDL_Val"]);
            //Use the ClassTesting class to determine if the dates are real, and fill in today's date if they're blank
            string StDt_Value = ClassTesting.checkFields(Request.Form["txtStartDate"], "Date");
            string EnDt_Value = ClassTesting.checkFields(Request.Form["txtEndDate"], "Date");

            //string StDt_Value = Convert.ToString(Request.QueryString["StDt_Val"]);
            //string EnDt_Value = Convert.ToString(Request.QueryString["EnDt_Val"]);

            string BTN_Value;
            // Because the date is stored as an INT, you have to request the string and then
            //   convert it to an INT
            string StDT_Vals = Request.QueryString["StDt_Val"].ToString();
            string EnDT_Vals = Request.QueryString["EnDt_Val"].ToString();


                //sqlquery = "Select PROC_NM as 'Agent Name', AdminLevel as Role, Count(Claim_ID) as 'Count of Claims Reviewed', Spare as AgentID ";
                //sqlquery = sqlquery + "from ClosedClaims_MERGE CCM ";
                sqlquery = "Select PROC_NM as 'Agent Name', AdminLevel as Role, Count(DISTINCT Claim_ID) as 'Count of Claims Reviewed', Spare as AgentID ";
                sqlquery = sqlquery + "from (SELECT DISTINCT Spare, SpareFinished, CLAIM_ID FROM ClosedClaims_MERGE ";
                sqlquery = sqlquery + "UNION SELECT DISTINCT Spare, SpareFinished, CLAIM_ID FROM tblAuditing) CCM ";
                sqlquery = sqlquery + "LEFT JOIN PROC_LIST PL ON CCM.Spare = PL.LOGIN ";
                sqlquery = sqlquery + "WHERE CCM.SpareFinished >= '" + StDt_Value + "' AND CCM.SpareFinished <= '" + EnDt_Value + "' ";
                sqlquery = sqlquery + "GROUP BY Spare, PROC_NM, AdminLevel ";
                sqlquery = sqlquery + "ORDER BY Count(Claim_ID) DESC";


            SqlConnection con = new SqlConnection(str);
            SqlCommand cmd = new SqlCommand(sqlquery, con);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            // Fill the DataSet.
            DataSet ds = new DataSet();
            adapter.Fill(ds, "dsEffVol");

            // Add this to a session variable so the datagrid won't get NULLed out on repost
            Session["SSEffVol"] = ds;

            // Perform the binding.
            grdEffVol.Attributes.Add("style", "overflow:auto");
            //GridView_WODetails.Attributes.Add("style", "table-layout:fixed");

            grdEffVol.AutoGenerateColumns = true;
            grdEffVol.DataSource = ds;
            grdEffVol.DataBind();

    }

我有一个新项目，不使用SQL字符串，而是基于Server存储过程来提取数据。那里的代码块是：

    protected void btnSubmit_OnClick(object sender, EventArgs e)
    {
        List<ReportData> myReportData = new List<ReportData>();
        using (SqlConnection connection1 = new SqlConnection(str2))
        {
            //Query the Reports table to find the record associated with the selected report
            using (SqlCommand cmd = new SqlCommand("SELECT * from RM_tblManagerReports WHERE ReportID =  " + cboFilterOption.SelectedValue + "", connection1))
            {
                connection1.Open();
                using (SqlDataReader DT1 = cmd.ExecuteReader())
                {
                    while (DT1.Read())
                    {
                        //Read the record into an "array", so you can find the SProc and View names
                        int MyRptID = Convert.ToInt32(DT1[0]);
                        string MyRptName = DT1[1].ToString();
                        string MyRptSproc = DT1[2].ToString();
                        string MySQLView = DT1[3].ToString();
                        string MyUseDates = DT1[4].ToString();

                        //Run the Stored Procedure first
                        SqlConnection connection2 = new SqlConnection(str2);
                        SqlCommand cmd2 = new SqlCommand();
                        cmd2.CommandType = CommandType.StoredProcedure;
                        cmd2.CommandText = "" + MyRptSproc + "";
                        cmd2.Connection = connection2;


                        //Set up the parameters, if they exist
                        if (MyUseDates != "N")
                        {
                            cmd2.Parameters.Add("@StDate", SqlDbType.Date).Value = DateTime.Parse(txtStDate.Value);
                            cmd2.Parameters.Add("@EnDate", SqlDbType.Date).Value = DateTime.Parse(txtEnDate.Value);
                        }
                        else
                        {
                        }

                        try
                        {
                            connection2.Open();
                            GridView_Reports.EmptyDataText = "No Records Found";
                            SqlDataReader dr = cmd2.ExecuteReader(CommandBehavior.CloseConnection);
                            Session["SSRptMenu"] = dr;
                            GridView_Reports.DataSource = dr;
                            GridView_Reports.DataBind();
                            // Add this to a session variable so the datagrid won't get NULLed out on repost


                            GridView_Reports.DataBound += GridView_Reports_RowDataBound;
                        }
                        catch (Exception ex)
                        {
                            ScriptManager.RegisterStartupScript(btnSubmit, typeof(Button), "Report Menu", "alert('There is no View associated with this report.\\nPlease contact the developers and let them know of this issue.')", true);
                            Console.WriteLine(ex);
                            return;
                        }
                        finally
                        {
                            connection2.Close();
                            connection2.Dispose();
                        }

                    }
                }
            }
        }
    }

我在猜测我的方式，我不确定我是否正确地将数据读入数据集。页面正在关闭，我很确定问题就在行中：

                        SqlDataReader dr = cmd2.ExecuteReader(CommandBehavior.CloseConnection);
                        Session["SSRptMenu"] = dr;
                        GridView_Reports.DataSource = dr;

老实说，我在googled上搜索了SqlDataReader vs SqlDataAdapter，但实际上找不到任何东西，但我需要在第二个示例中填充session变量，并且还需要正确地填充datagrid。因此，本质上，我需要将存储过程的结果放入dataset中。有人能对我做错什么提出建议吗？

Answer 1

我确信大多数控件都不接受DataSource属性中的读取器。另外，大多数读取器都是前向读取器，所以尽管您试图将读取器存储为会话变量，但您很可能只能读取它一次。

当您的文章似乎表明您需要使用DataSet时，您为什么要为此使用一个阅读器？为什么不像在第一篇文章中显示的那样使用适配器呢？适配器可以很好地处理使用sprocs的命令。

而不是：

SqlDataReader dr = cmd2.ExecuteReader(CommandBehavior.CloseConnection);
Session["SSRptMenu"] = dr;
GridView_Reports.DataSource = dr;

只需使用：

var adapter = new SqlDataAdapter(cmd2);
var ds = new DataSet();
adapter.Fill(ds, "MyTableName");
Session["SSRptMenu"] = ds;
GridView_Reports.DataSource = ds;