如何在Windows窗体应用中移除Excel导入DataGridView的重复值并保存至SQL数据库

Got it, let's walk through how to meet your requirement: importing Excel data into a WinForms DataGridView, removing duplicate entries, then efficiently saving the cleaned data to your SQL Server empl table. We'll enhance your existing code to fix inefficiencies and add the deduplication step.

1. Deduplicate Data Before Inserting #

First, after loading Excel data into your DataGridView (or directly into a DataTable), you need to remove duplicates. The easiest way is to use the DataTable.DefaultView.ToTable() method, which lets you specify unique columns to filter duplicates.

Example: Deduplicate after loading Excel data #

Suppose you have a DataTable named excelData that holds the imported Excel data. Add this code right after loading the data:

// Deduplicate based on e_id (adjust columns as needed for your uniqueness rule)
DataTable deduplicatedData = excelData.DefaultView.ToTable(true, "e_id", "f_name", "l_name", "address");
// Bind the cleaned data to DataGridView
dataGridView1.DataSource = deduplicatedData;

• The true parameter tells it to keep only unique rows.
• The list of column names defines which columns are used to determine duplicates (e.g., if e_id is a unique identifier, using just that will remove rows with the same ID).

2. Optimize Bulk Insert to SQL Server #

Your original loop executes a separate INSERT for each row, which is slow for large datasets. We'll use parameterized queries with batch execution (or SqlBulkCopy for even larger data) and ensure proper resource disposal with using statements (to avoid connection leaks).

Improved button1_Click Event Code #

private void button1_Click(object sender, EventArgs e)
{
    // Get the deduplicated data from DataGridView (cast back to DataTable)
    DataTable dt = (DataTable)dataGridView1.DataSource;
    
    // Use using statements to auto-dispose connections/commands
    using (SqlConnection con = new SqlConnection("Your_Connection_String_Here"))
    {
        con.Open();
        // Parameterized INSERT query
        string insertQuery = "INSERT INTO empl (e_id, f_name, l_name, address) VALUES (@e_id, @f_name, @l_name, @address)";
        
        using (SqlCommand cmd = new SqlCommand(insertQuery, con))
        {
            // Add parameters once (reuse them for each row)
            cmd.Parameters.Add("@e_id", SqlDbType.VarChar); // Adjust SqlDbType to match your column type
            cmd.Parameters.Add("@f_name", SqlDbType.VarChar);
            cmd.Parameters.Add("@l_name", SqlDbType.VarChar);
            cmd.Parameters.Add("@address", SqlDbType.VarChar);
            
            // Loop through rows and execute batch inserts
            foreach (DataRow row in dt.Rows)
            {
                cmd.Parameters["@e_id"].Value = row["e_id"];
                cmd.Parameters["@f_name"].Value = row["f_name"];
                cmd.Parameters["@l_name"].Value = row["l_name"];
                cmd.Parameters["@address"].Value = row["address"];
                
                cmd.ExecuteNonQuery();
            }
        }
        
        MessageBox.Show("Cleaned Excel data uploaded to database successfully!");
        
        // Refresh DataGridView with latest data from SQL
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM empl", con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                DataTable refreshedDt = new DataTable();
                sda.Fill(refreshedDt);
                dataGridView1.DataSource = refreshedDt;
            }
        }
    }
}

Key Improvements: #

• using Statements: Automatically dispose of connections, commands, and adapters to prevent resource leaks (no need to manually call Dispose() or Close()).
• Reusable Parameters: Add parameters once and update their values for each row, which is more efficient than adding/clearing parameters every time.
• Explicit SqlDbType: Specify the correct SQL data type for each parameter to avoid implicit conversion issues.
• Deduplication Integration: Uses the cleaned DataTable directly from the DataGridView.

Bonus: Even Faster Bulk Insert with SqlBulkCopy #

If you're dealing with very large datasets (thousands of rows), SqlBulkCopy is way faster than row-by-row inserts. Here's how to replace the loop with it:

// Inside the using (SqlConnection con) block:
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
{
    bulkCopy.DestinationTableName = "empl";
    // Map DataTable columns to SQL table columns (if names match, this is optional)
    bulkCopy.ColumnMappings.Add("e_id", "e_id");
    bulkCopy.ColumnMappings.Add("f_name", "f_name");
    bulkCopy.ColumnMappings.Add("l_name", "l_name");
    bulkCopy.ColumnMappings.Add("address", "address");
    
    bulkCopy.WriteToServer(dt);
}

Notes: #

• Replace Your_Connection_String_Here with your actual SQL Server connection string.
• Adjust SqlDbType values to match the data types of your empl table columns (e.g., SqlDbType.Int if e_id is an integer).
• If your duplicate rule is based on multiple columns (e.g., combination of f_name, l_name, and address), update the ToTable() method's column list accordingly.

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