OledbDataAdapter不更新表

Question

我在更新数据表时遇到问题。我要做的是：

1)从数据库加载数据(让我们称之为“旧数据库”

2)从其他数据库加载数据(让我们称之为“新数据库”)

3)清除新数据库中的所有数据

4)将所有旧数据(从旧数据库)合并到新数据库中。

以下是我如何实现这一目标的方法：

        string oldDatabase = this.txtOldReport.Text;
        string newDatabase = this.txtNewReport.Text;
        string backupFolder = @"C:\Planit\Report Updater\Backups";

        if ( !Directory.Exists ( backupFolder ) ) {
            Directory.CreateDirectory ( backupFolder );
        }

        string fullPath = Path.Combine ( backupFolder, string.Format ( @"Update Backup_{0}_{1}_{2}_{3}_{4}_{5}",
                                                                       DateTime.Now.Year,
                                                                       DateTime.Now.Month,
                                                                       DateTime.Now.Day,
                                                                       DateTime.Now.Hour,
                                                                       DateTime.Now.Minute,
                                                                       DateTime.Now.Second) );

        Directory.CreateDirectory ( fullPath );

        File.Copy ( oldDatabase, Path.Combine ( fullPath, @"Old Report.mdb" ) );
        File.Copy ( newDatabase, Path.Combine ( fullPath, @"New Report.mdb" ) );

        OleDbConnection oldConnection = new OleDbConnection ( string.Format ( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", oldDatabase ) );
        OleDbConnection newConnection = new OleDbConnection ( string.Format ( @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", newDatabase ) );
        OleDbCommand oldCommand = oldConnection.CreateCommand ( );
        OleDbCommand newSelectCommand = newConnection.CreateCommand ( );

        oldCommand.CommandText = @"SELECT * FROM Reports";
        newSelectCommand.CommandText = @"SELECT * FROM Reports";

        oldConnection.Open ( );
        newConnection.Open ( );

        DataSet oldDataset = new DataSet ( );
        DataSet newDataset = new DataSet ( );

        OleDbDataAdapter oldAdapter = new OleDbDataAdapter ( oldCommand );
        OleDbDataAdapter newAdapter = new OleDbDataAdapter ( newSelectCommand );

        OleDbCommandBuilder builder = new OleDbCommandBuilder ( newAdapter );

        builder.QuotePrefix = "[";
        builder.QuoteSuffix = "]";

        newAdapter.UpdateCommand = builder.GetUpdateCommand ( true );
        newAdapter.InsertCommand = builder.GetInsertCommand ( true );
        newAdapter.DeleteCommand = builder.GetDeleteCommand ( true );

        oldAdapter.Fill ( oldDataset );
        newAdapter.Fill ( newDataset );

        newDataset.Tables [ 0 ].Rows.Clear ( );

        // removed for a response below
        //foreach ( DataRow dr in newDataset.Tables [ 0 ].Rows ) {
        //    dr.Delete ( );
        //}

        foreach ( DataRow dr in oldDataset.Tables [ 0 ].Rows ) {
            dr.SetAdded ( );
            newDataset.Tables [ 0 ].ImportRow ( dr );
        }

        // removed for the response below
        //newDataset.AcceptChanges ( );

        newAdapter.Update ( newDataset.Tables [ 0 ] );

        oldConnection.Close ( );
        newConnection.Close ( );

    }   

有人能看到我的问题吗？当我叫“newAdapter.Update”时，什么都不会发生。

谢谢你的帮助。

Answer 1

MSDN说

当调用AcceptChanges时，任何仍处于编辑模式的DataRow对象都会成功地结束其编辑。DataRowState也会发生变化:所有已添加和修改的行不变，删除的行将被删除。

所以，这条线

  newDataset.AcceptChanges ( );

从导入的表中移除添加的RowState，当您调用更新时，数据库中没有要添加的行，因为newDataset表中的每一行的状态都等于Unchanged

所以你只需要删除它，然后再调用你的newAdapter.Update

顺便提一句，我不认为您需要遍历newDataset.Tables来删除每一行，因为您之前已经调用了newDataset.Tables[0].Rows.Clear()