none
如何在C#中使用FOR语句执行存储过程 RRS feed

  • 问题

  • 我在C#中写了一段INSERT代码,请教如何将这段INSRT代码改为FOR循环并调用存储过程代码?

            private void OutStoreInsert()
            {
                if (dgvOutStoreListA.RowCount > 0)
                {
                    SqlCommand cmd = new SqlCommand("", Sqlstr.GetCon());
                    try
                    {
                        for (int i = 0; i < dgvOutStoreListA.RowCount; i++)
                        {
                            string Sql = "INSERT INTO tb_OutStore(OSID,OutID,InteriorID,Material_ID,Material_Name,Material_Units,";
                            Sql += "Material_Number,Material_Price,Material_Sum,OSRemark,OSDate,PGProvider,PGPeople,HandlePeople)";
                            Sql += "VALUES('" + lbOSIDA.Text.Trim() + "','" + txtOutID.Text.Trim() + "',";
                            Sql += "'" + dgvOutStoreListA.Rows[i].Cells[0].Value.ToString().Trim() + "',";
                            Sql += "'" + dgvOutStoreListA.Rows[i].Cells[1].Value.ToString().Trim() + "',";
                            Sql += "'" + dgvOutStoreListA.Rows[i].Cells[2].Value.ToString().Trim() + "',";
                            Sql += "'" + dgvOutStoreListA.Rows[i].Cells[3].Value.ToString().Trim() + "',";
                            Sql += "'" + Convert.ToDecimal(dgvOutStoreListA.Rows[i].Cells[4].Value.ToString().Trim()) + "',";
                            Sql += "'" + Convert.ToDecimal(dgvOutStoreListA.Rows[i].Cells[5].Value.ToString().Trim()) + "',";
                            Sql += "'" + Convert.ToDecimal(dgvOutStoreListA.Rows[i].Cells[6].Value.ToString().Trim()) + "',";
                            Sql += "'" + dgvOutStoreListA.Rows[i].Cells[7].Value.ToString() + "',";
                            Sql += "'" + Convert.ToDateTime(dtpOSDateA.Value.ToShortDateString()) + "',";
                            Sql += "'" + txtPGProvider.Text.Trim() + "',";
                            Sql += "'" + txtPGPeople.Text.Trim() + "',";
                            Sql += "'" + txtHandlePeople.Text.Trim() + "')";
                            cmd.CommandText = Sql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    catch (SqlException Ex)
                    {
                        MessageBox.Show("数据提交失败!" + Ex.Message, "警告提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    finally
                    {
                        cmd.Dispose();
                        Sqlstr.GetClose();
                    }
                }
            }
    


    Raymond

    2012年11月26日 9:59

答案

  • 假设你的存储过程如下(我只是一个演示,具体情况可自行修改):

    Create procdure MyProc
    (
      @osid int,
      @OutId int,
      ………………
      @HandlePeople varchar(N)
    )
    As
     begin
       INSERT INTO tb_OutStore(OSID,OutID,InteriorID,Material_ID,Material_Name,Material_Units,Material_Number,Material_Price,Material_Sum,OSRemark,OSDate,PGProvider,PGPeople,HandlePeople)values(@osid,@outId,……,@HandlePeople)
     end

    运行生成存储过程之后,这样做:

    private void OutStoreInsert()
            {
                if (dgvOutStoreListA.RowCount > 0)
                {
                    SqlCommand cmd = new SqlCommand("", Sqlstr.GetCon());
                    try
                    {
    cmd.CommandText= "MyProc";                        cmd.CommandType =CommandType.StoredProcdure;
                      
    for (int i = 0; i < dgvOutStoreListA.RowCount; i++)
                        {
    cmd.Parameters.Clear();
                                      cmd.Parameters.AddWithValue("@osid",your value here);
                            …………
    cmd.Parameters.AddWithValue("@HandlePeople",your value here);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    catch (SqlException Ex)
                    {
                        MessageBox.Show("数据提交失败!" + Ex.Message, "警告提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    finally
                    {
                        cmd.Dispose();
                        Sqlstr.GetClose();
                    }
                }
            }

    我的博客园
    慈善点击,点击此处
    和谐拯救危机,全集下载,净化人心


    2012年11月27日 1:36
    版主