比较两个毫秒访问一个asp.net web应用程序的后端数据库 [英] comparing two ms access backend databases of an asp.net web application

问题描述

在我们的办公室，我们采取ASP.net应用毫秒的存取后端的日常备份

因为我们需要评估数据库中的表记录所做的更改，未来几天

在每一天，我想比较2访问数据库的第一个数据库的到底是备份的昨天和第二数据库备份的今天

我想下面的算法中，请仔细阅读，并告诉我如何进行比较的数据表/ GridView的

我需要显示日行/含差异/更新/删除数据的单元格

 进口System.Data这
进口System.Data.OleDb
局部类MoKoTrack
继承System.Web.UI.Page保护小组的Page_Load（BYVAL发件人为对象，BYVAL E上System.EventArgs）把手Me.Load
    昏暗MYDB =新的OleDbConnection（供应商= Microsoft.Jet.OLEDB.4.0;数据源= | DataDirectory目录| database.mdb;坚持安全信息= TRUE）    会议（CurrentDB）= MYDB
    myDB.open（）
    昏暗mytables = myDB.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，新的对象（）{}）    昏暗CurrentTable作为字符串
    昏暗的EE作为整数= mytables.Rows.Count
    昏暗的OriginalTables（EE）作为字符串
    昏暗的BackupTables（EE）作为字符串
    昏暗×如整数= 0
    对于i = 1到mytables.Rows.Count
        CurrentTable = mytables.Rows（I  -  1）.Item（2）的ToString
        如果mytables.Rows（I  -  1）.Item（3）的ToString =TABLE或者mytables.Rows（I  -  1）.Item（3）的ToString =VIEW然后
            如果CurrentTable.Contains（备份），然后
                BackupTables（X）= CurrentTable
            其他
                OriginalTables（X）= CurrentTable
            万一
            X = X + 1个
        万一
    接下来，我    对于i = 0到BackupTables.Count  -  1
        如果不BackupTables（I）=那
            CompareTable（BackupTables（I））        万一
    下一个
    myDB.Close（）
结束小组子CompareTable（BYVAL BackupTableName作为字符串）
    昏暗OriginalTable作为新的DataTable
    昏暗BackupTable作为新的DataTable
    昏暗ModificationsTable作为新的DataTable    昏暗MYDB =会议（CurrentDB）
    DIM FinalSQLString =SELECT * FROM [+ BackupTableName +]
    昏暗的myDBCommand =新的OleDbCommand（FinalSQLString，MYDB）
    生成一个临时的读者得到的病例数
    昏暗myReader作为的IDataReader = myDBCommand.ExecuteReader（）    点心myColumns = myReader.GetSchemaTable
    对于i = 1到myColumns.Rows.Count
    'OriginalTable.Columns.Add（myColumns.Rows（I  -  1）.Item（0）的ToString（））
    下一步我
    BackupTable.Load（myReader）    昏暗的OriginalTableName =左（BackupTableName，莱恩（BackupTableName） -  6）
    DIM FinalSQLString2 =SELECT * FROM [+ BackupTableName +]
    昏暗myDBCommand2 =新的OleDbCommand（FinalSQLString，MYDB）
    生成一个临时的读者得到的病例数
    昏暗myReader2作为的IDataReader = myDBCommand.ExecuteReader（）
    OriginalTable.Load（myReader2）    暗淡myGrid作为新的GridView
    myGrid.DataSource = OriginalTable
    myGrid.DataBind（）
    Me.form1.Controls.Add（myGrid）    暗淡myGrid2作为新的GridView
    myGrid2.DataSource = BackupTable
    myGrid2.DataBind（）
    Me.form1.Controls.Add（myGrid2）
    对于i = 0到OriginalTable.Rows.Count  -  1
        对于t = 0到OriginalTable.Columns.Count  -  1        下一个
    下一个
结束小组末级
 

我用下面的VBA code重命名备份数据库表到tablebackup

 私人小组Command0_Click（）
昏暗的TDF作为的TableDef对于每个TDF在CurrentDb.TableDefs
如果左（tdf.Name，4）LT;＆GT; MSys的然后
  tdf.Name = tdf.Name＆安培; 备份
万一
下一个结束小组
 

解决方案

您可以添加一个ModifiedOn场为每个表和自解压的最后一天已经修改的行。您可以比较那些修饰（存在于备份的那些），并写出所有新的（不这样做的那些）。

at our office we take daily backup of ASP.net application ms access backend

for the next few days we need to evaluate the changes made to records in the database tables

at the end of each day i want to compare 2 access databases first database is the backup of yesterday and second database is the backup of today

i thought of the following algorithm, please read carefully and tell me how to proceed to compare the datatables / gridviews

i need to display th rows / cells containing the differences / updates / deleted data

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

i am using the following VBA code to rename backup database tables into "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

解决方案

Can you add a ModifiedOn field for each table and extract the rows that have been modified since the last day. You can compare the modified ones (the ones that exist in the backup) and write out all new ones (the ones that don't).

这篇关于比较两个毫秒访问一个asp.net web应用程序的后端数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！