比较 ASP.NET Web 应用程序的两个 MS Access 后端数据库

Question

对 ASP.net 应用程序 MS Access 后端进行每日备份，

在我们的办公室，我们会在接下来的几天里

我们需要在每天结束时评估对数据库表中的记录所做的更改，我想比较 2 个 Access 数据库，第一个数据库是昨天的备份，第二个数据库是今天的备份

我想到了以下算法，请仔细阅读并告诉我如何继续比较

我需要的 数据表/网格视图显示包含差异/更新/删除数据的行/单元格

Imports System.Data
Imports System.Data.OleDb
Partial Class MoKoTrack
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim myDB = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|database.mdb;Persist Security Info=True")

    Session("CurrentDB") = myDB


    myDB.open()
    Dim mytables = myDB.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {})

    Dim CurrentTable As String
    Dim ee As Integer = mytables.Rows.Count
    Dim OriginalTables(ee) As String
    Dim BackupTables(ee) As String
    Dim X As Integer = 0
    For i = 1 To mytables.Rows.Count
        CurrentTable = mytables.Rows(i - 1).Item(2).ToString
        If mytables.Rows(i - 1).Item(3).ToString = "TABLE" Or mytables.Rows(i - 1).Item(3).ToString = "VIEW" Then
            If CurrentTable.Contains("Backup") Then
                BackupTables(X) = CurrentTable
            Else
                OriginalTables(X) = CurrentTable
            End If
            X = X + 1
        End If
    Next i

    For i = 0 To BackupTables.Count - 1
        If Not BackupTables(i) = "" Then
            CompareTable(BackupTables(i))

        End If
    Next
    myDB.Close()
End Sub

Sub CompareTable(ByVal BackupTableName As String)
    Dim OriginalTable As New DataTable
    Dim BackupTable As New DataTable
    Dim ModificationsTable As New DataTable

    Dim myDB = Session("CurrentDB")
    Dim FinalSQLString = "SELECT * FROM [" + BackupTableName + "]"
    Dim myDBCommand = New OleDbCommand(FinalSQLString, myDB)
    'Generate a temporary reader to get the number of cases
    Dim myReader As IDataReader = myDBCommand.ExecuteReader()

    'Dim myColumns = myReader.GetSchemaTable
    'For I = 1 To myColumns.Rows.Count
    '    OriginalTable.Columns.Add(myColumns.Rows(I - 1).Item(0).ToString())
    'Next I
    BackupTable.Load(myReader)

    Dim OriginalTableName = Left(BackupTableName, Len(BackupTableName) - 6)
    Dim FinalSQLString2 = "SELECT * FROM [" + BackupTableName + "]"
    Dim myDBCommand2 = New OleDbCommand(FinalSQLString, myDB)
    'Generate a temporary reader to get the number of cases
    Dim myReader2 As IDataReader = myDBCommand.ExecuteReader()
    OriginalTable.Load(myReader2)

    'Dim myGrid As New GridView
    'myGrid.DataSource = OriginalTable
    'myGrid.DataBind()
    'Me.form1.Controls.Add(myGrid)

    'Dim myGrid2 As New GridView
    'myGrid2.DataSource = BackupTable
    'myGrid2.DataBind()
    'Me.form1.Controls.Add(myGrid2)


    For i = 0 To OriginalTable.Rows.Count - 1
        For t = 0 To OriginalTable.Columns.Count - 1

        Next
    Next
End Sub

End Class

我正在使用以下 VBA 代码将备份数据库表重命名为“tablebackup”

Private Sub Command0_Click()
Dim tdf As TableDef

For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
  tdf.Name = tdf.Name & "backup"
End If
Next

End Sub

Answer 1

您能否为每个表添加一个 ModifiedOn 字段并提取自最后一天以来已修改的行。您可以比较修改的内容（备份中存在的内容）并写出所有新的内容（不存在的内容）。