c# - 在同一代码中多次使用同一 SQLCommand 实例进行多个查询？

Question

我对使用为什么不能在同一代码中多次使用同一 SQLCommand 实例有疑问？

我尝试了这里的代码，它对 gridview 运行良好，但是当我使用 cmd.CommandText() 方法更改查询时，它一直说:

There is already an open DataReader associated with this Command which must be closed first.

这是代码:

string cs = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
SqlConnection con = new SqlConnection(cs);

try
{
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    con.Open();
    cmd.CommandText = "Select top 10 FirstName, LastName, Address, City, State from Customers";

    GridView1.DataSource = cmd.ExecuteReader(); 
    GridView1.DataBind();


    cmd.CommandText = "SELECT TOP 10 COUNT(CreditLimit) FROM Customers";
    int total = (int)cmd.ExecuteScalar();
    TotalCreditLble.Text = "The total Credit :" + total.ToString();

}
catch(Exception exp)
{
    Response.Write(exp.Message);
}
finally
{
    con.Close();
}

Answer 1

问题是您正在使用 SqlCommand 对象通过 command.ExecuteReader() 命令生成 DataReader。当它打开时，您不能重复使用该命令。

这应该有效:

using (var reader = cmd.ExecuteReader())
{
    GridView1.DataSource = reader;
    GridView1.DataBind();
}
//now the DataReader is closed/disposed and can re-use command
cmd.CommandText = "SELECT TOP 10 COUNT(CreditLimit) FROM Customers";
int total = (int)cmd.ExecuteScalar();
TotalCreditLble.Text = "The total Credit :" + total.ToString();