C# WinForms中如何将绑定DataTable的DataGridView数据保存至MSSQL数据库

Hey there! Let's work through this step by step—since you're using a BindingSource and TableAdapter, the solution is indeed straightforward once we fix a couple of potential gaps, especially with that unbound delete column you mentioned.

First, let's tackle the delete button issue because that's likely one of the reasons your save isn't picking up deletions. When you click your "Ištrinti" button, if you're just removing the row from the DataGridView directly (like advancedDataGridView1.Rows.RemoveAt(...)), the underlying DataTable doesn't mark that row as deleted—it just removes it from the view. The TableAdapter needs those rows to be marked as deleted in the DataTable to send the DELETE command to SQL.

So here's how to handle the delete button click correctly:

private void advancedDataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
    // Replace "yourDeleteColumnIndex" with the actual index of your unbound delete column
    if (e.ColumnIndex == yourDeleteColumnIndex && e.RowIndex >= 0)
    {
        // Get the corresponding DataRowView from the BindingSource
        DataRowView rowView = produktaiBindingSource[e.RowIndex] as DataRowView;
        
        if (rowView != null)
        {
            // Mark the row as deleted in the DataTable
            rowView.Row.Delete();
            // The BindingSource will automatically update the DGV view—no need to remove rows manually
        }
    }
}

This ensures the row is flagged as deleted in veiklosDuomenysDataSet.Produktai, so the TableAdapter recognizes it when you call Update().

Next, let's refine your save button code. Your initial attempt was close, but let's add critical steps to commit edits and validate data:

private void btnSave_Click(object sender, EventArgs e)
{
    try
    {
        // Commit any in-progress cell edits in the DGV first
        advancedDataGridView1.EndEdit();
        // Push changes from the BindingSource to the underlying DataTable
        produktaiBindingSource.EndEdit();
        
        // Check for data validation errors (optional but helpful)
        if (veiklosDuomenysDataSet.HasErrors)
        {
            MessageBox.Show("There are invalid entries in the data. Please fix them before saving.");
            return;
        }
        
        // Update the database with all pending changes (inserts, updates, deletes)
        int rowsAffected = produktaiTableAdapter.Update(veiklosDuomenysDataSet.Produktai);
        
        // Reset the DataTable's change tracking state after successful save
        veiklosDuomenysDataSet.Produktai.AcceptChanges();
        
        MessageBox.Show($"Successfully saved {rowsAffected} rows!");
    }
    catch (Exception ex)
    {
        MessageBox.Show($"Error saving data: {ex.Message}");
    }
}

Key notes here:

• advancedDataGridView1.EndEdit() ensures any cell you're currently editing is saved to the BindingSource before proceeding.
• The Update() method returns the number of rows modified in the database—this is a quick way to confirm changes were applied.

Now, let's check if your TableAdapter has the necessary commands. If your SQL table doesn't have a primary key, the TableAdapter wizard won't generate Insert/Update/Delete commands automatically. Here's how to verify:

1. Open your DataSet (.xsd file) in Visual Studio.
2. Right-click on produktaiTableAdapter and select "Configure".
3. Ensure the "Generate Insert, Update, and Delete statements" option is checked.
4. If no primary key exists for your Produktai table, add one in SQL Server first—this is mandatory for the TableAdapter to track row changes correctly.

Regarding your question about DataTable Produktai = advancedDataGridView1.DataSource as DataTable;—you don't need this! Your DGV is bound to produktaiBindingSource, which is already linked to veiklosDuomenysDataSet.Produktai. All changes in the DGV flow through the BindingSource to the DataTable automatically (as long as edits are committed properly).

Troubleshooting steps if it still doesn't work:

• Check if veiklosDuomenysDataSet.Produktai.GetChanges() returns anything. If it's null, there are no pending changes to save.
• Confirm your filter (produktaiBindingSource.Filter = ...) isn't hiding changed rows—filters only affect the DGV view, not the underlying DataTable, but it's worth verifying.
• Make sure your SQL user account has Insert/Update/Delete permissions on the Produktai table.

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