更新MySQL会返回受影响的行，但实际上不会更新数据库 [英] Updating MySQL returns rows affected, but doesn't actually update the Database

问题描述

我目前使用MonoDevelop在Ubuntu上使用Mono，并使用DataTable与数据库中的表匹配，并且应该尝试更新它。

I'm currently using Mono on Ubuntu with MonoDevelop, running with a DataTable matching a table in the database, and should be attempting to update it.

以下使用从另一台机器上的Dataset.WriteXML创建的XML文件中加载的数据集。

The code following uses a Dataset loaded from an XML file, which was created from a Dataset.WriteXML on another machine.

try
{
    if(ds.Tables.Contains(s))
    {                        
        ds.Tables[s].AcceptChanges();
        foreach(DataRow dr in ds.Tables[s].Rows)
            dr.SetModified(); // Setting to modified so that it updates, rather than inserts, into the database
        hc.Data.Database.Update(hc.Data.DataDictionary.GetTableInfo(s), ds.Tables[s]);
    }
            }
            catch (Exception ex)
            {
                Log.WriteError(ex);
            }

这是用于插入/更新到数据库的代码。

This is the code for inserting/updating into the database.

public override int SQLUpdate(DataTable dt, string tableName)
    {
        MySqlDataAdapter da = new MySqlDataAdapter();
        try
        {
            int rowsChanged = 0;
            int tStart = Environment.TickCount;

            da.SelectCommand = new MySqlCommand("SELECT * FROM " + tableName);
            da.SelectCommand.Connection = connection;

            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            da.UpdateCommand = cb.GetUpdateCommand();
            da.DeleteCommand = cb.GetDeleteCommand();
            da.InsertCommand = cb.GetInsertCommand();
            da.ContinueUpdateOnError = true;
            da.AcceptChangesDuringUpdate = true;

            rowsChanged = da.Update(dt);

            Log.WriteVerbose("Tbl={0},Rows={1},tics={2},", dt.TableName, rowsChanged, Misc.Elapsed(tStart));
            return rowsChanged;
            catch (Exception ex)
            {
                Log.WriteError("{0}", ex.Message);
                return -1
            }

我正在尝试上述代码， rowsChanged变为4183，我正在编辑的行数。但是，当我使用HeidiSQL检查数据库本身时，它根本不会改变任何东西。

I'm trying the above code, and rowsChanged becomes 4183, the number of rows I'm editing. However, when I use HeidiSQL to check the database itself, it doesn't change anything at all.

我缺少一个步骤？

编辑：或者，能够覆盖数据库中的所有行也可以工作。这是用于使用USB记忆棒更新远程计算机的设置，强制其匹配源数据表。

Alternatively, being able to overwrite all rows in the database would work as well. This is a setup for updating remote computers using USB sticks, forcing it to match a source data table.

编辑2：添加更多代码示例以显示DT的来源。 DataTable在调用函数中预填充，所有行都有DataRow.SetModified（）;应用。

Edit 2: Added more code sample to show the source of the DT. The DataTable is prefilled in the calling function, and all rows have DataRow.SetModified(); applied.

编辑3：附加信息。表中正在填充来自XML文件的数据。

Edit 3: Additional information. The Table is being filled with data from an XML file. Attempting fix suggested in comments.

感谢您的意见。

推荐答案

您可能想要查看的最简单的方法可能是TRUNCATE目标表， XML导入到它（使用AI关闭，因此它使用导入的ID，如果必要）。唯一的问题可能是有权这样做。否则...

The simplest way which you may want to look into might be to TRUNCATE the destination table, then simply save the XML import to it (with AI off so it uses the imported ID if necessary). The only problem may be with the rights to do that. Otherwise...

您尝试执行的操作几乎 可使用 Merge 方法。 由于该方法在 DataTables 上执行，因此如果某行已在master数据库，它将根本不存在于XML提取中（与 已删除的 RowState 不同）。

What you are trying to do can almost be handled using the Merge method. However, it can't/won't know about deleted rows. Since the method is acting on DataTables, if a row was deleted in the master database, it will simply not exist in the XML extract (versus a RowState of Deleted). These can be weeded out with a loop.

同样，任何新行对于AI int都可能获得不同的PK。为了防止这种情况，只需在目标数据库中使用一个简单的非AI PK，以便它可以接受任何数字。

Likewise, any new rows may get a different PK for an AI int. To prevent that, just use a simple non-AI PK in the destination db so it can accept any number.

XML加载：

private DataTable LoadXMLToDT(string filename)
{
    DataTable dt = new DataTable();
    dt.ReadXml(filename);
    return dt;
}

合并代码：

DataTable dtMaster = LoadXMLToDT(@"C:\Temp\dtsample.xml");
// just a debug monitor
var changes = dtMaster.GetChanges();

string SQL = "SELECT * FROM Destination";
using (MySqlConnection dbCon = new MySqlConnection(MySQLOtherDB))
{
    dtSample = new DataTable();
    daSample = new MySqlDataAdapter(SQL, dbCon);

    MySqlCommandBuilder cb = new MySqlCommandBuilder(daSample);
    daSample.UpdateCommand = cb.GetUpdateCommand();
    daSample.DeleteCommand = cb.GetDeleteCommand();
    daSample.InsertCommand = cb.GetInsertCommand();
    daSample.FillSchema(dtSample, SchemaType.Source);
    dbCon.Open();

    // the destination table
    daSample.Fill(dtSample);

    // handle deleted rows
    var drExisting = dtMaster.AsEnumerable()
                .Select(x => x.Field<int>("Id"));
    var drMasterDeleted = dtSample.AsEnumerable()
                .Where( q => !drExisting.Contains(q.Field<int>("Id")));

    // delete based on missing ID
    foreach (DataRow dr in drMasterDeleted)
        dr.Delete();

    // merge the XML into the tbl read
    dtSample.Merge(dtMaster,false, MissingSchemaAction.Add);

    int rowsChanged = daSample.Update(dtSample);
}

无论如何， rowsChanged 总是报告与总行数一样多的更改。但是Master / XML DataTable的更改会流向其他/目标表。

For whatever reason, rowsChanged always reports as many changes as there are total rows. But changes from the Master/XML DataTable do flow thru to the other/destination table.

删除代码获取现有ID列表，然后确定需要哪些行通过新XML表是否具有带有该ID的行从目标DataTable中删除。所有缺少的行都被删除，然后表合并。

The delete code gets a list of existing IDs, then determines which rows needs to be deleted from the destination DataTable by whether the new XML table has a row with that ID or not. All the missing rows are deleted, then the tables are merged.

关键是 dtSample.Merge（dtMaster，false，MissingSchemaAction.Add）; 其中合并来自 dtMaster 与 dtSample 的数据。 false param允许传入的XML更改覆盖另一个表中的值（并最终保存到db）。

The key is dtSample.Merge(dtMaster,false, MissingSchemaAction.Add); which merges the data from dtMaster with dtSample. The false param is what allows the incoming XML changes to overwrite values in the other table (and eventually be saved to the db).

我不知道一些问题，如不匹配的AI PK是一个大问题，但是这似乎处理所有我可以找到。实际上，您要做的是数据库同步。虽然有一个表，只有几行，以上应该工作。

I have no idea whether some of the issues like non matching AI PKs is a big deal or not, but this seems to handle all that I could find. In reality, what you are trying to do is Database Synchronization. Although with one table, and just a few rows, the above should work.

这篇关于更新MySQL会返回受影响的行，但实际上不会更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！