如何在C#中处理数据库主键冲突异常？

Hey there! Let's walk through how to fix the primary key conflict issue in your donor info insertion code, and first let's address a critical security flaw in your current implementation—string concatenation for SQL queries is a huge SQL injection risk. We'll fix that first, then tackle the primary key problem.

First: Fix the SQL Injection Vulnerability #

Your current code builds the SQL query by concatenating user input directly, which leaves your database wide open to attacks. Always use parameterized queries instead. Here's how to rewrite your command safely:

using (SqlConnection con = new SqlConnection("YourConnectionString"))
{
    con.Open();
    string insertQuery = @"INSERT INTO donorinfo 
                          (donorid, name, fathersname, age, sex, cnicno, mobileno, 
                           dateofbirth, city, address, eyecolor, bloodgroup, dateofdonation)
                          VALUES (@DonorId, @Name, @FathersName, @Age, @Sex, @CnicNo, 
                                  @MobileNo, @DateOfBirth, @City, @Address, @EyeColor, 
                                  @BloodGroup, @DateOfDonation)";

    using (SqlCommand cmd = new SqlCommand(insertQuery, con))
    {
        // Add parameters with proper types (adjust DbType based on your column types)
        cmd.Parameters.Add("@DonorId", SqlDbType.VarChar).Value = textBox1.Text;
        cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = textBox2.Text;
        cmd.Parameters.Add("@FathersName", SqlDbType.VarChar).Value = textBox3.Text;
        cmd.Parameters.Add("@Age", SqlDbType.Int).Value = int.Parse(textBox4.Text); // Validate input first!
        cmd.Parameters.Add("@Sex", SqlDbType.VarChar).Value = comboBox1.Text;
        cmd.Parameters.Add("@CnicNo", SqlDbType.VarChar).Value = textBox6.Text;
        cmd.Parameters.Add("@MobileNo", SqlDbType.VarChar).Value = textBox7.Text;
        cmd.Parameters.Add("@DateOfBirth", SqlDbType.Date).Value = DateTime.Parse(dateofbirth.Text);
        cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = textBox9.Text;
        cmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = textBox10.Text;
        cmd.Parameters.Add("@EyeColor", SqlDbType.VarChar).Value = comboBox2.Text;
        cmd.Parameters.Add("@BloodGroup", SqlDbType.VarChar).Value = comboBox3.Text;
        cmd.Parameters.Add("@DateOfDonation", SqlDbType.Date).Value = DateTime.Parse(dateofdonation.Text);

        // Conflict handling logic goes here...
    }
}

Note: Always add input validation (e.g., check if textBox4 has a valid integer) to avoid runtime format exceptions.

Solutions for Primary Key Conflicts #

Assuming donorid is your primary key, here are the most practical ways to handle duplicate entries:

1. Catch the SQL Exception and Notify the User #

SQL Server throws error code 2627 when a primary key violation occurs. You can catch this specific exception and show a user-friendly message:

try
{
    cmd.ExecuteNonQuery();
    MessageBox.Show("Donor info added successfully!");
}
catch (SqlException ex)
{
    if (ex.Number == 2627)
    {
        MessageBox.Show($"Error: A donor with ID {textBox1.Text} already exists.");
    }
    else
    {
        MessageBox.Show($"Database error: {ex.Message}");
    }
}
catch (Exception ex)
{
    MessageBox.Show($"Input error: {ex.Message}");
}

This is straightforward and works well for most cases where you just need to inform the user about duplicates.

2. Use MERGE to Upsert (Insert or Update) #

If you want to update the existing donor's info instead of throwing an error when the primary key exists, use the MERGE statement. This lets you define logic for both new and existing records:

string mergeQuery = @"MERGE INTO donorinfo AS Target
                      USING (VALUES (@DonorId, @Name, @FathersName, @Age, @Sex, @CnicNo, 
                                      @MobileNo, @DateOfBirth, @City, @Address, @EyeColor, 
                                      @BloodGroup, @DateOfDonation)) AS Source
                      (donorid, name, fathersname, age, sex, cnicno, mobileno, 
                       dateofbirth, city, address, eyecolor, bloodgroup, dateofdonation)
                      ON Target.donorid = Source.donorid
                      WHEN MATCHED THEN 
                          UPDATE SET 
                              name = Source.name,
                              fathersname = Source.fathersname,
                              age = Source.age,
                              sex = Source.sex,
                              cnicno = Source.cnicno,
                              mobileno = Source.mobileno,
                              dateofbirth = Source.dateofbirth,
                              city = Source.city,
                              address = Source.address,
                              eyecolor = Source.eyecolor,
                              bloodgroup = Source.bloodgroup,
                              dateofdonation = Source.dateofdonation
                      WHEN NOT MATCHED THEN
                          INSERT (donorid, name, fathersname, age, sex, cnicno, mobileno, 
                                  dateofbirth, city, address, eyecolor, bloodgroup, dateofdonation)
                          VALUES (Source.donorid, Source.name, Source.fathersname, Source.age, 
                                  Source.sex, Source.cnicno, Source.mobileno, Source.dateofbirth, 
                                  Source.city, Source.address, Source.eyecolor, Source.bloodgroup, 
                                  Source.dateofdonation);";

// Replace insertQuery with mergeQuery in your SqlCommand, then execute as usual

This is ideal if you want to keep donor records up-to-date when a duplicate ID is submitted.

3. Ignore Duplicates Silently (Use Sparingly) #

If you don't need to notify the user or update existing records, you can add WITH (IGNORE_DUP_KEY = ON) to your INSERT statement to skip duplicates without throwing an error:

string insertQuery = @"INSERT INTO donorinfo WITH (IGNORE_DUP_KEY = ON)
                      (donorid, name, fathersname, age, sex, cnicno, mobileno, 
                       dateofbirth, city, address, eyecolor, bloodgroup, dateofdonation)
                      VALUES (@DonorId, @Name, @FathersName, @Age, @Sex, @CnicNo, 
                              @MobileNo, @DateOfBirth, @City, @Address, @EyeColor, 
                              @BloodGroup, @DateOfDonation)";

Use this only if silent failure is acceptable—users won't get any feedback that their entry already exists.

Key Takeaways #

• Never use string concatenation for SQL queries—always use parameterized queries to prevent SQL injection.
• Pick the conflict handling strategy based on your needs: notify the user, update existing data, or ignore duplicates.
• Always validate user input to avoid parsing errors (e.g., check if age is a valid integer before converting).

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