c# - 在 for 循环中使用 INSERT 语句仅接受第一条记录

Question

我尝试了此操作，但 71 条记录中仅插入了第一条记录，其他记录未插入。在第二个循环中插入时发生错误。

for (int i = 0; i <= dtRaffleEntries.Rows.Count - 1; i++)
{
   for (int c = 0; c <= Convert.ToInt32(dtRaffleEntries.Rows[i]["RaffleEntries"].ToString()) - 1; c++)
   {
                        errorline = dtRaffleEntries.Rows[i]["ORNumber"].ToString(); //FLR

                        sbRaffleEntries.Append("INSERT INTO `tblraffleentry` ");
                        sbRaffleEntries.Append("(");
                        sbRaffleEntries.Append("`PromoCode`,`TransactionAmount`,");
                        sbRaffleEntries.Append("`TransactionDate`,`TransactionTime`,");
                        sbRaffleEntries.Append("`CardName`,`CardNumber`,");
                        sbRaffleEntries.Append("`BUCode`,`StationName`,");
                        sbRaffleEntries.Append("`MerchantID`,`TerminalID`,");
                        sbRaffleEntries.Append("`ORNumber`,`Remarks`,");
                        sbRaffleEntries.Append("`CreatedBy`,`DateCreated`,");
                        sbRaffleEntries.Append("`DateUpdated`,`filename`");
                        sbRaffleEntries.Append(")");
                        sbRaffleEntries.Append(" VALUES");
                        sbRaffleEntries.Append("(?,?,CONVERT(?,DATETIME),CONVERT(?,DATETIME),?,?,?,?,?,?,?,?,?,CONVERT(?,DATETIME),CONVERT(?,DATETIME),?) ");

                        insertRaffleEntries.Parameters.AddWithValue("PromoCode", RafflePromoCode);
                        insertRaffleEntries.Parameters.AddWithValue("TransactionAmount", dtRaffleEntries.Rows[i]["TransactionAmount"].ToString());
                        insertRaffleEntries.Parameters.AddWithValue("TransactionDate", Convert.ToDateTime(dtRaffleEntries.Rows[i]["TransactionDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
                        insertRaffleEntries.Parameters.AddWithValue("TransactionTime", Convert.ToDateTime(dtRaffleEntries.Rows[i]["TransactionTime"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
                        insertRaffleEntries.Parameters.AddWithValue("CardName", dtRaffleEntries.Rows[i]["CardName"].ToString());
                        insertRaffleEntries.Parameters.AddWithValue("CardNumber", dtRaffleEntries.Rows[i]["CardNumber"].ToString());
                        insertRaffleEntries.Parameters.AddWithValue("BUCode", dtRaffleEntries.Rows[i]["BUCode"].ToString());
                        insertRaffleEntries.Parameters.AddWithValue("StationName", dtRaffleEntries.Rows[i]["StationName"].ToString());
                        insertRaffleEntries.Parameters.AddWithValue("MerchantID", dtRaffleEntries.Rows[i]["MerchantID"].ToString());
                        insertRaffleEntries.Parameters.AddWithValue("TerminalID", dtRaffleEntries.Rows[i]["TerminalID"].ToString());
                        insertRaffleEntries.Parameters.AddWithValue("ORNumber", dtRaffleEntries.Rows[i]["ORNumber"].ToString());
                        insertRaffleEntries.Parameters.AddWithValue("Remarks", " ");
                        insertRaffleEntries.Parameters.AddWithValue("CreatedBy", UserName.ToString());
                        insertRaffleEntries.Parameters.AddWithValue("DateCreated", dateTimeNow.ToString("yyyy-MM-dd HH:mm:ss"));
                        insertRaffleEntries.Parameters.AddWithValue("DateUpdated", dateTimeNow.ToString("yyyy-MM-dd HH:mm:ss"));
                        insertRaffleEntries.Parameters.AddWithValue("filename", filename);

                        insertRaffleEntries.CommandText = sbRaffleEntries.ToString();
                        insertRaffleEntries.ExecuteNonQuery();

                        count++
                         }
                    count2++;
                    backgroundWorker1.ReportProgress(((int)((double)(i + 1) / dtRaffleEntries.Rows.Count * 100)), CompleteText);
                }

错误表明它是 mysql 语法，但我检查它是否正确并成功执行第一个循环。在第二个循环中出现错误，依此类推。

Answer 1

在内部for之后添加此行

....
for (int c = 0; c <= Convert.ToInt32(dtRaffleEntries.Rows[i]["RaffleEntries"].ToString()) - 1; c++)
{
    insertRaffleEntries.Parameters.Clear();
    .....

在第一个循环之后，您再次添加相同的参数和相同的命令文本，以复制所有内容。这会导致一条异常消息，指出参数过多和语法无效。一种更好且性能稍高的方法应该是在循环外定义一次参数和文本，并仅在循环内设置值

例如

string cmdText = @"INSERT INTO `tblraffleentry` 
`PromoCode`,`TransactionAmount`,`TransactionDate`,`TransactionTime`,
`CardName`,`CardNumber`,`BUCode`,`StationName`,`MerchantID`,`TerminalID`,
`ORNumber`,`Remarks`,`CreatedBy`,`DateCreated`,`DateUpdated`,`filename`)
 VALUES
 (?,?,CONVERT(?,DATETIME),CONVERT(?,DATETIME),?,?,?,?,?,?,?,?,?,
 CONVERT(?,DATETIME),CONVERT(?,DATETIME),?)";
insertRaffleEntries.CommandText = cmdText;
insertRaffleEntries.Parameters.Add("PromoCode", MySqlDbType.VarChar);
// Define the other parameters with the proper DBType here 
.....

for (int i = 0; i <= dtRaffleEntries.Rows.Count - 1; i++)
{
   for (int c = 0; c <= Convert.ToInt32(dtRaffleEntries.Rows[i]["RaffleEntries"].ToString()) - 1; c++)
   {
        errorline = dtRaffleEntries.Rows[i]["ORNumber"].ToString(); //FLR
        insertRaffleEntries["PromoCode"].Value =  RafflePromoCode;
        // Set the value for the other parameter here 
        .....
        insertRaffleEntries.ExecuteNonQuery();
   }
}