里面一个SQL查询执行的for循环 [英] Sql query execution inside a for loop

问题描述

for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
{
    string toemail = GridView1.Rows[i].Cells[2].Text;
    string FID1 = GridView1.Rows[i].Cells[0].Text;
    GridViewRow row = GridView1.Rows[i];
    CheckBox Ckbox = (CheckBox)row.FindControl("CheckBoxMark1");
    if (Ckbox.Checked == true)
    {
        sendMail(toemail);

        //ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Email send Succesfully')</script>");
        ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Email sent Succesfully on " + test + "')</script>");

        cn1.Open();
       //cmd4.CommandText = "Insert into TrackingFaculty_det  (EmailsentDate)  values (@EmailsentDate) WHERE FID=@FID";
       cmd4.CommandText = "update TrackingFaculty_det SET EmailsentDate=@Email WHERE FID=@FID  ";
       cmd4.CommandType = CommandType.Text;
       cmd4.Connection = cn1;

       cmd4.Parameters.Add("@Email", SqlDbType.DateTime, 8);
       cmd4.Parameters["@Email"].Value = sdt;
       cmd4.Parameters.Add("@FID",SqlDbType.VarChar,10);
       cmd4.Parameters["@FID"].Value = FID1;
       cmd4.ExecuteNonQuery();
       cn1.Close();
    }
}

//这是 C＃ code一起与 asp.net的一部分

问题出现，如果每当循环被执行选择更新应该在<$ C $完成C>数据库但是当循环执行不止一次更新的更多查询将给出一个误差变量名@Email'已声明。变量名必须是查询批处理或存储过程中是独一无二的。此。任何解决方案？

The problem arises if whenever the for loop gets executed the updation should be done in the database but when for loop executes more than once the update query will give a error as The variable name '@Email' has already been declared. Variable names must be unique within a query batch or stored procedure..Any solution for this?

推荐答案

只需推动循环外你的循环的不变部分。这意味着，在的SqlCommand，连接和参数集合的创建开幕的初始化。结果
然后，在循环中只是改变参数值和执行。 （这将总是比重新循环内的参数相同的列表更快）

Just move the invariant part of your loop outside the loop. That means, the initialization of the SqlCommand, the opening of the connection and the creation of the parameters collection.
Then inside the loop just change the value of the parameters and execute. (This will be always faster than recreating the same list of parameters inside the loop)

   SqlCommand cmd4 = new SqlCommand("update TrackingFaculty_det " + 
                                    "SET EmailsentDate=@Email WHERE FID=@FID", cn1);
   cn1.Open();
   cmd4.Parameters.Add("@Email", SqlDbType.DateTime, 8);
   cmd4.Parameters.Add("@FID",SqlDbType.VarChar,10);
   for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
   {
       .....
       if (Ckbox.Checked == true)
       {
          ....
          cmd4.Parameters["@Email"].Value = sdt;
          cmd4.Parameters["@FID"].Value = FID1;
          cmd4.ExecuteNonQuery();
       }
   }
   cn1.Close();

这篇关于里面一个SQL查询执行的for循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！