c# - 如何在 C# 上从 DataGrid 更新 MySql 数据库？

Question

我正在尝试从 DataGrid 更新 MySQL DB，但它不起作用。添加的数据不在数据库中，也没有错误，为什么会出现这种情况。我已经尝试解决这个问题好几天了。

private void dtGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
        {    
        MySqlConnection conn = DBUtils.GetDBConnection();
        string table = "brands";
        string sql = "SELECT * FROM "+table;
        MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(sql, conn);
        conn.Open();
        MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(myDataAdapter);
        myDataAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
        myDataAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
        myDataAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();
        DataSet myDataSet = new DataSet();
        myDataAdapter.Fill(myDataSet, table);
        myDataAdapter.AcceptChangesDuringUpdate = true;
        myDataAdapter.Update(myDataSet, table);
        conn.Close();
    }

Answer 1

@尤金

所以这里:

DataTable dt = new DataTable();
DataSet ds = new DataSet();
dt = ((DataView)dtGrid.ItemsSource).ToTable() as DataTable;
ds.Tables.Add(dt);

然后再次填充

myDataAdapter.Fill(ds, table);

您正在创建一个新数据表，然后向其中添加一个现有数据表。这就是为什么所有行都被再次添加的原因。

尝试将您的代码更改为。 XAML:

<Grid>
    <TabControl Height="310" HorizontalAlignment="Left" Margin="-1,-1,0,0" Name="tabControl1" VerticalAlignment="Top" Width="763">
        <TabItem Header="Brands" Name="tabBrands">
            <DataGrid ItemsSource="{Binding}" Name="dtGrid" Height="280" Width="750" BeginningEdit="dtGrid_BeginningEdit" RowEditEnding="dtGrid_RowEditEnding" AutoGenerateColumns="True" AutoGeneratingColumn="dtGrid_AutoGeneratingColumn" />               
        </TabItem>
        <TabItem Header="Later" Name="tabItem2">

        </TabItem>
    </TabControl>

</Grid>

C#

public partial class MainWindow : Window
{

    private MySqlDataAdapter myDataAdapter;        
    private DataSet myDataSet;
    private MySqlCommandBuilder myBuilder;
    private MySqlConnection myConn = DBUtils.GetDBConnection();

    public MainWindow()
    {
        InitializeComponent();
        myConn.Open();
        myDataAdapter = new MySqlDataAdapter {SelectCommand=new MySqlCommand() {Connection=myConn, CommandText= "SELECT * FROM brands" } };
        myDataSet = new DataSet();
        myDataAdapter.Fill(myDataSet, "brands");           
        dtGrid.DataContext = myDataSet.Tables["brands"].DefaultView;
    }

    private void dtGrid_BeginningEdit(object sender, DataGridBeginningEditEventArgs e)
    {
        myBuilder  = new MySqlCommandBuilder(myDataAdapter);
        DataRowView myDRV = (DataRowView)dtGrid.SelectedItem;
        myDRV.BeginEdit();
    }

    private void dtGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
    {
        DataRowView myDRV = (DataRowView)dtGrid.SelectedItem;
        myDRV.EndEdit();
        myDataAdapter.UpdateCommand = myBuilder.GetUpdateCommand();
        myDataAdapter.Update(myDataSet, "brands");
    }

    private void dtGrid_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
    {
        var tc = e.Column as System.Windows.Controls.DataGridTextColumn;
        var b = tc.Binding as System.Windows.Data.Binding;

        b.UpdateSourceTrigger = UpdateSourceTrigger.PropertyChanged;
        b.ValidatesOnDataErrors = true;
        b.NotifyOnValidationError = true;
    }

}

这会丢失按钮，但您现在可以编辑和添加行，而无需按钮。要添加行，请转到 DataGrid 底部的空白行。