时间戳

首页 > 解决方案 > 如何从 C# 上的 DataGrid 更新 MySql 数据库？

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

问题描述

我正在尝试从 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();
    }

标签： c#mysqldatabasewpfdatagrid

解决方案

一旦用户已经进行了编辑，您似乎正在运行此代码，名称为“ RowEditEnding”。

所以我认为问题在于你只是MySqlCommandBuilder在编辑完成后才创建。

您需要创建第MySqlCommandBuilder一个，然后进行编辑，然后获取更新/插入/删除命令。

例如类似以下的内容（在 VB 中很抱歉，但你明白了要点）：

Using NotesDS As New DataSet
        Using NotesDA As New SqlDataAdapter With {.SelectCommand = New SqlCommand With {.Connection = SQLDBConnection, .CommandText = "SELECT * FROM Notes WHERE ID = " & ID}}
            NotesDA.Fill(NotesDS, "Notes")
            Using NotesDV As New DataView(NotesDS.Tables("Notes"))
                Using NoteBuilder As New SqlCommandBuilder(NotesDA) With {.QuotePrefix = "[", .QuoteSuffix = "]"}                        
                    If NotesDV.Count = 1 Then                             
                        Dim NoteDRV As DataRowView = NotesDV(0)
                        NoteDRV.BeginEdit()
                        NoteDRV.Item("UserName") = UserName
                        NoteDRV.Item("Note") = Note
                        NoteDRV.Item("NoteDate") = NoteDate
                        NoteDRV.Item("CompanyCode") = CompanyCode
                        NoteDRV.EndEdit()
                        NotesDA.UpdateCommand = NoteBuilder.GetUpdateCommand
                        NotesDA.Update(NotesDS, "Notes")
                    End If
                End Using
            End Using
        End Using
    End Using

编辑

@Eugene，你有什么DataGrid约束？大概您将 a 设置DataView为DataContext?

如果是这种情况，那么您将使用 aDataAdapter来填充DataView可能的页面加载？这是DataAdapter您需要初始化的MySqlCommandBuilder。

尝试以下操作：

  • 声明你DataAdapter，DataSet并DataView在页面顶部。
  • 使用BeginningEdit您的 dtGrid 的处理程序来初始化MySqlCommandBuilder您声明的DataAdapter
  • 使用RowEditEnding运行DataAdapter.Update命令

例如（据我所知，我使用的是 SQL，但 MySQL 的工作原理相同）

using System.Windows;
using System.Windows.Controls;    
using System.Data;
using System.Data.SqlClient;

namespace testApp
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private SqlDataAdapter myDataAdapter;
        private DataView myDataView;
        private DataSet myDataSet;
        private SqlCommandBuilder myBuilder;
        private SqlConnection myConn = new SqlConnection("CONNECTIONSTRING");
        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            myConn.Open();
            myDataAdapter = new SqlDataAdapter {SelectCommand=new SqlCommand() {Connection=myConn, CommandText="SELECT MINumber, CompanyName FROM EIncCompanies WHERE CompanyName LIKE 'Test%'" } };
            myDataSet = new DataSet();
            myDataAdapter.Fill(myDataSet, "EIncCompanies");
            myDataView = new DataView(myDataSet.Tables["EIncCompanies"]);
            dtGrid.DataContext = myDataView;
        }

        private void dtGrid_BeginningEdit(object sender, DataGridBeginningEditEventArgs e)
        {
            myBuilder  = new SqlCommandBuilder(myDataAdapter) { QuotePrefix="[", QuoteSuffix="]"};
            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, "EIncCompanies");
        }

    }
}

我还设置Mode=TwoWay, UpdateSourceTrigger=PropertyChanged了绑定在DataGrid.

推荐阅读