WinForms中DataGridView单元格变更同步至数据库的事件选择及问题排查

Hey Ivan, let's break down the issues you're facing step by step—there are a few critical problems in both your event handling approach and SQL code that's causing things to fail. Let's fix this together:

First: Why Your Events Aren't Working #

The CellEndEdit, CellValueChanged, or CellLeave events might not be triggering as expected for two main reasons:

• You're not using a proper data binding layer: If you're directly binding the database table to the DataGridView without a BindingSource or DataTable, the grid's changes won't sync reliably with your data source, and events may fire at the wrong time (e.g., before a new row is fully committed).
• Event trigger timing: CellLeave only fires when the cell loses focus to another control, not when moving to another cell in the same grid. CellValueChanged fires on every keystroke (not when the edit is fully done), and CellEndEdit might not catch new rows until the row is validated.

A better choice is the RowValidated event—it triggers after an entire row's edits are complete and validated, making it perfect for submitting row-level changes to the database.

Second: Fix Your Broken SQL Code #

Your current INSERT/UPDATE code has obvious bugs that would fail even if the events worked:

1. INSERT Bug: You're using dataGridView1.SelectedCells[0].Value for both PersonName and City—you're inserting the same value into both columns! You need to target the specific cells for each column in the current row.
2. UPDATE Bugs:
   • You're using string concatenation for SQL (risk of SQL injection and syntax errors)
   • There's an extra comma before WHERE (invalid SQL syntax)
   • dataGridView1.SelectedRows[0] doesn't get the ID value—it returns the entire row object, not the cell content
   • Again, you're using the same selected cell value for both PersonName and City
3. Connection Leaks: You're not properly disposing of database connections/commands—always use using statements to ensure resources are cleaned up.

Working Implementation #

Let's rebuild this correctly with proper binding and safe SQL:

Step 1: Set Up Proper Data Binding

First, use a BindingSource and DataTable to link your database to the DataGridView. This ensures changes sync reliably:

private DataTable _infoTable;
private BindingSource _infoBindingSource;
private readonly string _connString = "Your_Connection_String_Here"; // Replace with your actual connection string

private void Form_Load(object sender, EventArgs e)
{
    // Load data from the database into a DataTable
    _infoTable = new DataTable();
    using (var conn = new SqlConnection(_connString))
    using (var adapter = new SqlDataAdapter("SELECT ID, PersonName, City FROM Info", conn))
    {
        adapter.Fill(_infoTable);
    }

    // Bind the DataTable to a BindingSource, then to the DataGridView
    _infoBindingSource = new BindingSource();
    _infoBindingSource.DataSource = _infoTable;
    dataGridView1.DataSource = _infoBindingSource;

    // Attach the RowValidated event to handle row edits/inserts
    dataGridView1.RowValidated += DataGridView1_RowValidated;
}

Step 2: Handle Row Validations & Database Updates

This event will trigger when a row's edits are done, and we'll safely insert/update the database:

private void DataGridView1_RowValidated(object sender, DataGridViewCellEventArgs e)
{
    var row = dataGridView1.Rows[e.RowIndex];
    // Skip the temporary "new row" placeholder
    if (row.IsNewRow) return;

    // Extract values from the current row (handle nulls safely)
    var idValue = row.Cells["ID"].Value;
    var personName = row.Cells["PersonName"].Value?.ToString() ?? string.Empty;
    var city = row.Cells["City"].Value?.ToString() ?? string.Empty;

    try
    {
        using (var conn = new SqlConnection(_connString))
        {
            conn.Open();

            // Determine if this is an insert (ID is null/0, assuming ID is auto-increment)
            if (idValue == DBNull.Value || Convert.ToInt32(idValue) == 0)
            {
                // Insert new row with parameterized SQL
                using (var cmd = new SqlCommand(
                    "INSERT INTO Info(PersonName, City) VALUES (@personName, @city); SELECT SCOPE_IDENTITY();", 
                    conn))
                {
                    cmd.Parameters.AddWithValue("@personName", personName);
                    cmd.Parameters.AddWithValue("@city", city);

                    // Get the auto-generated ID and update the grid row
                    var newId = Convert.ToInt32(cmd.ExecuteScalar());
                    row.Cells["ID"].Value = newId;
                }
            }
            else
            {
                // Update existing row with parameterized SQL
                using (var cmd = new SqlCommand(
                    "UPDATE Info SET PersonName = @personName, City = @city WHERE ID = @id", 
                    conn))
                {
                    cmd.Parameters.AddWithValue("@personName", personName);
                    cmd.Parameters.AddWithValue("@city", city);
                    cmd.Parameters.AddWithValue("@id", idValue);

                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show($"Failed to save changes: {ex.Message}");
        // Roll back the row's edits if something goes wrong
        row.CancelEdit();
    }
}

Key Improvements #

• Parameterized SQL: Eliminates SQL injection risks and avoids syntax errors from special characters
• Proper Resource Management: using statements ensure connections/commands are disposed correctly
• Row-Level Handling: RowValidated ensures we only submit changes once per row edit
• Auto-Increment ID Handling: Uses SCOPE_IDENTITY() to get the new row's ID without reloading the entire table
• Null Safety: Handles cases where cell values might be null

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