使用 ExecuteReader 而不是 SQLDataAdapter
本文关键字：SQLDataAdapter ExecuteReader 使用 | 更新日期: 2023-09-27 17:55:25
我有一个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 字符串，而是基于 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;
老实说，我已经用谷歌搜索了SqlDataReader vs SqlDataAdapter，但实际上找不到任何东西，但是我需要在第二个示例中填充会话变量，并正确填充数据网格。 因此，从本质上讲，我需要将存储过程的结果放入数据集中。 任何人都可以就我做错了什么提供建议吗？
我很确定大多数控件在其数据源属性中不接受读取器。此外，大多数阅读器都是只进的，因此尽管您尝试将读取器存储为会话变量，但您可能只能读取一次。
当您的帖子似乎表明您知道需要使用 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;