gpt4 book ai didi

c# - 必须关闭 asp.net datareader

转载 作者:行者123 更新时间:2023-12-03 20:50:53 28 4
gpt4 key购买 nike

我的代码:

SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
//..........
cmd.CommandText = "SELECT * FROM TempQn WHERE creatorId= '" +
Session["administratorID"].ToString() + "'";
dr = cmd.ExecuteReader();
while (dr.Read())
{
int ids = Int32.Parse(dr["QuestionID"].ToString());
cmd.CommandText = " INSERT INTO Answers (QuestionId,Answer) Select c.QnId, c.Answer From TempAns c Where c.Id = " + ids + " ";
cmd.ExecuteNonQuery(); //this line
}
dr.Close();

错误是:

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

什么样的命令应该替换 cmd.ExecuteNonQuery();

最佳答案

只要 DataReader 处于“事件状态”,您就不能执行任何进一步的 SQL 语句。

为了克服这个问题,存储 SQL 语句的列表,然后在阅读后执行它们:

cmd.CommandText = "SELECT * FROM Question WHERE SurveyID= '" + sID + "'";    
dr = cmd.ExecuteReader();
List<string> arrSQL = new List<string>();
while (dr.Read())
{
int ids = Int32.Parse(dr["QuestionID"].ToString());
arrSQL.Add("INSERT INTO Answers (QuestionId,Answer) Select c.QnId, c.Answer From TempAns c Where c.Id = " + ids + " ");
}
dr.Close();

arrSQL.ForEach(strSQL =>
{
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
});

您当前的代码很容易受到 SQL 注入(inject)攻击,这不是好的做法 - 您最好使用参数而不是向原始 SQL 注入(inject)值 - 以下是如何实现这一点:

cmd.CommandText = "SELECT * FROM Question WHERE SurveyID=@id";
cmd.Parameters.AddWithValue("@id", sID);
dr = cmd.ExecuteReader();
List<int> arrQuestions = new List<int>();
while (dr.Read())
{
int ids = Int32.Parse(dr["QuestionID"].ToString());
arrQuestions.Add(ids);
}
dr.Close();

cmd.CommandText = "INSERT INTO Answers (QuestionId, Answer) Select c.QnId, c.Answer From TempAns c Where c.Id = @id";
arrQuestions.ForEach(id =>
{
cmd.Parameters["@id"].Value = id;
cmd.ExecuteNonQuery();
});

关于c# - 必须关闭 asp.net datareader,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8501965/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com