如何从c#上的datagrid更新mysql数据库？

nsc4cvqm  于 2021-06-18  发布在  Mysql
关注(0)|答案(3)|浏览(441)

我正在尝试从datagrid更新mysql db，但它不起作用。添加的数据不在数据库中，并且没有错误，为什么会发生这种情况。我已经试着解决这个问题好几天了。

  1. private void dtGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
  2.         {    
  3.         MySqlConnection conn = DBUtils.GetDBConnection();
  4.         string table = "brands";
  5.         string sql = "SELECT * FROM "+table;
  6.         MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(sql, conn);
  7.         conn.Open();
  8.         MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(myDataAdapter);
  9.         myDataAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
  10.         myDataAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
  11.         myDataAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();
  12.         DataSet myDataSet = new DataSet();
  13.         myDataAdapter.Fill(myDataSet, table);
  14.         myDataAdapter.AcceptChangesDuringUpdate = true;
  15.         myDataAdapter.Update(myDataSet, table);
  16.         conn.Close();
  17.     }
mysqlDatabasec#wpfDataGrid

3条答案

按热度按时间
g6baxovj

g6baxovj1#

看起来您正在运行此代码，只要用户已按名称进行了编辑 RowEditEnding ".
所以我认为问题是你只是在创造 MySqlCommandBuilder 编辑完成后。
你需要创建 MySqlCommandBuilder 首先，进行编辑，然后获取更新/插入/删除命令。
e、 g.类似于以下内容（在vb中很抱歉，但您了解要点）：

  1. Using NotesDS As New DataSet
  2.         Using NotesDA As New SqlDataAdapter With {.SelectCommand = New SqlCommand With {.Connection = SQLDBConnection, .CommandText = "SELECT * FROM Notes WHERE ID = " & ID}}
  3.             NotesDA.Fill(NotesDS, "Notes")
  4.             Using NotesDV As New DataView(NotesDS.Tables("Notes"))
  5.                 Using NoteBuilder As New SqlCommandBuilder(NotesDA) With {.QuotePrefix = "[", .QuoteSuffix = "]"}                        
  6.                     If NotesDV.Count = 1 Then                             
  7.                         Dim NoteDRV As DataRowView = NotesDV(0)
  8.                         NoteDRV.BeginEdit()
  9.                         NoteDRV.Item("UserName") = UserName
  10.                         NoteDRV.Item("Note") = Note
  11.                         NoteDRV.Item("NoteDate") = NoteDate
  12.                         NoteDRV.Item("CompanyCode") = CompanyCode
  13.                         NoteDRV.EndEdit()
  14.                         NotesDA.UpdateCommand = NoteBuilder.GetUpdateCommand
  15.                         NotesDA.Update(NotesDS, "Notes")
  16.                     End If
  17.                 End Using
  18.             End Using
  19.         End Using
  20.     End Using

编辑
@尤金，你的工作是什么 DataGrid 一定要吗？想必你正在设定一个 DataView 作为 DataContext ?
如果是这样的话，那么你将使用 DataAdapter 填充 DataView 在页面加载上？这就是 DataAdapter 你需要初始化你的 MySqlCommandBuilder 与。
请尝试以下操作：
宣布你 DataAdapter , DataSet 以及 DataView 在页面顶部。
使用 BeginningEdit dtgrid的处理程序来初始化 MySqlCommandBuilder 在你宣布的 DataAdapter 使用 RowEditEnding 运行 DataAdapter.Update 命令
e、 g.（据我所知，我使用的是sql，但mysql的工作原理是一样的）

  1. using System.Windows;
  2. using System.Windows.Controls;    
  3. using System.Data;
  4. using System.Data.SqlClient;
  6. namespace testApp
  7. {
  8.     /// <summary>
  9.     /// Interaction logic for MainWindow.xaml
  10.     /// </summary>
  11.     public partial class MainWindow : Window
  12.     {
  13.         public MainWindow()
  14.         {
  15.             InitializeComponent();
  16.         }
  18.         private SqlDataAdapter myDataAdapter;
  19.         private DataView myDataView;
  20.         private DataSet myDataSet;
  21.         private SqlCommandBuilder myBuilder;
  22.         private SqlConnection myConn = new SqlConnection("CONNECTIONSTRING");
  23.         private void Window_Loaded(object sender, RoutedEventArgs e)
  24.         {
  25.             myConn.Open();
  26.             myDataAdapter = new SqlDataAdapter {SelectCommand=new SqlCommand() {Connection=myConn, CommandText="SELECT MINumber, CompanyName FROM EIncCompanies WHERE CompanyName LIKE 'Test%'" } };
  27.             myDataSet = new DataSet();
  28.             myDataAdapter.Fill(myDataSet, "EIncCompanies");
  29.             myDataView = new DataView(myDataSet.Tables["EIncCompanies"]);
  30.             dtGrid.DataContext = myDataView;
  31.         }
  33.         private void dtGrid_BeginningEdit(object sender, DataGridBeginningEditEventArgs e)
  34.         {
  35.             myBuilder  = new SqlCommandBuilder(myDataAdapter) { QuotePrefix="[", QuoteSuffix="]"};
  36.             DataRowView myDRV = (DataRowView)dtGrid.SelectedItem;
  37.             myDRV.BeginEdit();
  38.         }
  40.         private void dtGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
  41.         {
  42.             DataRowView myDRV = (DataRowView)dtGrid.SelectedItem;
  43.             myDRV.EndEdit();
  44.             myDataAdapter.UpdateCommand = myBuilder.GetUpdateCommand();
  45.             myDataAdapter.Update(myDataSet, "EIncCompanies");
  46.         }
  48.     }
  49. }

我还设置了 Mode=TwoWay, UpdateSourceTrigger=PropertyChanged 论图书馆的装订 DataGrid .

展开查看全部
赞(0）分享  回复(0）举报  2021-06-18
vsnjm48y

vsnjm48y2#

@尤金
所以这里：

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

然后用

  1. myDataAdapter.Fill(ds, table);

您正在创建一个新的数据表，然后向其中添加一个现有的数据表。这就是为什么所有行都被再次添加。
尝试将代码更改为。xaml编号：

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

c级#

  1. public partial class MainWindow : Window
  2. {
  4.     private MySqlDataAdapter myDataAdapter;        
  5.     private DataSet myDataSet;
  6.     private MySqlCommandBuilder myBuilder;
  7.     private MySqlConnection myConn = DBUtils.GetDBConnection();
  9.     public MainWindow()
  10.     {
  11.         InitializeComponent();
  12.         myConn.Open();
  13.         myDataAdapter = new MySqlDataAdapter {SelectCommand=new MySqlCommand() {Connection=myConn, CommandText= "SELECT * FROM brands" } };
  14.         myDataSet = new DataSet();
  15.         myDataAdapter.Fill(myDataSet, "brands");           
  16.         dtGrid.DataContext = myDataSet.Tables["brands"].DefaultView;
  17.     }
  19.     private void dtGrid_BeginningEdit(object sender, DataGridBeginningEditEventArgs e)
  20.     {
  21.         myBuilder  = new MySqlCommandBuilder(myDataAdapter);
  22.         DataRowView myDRV = (DataRowView)dtGrid.SelectedItem;
  23.         myDRV.BeginEdit();
  24.     }
  26.     private void dtGrid_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
  27.     {
  28.         DataRowView myDRV = (DataRowView)dtGrid.SelectedItem;
  29.         myDRV.EndEdit();
  30.         myDataAdapter.UpdateCommand = myBuilder.GetUpdateCommand();
  31.         myDataAdapter.Update(myDataSet, "brands");
  32.     }
  34.     private void dtGrid_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
  35.     {
  36.         var tc = e.Column as System.Windows.Controls.DataGridTextColumn;
  37.         var b = tc.Binding as System.Windows.Data.Binding;
  39.         b.UpdateSourceTrigger = UpdateSourceTrigger.PropertyChanged;
  40.         b.ValidatesOnDataErrors = true;
  41.         b.NotifyOnValidationError = true;
  42.     }
  44. }

这会丢失按钮，但您现在可以编辑和添加行，而不需要按钮。向DATAGRID底部的空白行添加行。

展开查看全部
赞(0）分享  回复(0）举报  2021-06-18
r1zk6ea1

r1zk6ea13#

@simon，我做了不同的事情，现在它插入了dtgrid中的所有值，而不仅仅是新值，也许我没有正确地共享它。填充dbgrid：

  1. public MainWindow()
  2.     {
  3.         InitializeComponent();
  4.         /*string sql = "SELECT * FROM brands";*/
  5.         MySqlConnection conn = DBUtils.GetDBConnection();
  6.         MySqlCommand cmd = new MySqlCommand("select * from brands", conn);
  7.         conn.Open();
  8.         DataTable dt = new DataTable();
  9.         dt.Load(cmd.ExecuteReader());
  10.         conn.Close();
  11.         dtGrid.ItemsSource = dt.DefaultView;
  12.     }

插入新项目

  1. private void Insert_Click(object sender, RoutedEventArgs e)
  2.     {
  3.         MySqlConnection conn = DBUtils.GetDBConnection();
  4.         string table = "brands";
  5.         string sql = "SELECT * FROM " + table;
  6.         MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(sql, conn);
  7.         conn.Open();
  8.         MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(myDataAdapter);
  9.         DataSet myDataSet = new DataSet();
  10.         DataTable dt = new DataTable();
  11.         DataSet ds = new DataSet();
  12.         dt = ((DataView)dtGrid.ItemsSource).ToTable() as DataTable;
  13.         ds.Tables.Add(dt);
  14.         myDataAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
  15.         myDataAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
  16.         myDataAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();
  17.         myDataAdapter.Fill(ds, table);
  18.         myDataAdapter.AcceptChangesDuringUpdate = true;
  19.         myDataAdapter.Update(ds, table);
  20.     }

xaml公司

  1. <Window x:Class="TechD.MainWindow"
  2.     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  3.     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  4.     Title="TechDep" Height="390" Width="902">
  5. <Grid>
  6.     <TabControl Height="310" HorizontalAlignment="Left" Margin="-1,-1,0,0" Name="tabControl1" VerticalAlignment="Top" Width="763">
  7.         <TabItem Header="Brands" Name="tabBrands">
  8.             <DataGrid AutoGenerateColumns="True" Height="280" Name="dtGrid" Width="750" CanUserAddRows="True" ItemsSource="{Binding}" RowEditEnding="dtGrid_RowEditEnding" />
  9.         </TabItem>
  10.         <TabItem Header="Later" Name="tabItem2">
  12.         </TabItem>
  13.     </TabControl>
  14.     <Button Content="Insert" Height="23" HorizontalAlignment="Left" Margin="774,21,0,0" Name="Insert" VerticalAlignment="Top" Width="90" Click="Insert_Click" />
  15.     <Button Content="Button" Height="23" HorizontalAlignment="Left" Margin="774,50,0,0" Name="button2" VerticalAlignment="Top" Width="90" />
  16.     <Button Content="Button" Height="23" HorizontalAlignment="Left" Margin="774,79,0,0" Name="button3" VerticalAlignment="Top" Width="90" />
  17.     <Button Content="Button" Height="23" HorizontalAlignment="Left" Margin="774,108,0,0" Name="button4" VerticalAlignment="Top" Width="90" />
  18. </Grid>
展开查看全部
赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com