WinForms DataGridView - 更新数据库 [英] WinForms DataGridView - update database

问题描述

我知道这是 DataGridView 的一个基本功能，但由于某种原因，我无法让它工作.我只希望我的 Windows 窗体上的 DataGridView 在用户单击保存"按钮时将对其所做的任何更改提交到数据库.

I know this is a basic function of the DataGridView, but for some reason, I just can't get it to work. I just want the DataGridView on my Windows form to submit any changes made to it to the database when the user clicks the "Save" button.

我根据用户在 DropDownList 中的选择触发的函数填充 DataGridView，如下所示:

I populate the DataGridView according to a function triggered by a user selection in a DropDownList as follows:

using (SqlConnection con = new SqlConnection(conString))
{
    con.Open();
    SqlDataAdapter ruleTableDA = new SqlDataAdapter("SELECT rule.fldFluteType AS [Flute], rule.fldKnife AS [Knife], rule.fldScore AS [Score], rule.fldLowKnife AS [Low Knife], rule.fldMatrixScore AS [Matrix Score], rule.fldMatrix AS [Matrix] FROM   dbo.tblRuleTypes rule WHERE rule.fldMachine_ID = '1003'", con);
    DataSet ruleTableDS = new DataSet();
    ruleTableDA.Fill(ruleTableDS);
    RuleTable.DataSource = ruleTableDS.Tables[0];
}

在我的保存功能中，我基本上有以下内容(我已经删减了一些围绕它的代码以切入重点):

In my save function, I basically have the following (I've trimmed out some of the code around it to get to the point):

using (SqlDataAdapter ruleTableDA = new SqlDataAdapter("SELECT rule.fldFluteType AS [Flute], rule.fldKnife AS [Knife], 
       rule.fldScore AS [Score], rule.fldLowKnife AS [Low Knife],
       rule.fldMatrixScore AS [Matrix Score], rule.fldMatrix AS [Matrix]
       FROM dbo.tblRuleTypes rule WHERE rule.fldMachine_ID = '1003'", con))
    {
        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(ruleTableDA);
        DataTable dt = new DataTable();
        dt = RuleTable.DataSource as DataTable;
        ruleTableDA.Fill(dt);
        ruleTableDA.Update(dt);
    }

好的，所以我编辑了代码以执行以下操作:构建命令，基于 DataGridView (RuleTable) 创建一个 DataTable，用 DataTable 填充 DataAdapter，然后更新数据库.现在 ruleTableDA.Update(dt) 抛出异常并发冲突:UpdateCommand 影响了预期的 1 条记录中的 0 条."

Okay, so I edited the code to do the following: build the commands, create a DataTable based on the DataGridView (RuleTable), fill the DataAdapter with the DataTable, and update the database. Now ruleTableDA.Update(dt) is throwing the exception "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."

推荐答案

我认为这里有几个问题:要记住的顺序是，当您加载网格时，它已经指向一个数据表/集.当您在网格中键入时，更改会暂时保留到绑定到网格的数据表中.因此，您不希望每次保存时都创建数据表，因为对现有数据表所做的更改将被忽略.第二个问题是您可能不需要每次都创建绑定源，因为就像第一点一样，如果网格显示数据，则已经有绑定源.第三个问题是 SQLCommandBuilder 类具有 GetInsertCommand、GetUpdateCommand 等方法，必须使用这些方法才能实际获取适当的命令.

I believe there are a few problems here: The sequence to keep in mind is that when you load up your grid, it is pointed to a datatable/set already. When you type into the grid, the changes are temporarily persisted to the data table that is bound to the grid. Therefore, you do not want to be creating a data table every time you save, because the changes that were made to the existing data table are being ignored. Second issue is that you probably don't need to create a binding source everytime, because just like the first point, if the grid is showing data, there is already a binding source that it is bound to. Third problem is that the SQLCommandBuilder class has methods like GetInsertCommand, GetUpdateCommand, etc. that must be used to actually get the appropriate commands.

using (SqlDataAdapter ruleTableDA = new SqlDataAdapter("SELECT rule.fldFluteType AS [Flute], rule.fldKnife AS [Knife], 
       rule.fldScore AS [Score], rule.fldLowKnife AS [Low Knife],
       rule.fldMatrixScore AS [Matrix Score], rule.fldMatrix AS [Matrix]
       FROM dbo.tblRuleTypes rule WHERE rule.fldMachine_ID = '1003'", con))
    {
        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(ruleTableDA);
        DataTable dt = new DataTable();
        dt = RuleTable.DataSource as DataTable;
        //ruleTableDA.Fill(dt);
        ruleTableDA.Update(dt);
    }

这篇关于WinForms DataGridView - 更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！