Winform应用SQL Server数据插入失败求助

我开发了一个基于SQL Server数据库的WinForms应用，遇到的问题是代码执行成功后，数据并未插入到Production_Master表中。已确认连接字符串正确，代码逻辑看似无误，但数据仍无法插入。以下是使用的代码及日志信息，恳请帮忙排查：

代码实现 #

private void StoreData()
{
    try
    {
        // Log connection string details (mask sensitive information if necessary)
        LogInformation($"Attempting to open connection with connection string: {connectionString}");

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Log which database and server the connection is using
            LogInformation($"Connected to database: {connection.Database} on server: {connection.DataSource} with connection string: {connection.ConnectionString}");

            string query = "INSERT INTO Production_Master (PlanId, ModelId, PartId, Part_Sr_No, SerialId, [User], Date_Time) " +
                            "VALUES (@PlanId, @ModelId, @PartId, @Part_Sr_No, @SerialId, @User, @Date_Time)";

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                // Log which table is being accessed
                LogInformation("Accessing table: Production_Master");

                // Define constant values outside the loop
                command.Parameters.Add("@PlanId", SqlDbType.Int).Value = _PlanId;
                command.Parameters.Add("@ModelId", SqlDbType.Int).Value = _MId;
                command.Parameters.Add("@SerialId", SqlDbType.NVarChar, 50).Value = P_T_QR_DATA;
                command.Parameters.Add("@User", SqlDbType.Int).Value = 0; // Assuming this is a constant value

                for (int i = 0; i < partId.Count; i++)
                {
                    // Remove previously added parameters
                    command.Parameters.Remove("@PartId");
                    command.Parameters.Remove("@Part_Sr_No");
                    command.Parameters.Remove("@Date_Time");

                    // Set parameters that change per iteration
                    command.Parameters.Add("@PartId", SqlDbType.Int).Value = partId[i];
                    command.Parameters.Add("@Part_Sr_No", SqlDbType.NVarChar, 50).Value = Station3Part[i];
                    command.Parameters.Add("@Date_Time", SqlDbType.DateTime).Value = DateTime.Now;

                    // Log the parameters being inserted
                    LogInformation($"Inserting data: PlanId={_PlanId}, ModelId={_MId}, PartId={partId[i]}, Part_Sr_No={Station3Part[i]}, SerialId={P_T_QR_DATA}, User=0, Date_Time={DateTime.Now}");

                    int rowsAffected = command.ExecuteNonQuery();

                    if (rowsAffected > 0)
                    {
                        richTextBox1.AppendText($"Data for Part {partId[i]} stored successfully.\n");
                        // Log success message
                        LogInformation($"Data for PartId={partId[i]} stored successfully.");
                    }
                    else
                    {
                        // Handle potential insertion failure for each iteration
                        // Log an error message or display a specific message for the failed part
                        LogError($"Failed to store data for PartId={partId[i]}.");
                    }
                }
            }
        }

        // Clear variables after storing data
        tankSR = "";
        Station3Part.Clear();
        station3Tank = "";

        // Log clearing of variables
        LogInformation("Cleared variables after storing data.");
    }
    catch (SqlException ex)
    {
        LogException(ex);
    }
}

日志信息 #

2024-05-24 15:37:27 [INFO] Connected to database: CKDDB on server: 10.12.101.1 with connection string: Data Source=10.12.101.1;User ID=sa;Initial Catalog=CKDDB;Integrated Security=False;
2024-05-24 15:37:27 [INFO] Accessing table: Production_Master
2024-05-24 15:37:27 [INFO] Cleared variables after storing data.
2024-05-24 15:41:14 [INFO] Attempting to open connection with connection string: Data Source=10.12.101.1;User ID=sa;Password=Bal@1234;Initial Catalog=CKDDB;Integrated Security=False;

排查建议 #

• 循环未执行：从日志看，完全没有循环内的插入日志输出，说明partId.Count大概率为0，循环根本没运行。先检查partId集合是否正确填充了数据。
• 参数操作逻辑优化：循环内反复移除再添加参数容易引发隐性错误（比如参数未找到的异常被吞），建议改为循环外一次性定义所有参数，循环内仅更新值：

  // 循环外定义所有参数
  command.Parameters.Add("@PlanId", SqlDbType.Int).Value = _PlanId;
  command.Parameters.Add("@ModelId", SqlDbType.Int).Value = _MId;
  command.Parameters.Add("@SerialId", SqlDbType.NVarChar, 50).Value = P_T_QR_DATA;
  command.Parameters.Add("@User", SqlDbType.Int).Value = 0;
  // 初始化可变参数
  command.Parameters.Add("@PartId", SqlDbType.Int);
  command.Parameters.Add("@Part_Sr_No", SqlDbType.NVarChar, 50);
  command.Parameters.Add("@Date_Time", SqlDbType.DateTime);
  
  for (int i = 0; i < partId.Count; i++)
  {
      // 直接更新参数值
      command.Parameters["@PartId"].Value = partId[i];
      command.Parameters["@Part_Sr_No"].Value = Station3Part[i];
      command.Parameters["@Date_Time"].Value = DateTime.Now;
  
      // 后续日志、执行逻辑不变
  }
  

• 显式事务控制：虽然默认是自动提交，但存在隐性事务或数据库配置问题时可能导致数据未持久化，可添加显式事务：

  using (SqlConnection connection = new SqlConnection(connectionString))
  {
      connection.Open();
      using (SqlTransaction transaction = connection.BeginTransaction())
      {
          using (SqlCommand command = new SqlCommand(query, connection, transaction))
          {
              // 原有代码逻辑...
              // 循环结束后提交事务
              transaction.Commit();
          }
      }
  }
  

• 权限验证：确认sa账号拥有Production_Master表的INSERT权限，部分场景下SQL Server不会抛出权限不足异常，仅返回0行影响。
• 表/字段名校验：确认数据库中Production_Master表存在，且字段名（如[User]、Date_Time）完全匹配，注意数据库是否开启大小写敏感。
• 异常捕获范围扩展：当前仅捕获SqlException，建议改为捕获Exception并记录，排查是否有非SQL类异常导致循环提前终止。

内容的提问来源于stack exchange，提问作者Ganesh