C#导入Excel到SQL Server时触发主键约束冲突错误

问题详情 #

执行cmd.ExecuteNonQuery();时触发主键约束错误，错误信息如下：

Violation of PRIMARY KEY constraint 'PK_tblPurchase'. Cannot insert duplicate key in object 'dbo.tblPurchase'. The duplicate key value is ()

Excel数据能成功导入，但每次操作都会弹出该错误。完整代码如下：

public partial class FrmPurchase : Form
{
    public FrmPurchase()
    {
        InitializeComponent();
    }

    private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
    {
        DataTable dt = tableCollection[cboSheet.SelectedItem.ToString()];

        dataGridView1.DataSource = dt;
    }

    DataTableCollection tableCollection;

    private void btnImport_Click(object sender, EventArgs e)
    {
        using (OpenFileDialog openFileDialog = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx" })
        {
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                txtFilename.Text = openFileDialog.FileName;

                using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
                {
                    using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
                    {
                        DataSet result = reader.AsDataSet();
                        tableCollection = result.Tables;
                        cboSheet.Items.Clear();

                        foreach (DataTable table in tableCollection)
                            cboSheet.Items.Add(table.TableName); //add sheet to combobox
                    }
                }
            }
        }
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
        string connString = @"Data Source=LEGION-MC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True";

        using (SqlConnection con = new SqlConnection(connString))
        {
            con.Open();

            for (int i = 1; i < dataGridView1.Rows.Count; i++)
            {
                string itemCode = Convert.ToString(dataGridView1.Rows[i].Cells[0].Value);
                string itemCost = Convert.ToString(dataGridView1.Rows[i].Cells[1].Value);
                string itemSRP = Convert.ToString(dataGridView1.Rows[i].Cells[2].Value);
                string itemQty = Convert.ToString(dataGridView1.Rows[i].Cells[3].Value);
                string supplierID = Convert.ToString(dataGridView1.Rows[i].Cells[4].Value);
                string categoryID = Convert.ToString(dataGridView1.Rows[i].Cells[5].Value);
                string itemStatus = Convert.ToString(dataGridView1.Rows[i].Cells[6].Value);
                string datePurchased = Convert.ToString(dataGridView1.Rows[i].Cells[7].Value);

                using (SqlCommand cmd = new SqlCommand("INSERT INTO tblPurchase (Item_Code, Item_Cost, Item_SRP, Item_QTY, Supplier_ID, Category_ID, Item_Status, Date_Purchased) VALUES (@ItemCode, @ItemCost, @ItemSRP, @ItemQty, @SupplierID, @CategoryID, @ItemStatus, @DatePurchased)", con))
                {
                    cmd.Parameters.AddWithValue("@ItemCode", itemCode);
                    cmd.Parameters.AddWithValue("@ItemCost", itemCost);
                    cmd.Parameters.AddWithValue("@ItemSRP", itemSRP);
                    cmd.Parameters.AddWithValue("@ItemQty", itemQty);
                    cmd.Parameters.AddWithValue("@SupplierID", supplierID);
                    cmd.Parameters.AddWithValue("@CategoryID", categoryID);
                    cmd.Parameters.AddWithValue("@ItemStatus", itemStatus);
                    cmd.Parameters.AddWithValue("@DatePurchased", datePurchased);

                    cmd.ExecuteNonQuery();
                }
            }

            MessageBox.Show("Inserted successfully");
        }
    }
}

错误原因 #

1. 主键字段未被包含在插入逻辑中：tblPurchase表的主键（比如Purchase_ID）未出现在INSERT语句的字段列表里，数据库尝试插入空值作为主键。由于主键约束要求唯一且非空，重复插入空值就会触发重复键错误。
2. 循环范围包含空行：for (int i = 1; i < dataGridView1.Rows.Count; i++)会遍历到DataGridView默认添加的最后一行空行，该行所有字段值为空，插入时导致主键字段为空，触发错误。

解决办法 #

1. 修正主键字段的插入逻辑 #

• 如果主键是自增IDENTITY列：确认表设计中主键已设置为自增，此时INSERT语句无需指定主键列，数据库会自动生成主键值，避免空值问题。
• 如果主键不是自增：从Excel中读取主键值并加入插入语句，示例如下：

// 假设主键是Purchase_ID，对应Excel第8列（索引8）
string purchaseID = Convert.ToString(dataGridView1.Rows[i].Cells[8].Value);

using (SqlCommand cmd = new SqlCommand("INSERT INTO tblPurchase (Purchase_ID, Item_Code, Item_Cost, Item_SRP, Item_QTY, Supplier_ID, Category_ID, Item_Status, Date_Purchased) VALUES (@PurchaseID, @ItemCode, @ItemCost, @ItemSRP, @ItemQty, @SupplierID, @CategoryID, @ItemStatus, @DatePurchased)", con))
{
    cmd.Parameters.AddWithValue("@PurchaseID", purchaseID);
    // 其他参数绑定代码不变
}

2. 排除DataGridView的空行 #

修改循环条件，跳过最后一行默认空行：

// 减去1，排除最后一行空行
for (int i = 1; i < dataGridView1.Rows.Count - 1; i++)
{
    // 原有插入逻辑
}

3. 添加空值检查，跳过无效行 #

在插入前检查关键字段是否为空，避免插入无效数据：

for (int i = 1; i < dataGridView1.Rows.Count; i++)
{
    string itemCode = Convert.ToString(dataGridView1.Rows[i].Cells[0].Value);
    // 检查必填字段（如Item_Code或主键字段）是否为空
    if (string.IsNullOrEmpty(itemCode))
        continue; // 跳过空行
    
    // 原有插入逻辑
}

4. 使用MERGE避免重复插入（可选） #

如果需要避免重复数据，可使用MERGE语句实现"存在则更新，不存在则插入"的逻辑：

string sql = @"MERGE INTO tblPurchase AS Target
               USING (VALUES (@ItemCode, @ItemCost, @ItemSRP, @ItemQty, @SupplierID, @CategoryID, @ItemStatus, @DatePurchased)) AS Source (Item_Code, Item_Cost, Item_SRP, Item_QTY, Supplier_ID, Category_ID, Item_Status, Date_Purchased)
               ON Target.Item_Code = Source.Item_Code -- 假设Item_Code是唯一标识字段
               WHEN NOT MATCHED THEN
                   INSERT (Item_Code, Item_Cost, Item_SRP, Item_QTY, Supplier_ID, Category_ID, Item_Status, Date_Purchased)
                   VALUES (Source.Item_Code, Source.Item_Cost, Source.Item_SRP, Source.Item_QTY, Source.Supplier_ID, Source.Category_ID, Source.Item_Status, Source.Date_Purchased);";

using (SqlCommand cmd = new SqlCommand(sql, con))
{
    // 绑定参数代码不变
    cmd.ExecuteNonQuery();
}

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