比较 SQL 数据库模式

• 下载源代码 - 43.2 KB

引言

随着软件产品的演变，底层数据库模式的更改是不可避免的，您必须确保在开发周期中所做的任何更改都应用于软件分发的所有其他数据库。 在我们所有的产品中，我们都包含一个数据库管理模块，该模块将更改传播到实时数据库，并确保软件版本与数据库模式匹配。

但在开发的早期阶段，设计人员和开发人员所做的模式更改变化迅速，很难跟踪这些更改并将它们传播到其他数据库。 在 Paritor，我们遇到了这个问题，并且发现手动比较数据库可能是一项耗时的工作。 市场上有很多工具可以分析数据库、显示差异，甚至编写任何必要的更改脚本，但除了它们都需要花钱之外，它们似乎都是“小题大做”。 因此，前几天早上，我坐下来编写了一个简单的实用程序，用于打开两个数据库、分析差异并报告它发现的内容。

背景

我将其编写为 WPF 应用程序，主要是因为我想使用 WPF Document 命名空间来创建报告，并使用 WPF Document Viewer 显示它。 您可以轻松地使用 Windows Forms 编写它并使用 System.Drawing.Print，甚至生成水晶报表。

使用代码

该程序请求要比较的两个数据库的详细信息，并打开与每个数据库的 SQL 连接。

Private Sub btnAnalyse_Click(ByVal sender As System.Object, _
        ByVal e As System.Windows.RoutedEventArgs) Handles btnAnalyse.Click
'
' Try and open database 1
'
    Try
        Me.lblProgress.Content = "Open " & Me.txtServer1.Text & _
                                 " " & Me.txtDatabase1.Text
        Dim cnnString As String = "Initial Catalog=" & Me.txtDatabase1.Text & _
                                  ";Connect Timeout=60;Data Source=" & _
                                  Me.txtServer1.Text & ";user id=" & _
                                  Me.txtSQLLogin1.Text & _
                                  ";password=" & Me.txtSQLPassword1.Text
        cnn1 = New SqlConnection(cnnString)
        cnn1.Open()
        Catch ex As Exception
        MessageBox.Show("Connection to Database 1 Failed - " & _
                        ex.Message, "SQL Connection", _
                        MessageBoxButton.OK, MessageBoxImage.Error)
        Exit Sub
    End Try

'
' Try and open database 2
'
    Try
        Me.lblProgress.Content = "Open " & Me.txtServer2.Text & _
                                 " " & Me.txtDatabase2.Text
        Dim cnnString As String = "Initial Catalog=" & Me.txtDatabase2.Text & _
                                  ";Connect Timeout=60;Data Source=" & _
                                  Me.txtServer2.Text & ";user id=" & _
                                  Me.txtSQLLogin2.Text & ";password=" & _
                                  Me.txtSQLPassword2.Text
        cnn2 = New SqlConnection(cnnString)
        cnn2.Open()
        Catch ex As Exception
        MessageBox.Show("Connection to Database 2 Failed - " & ex.Message, _
                        "SQL Connection", MessageBoxButton.OK, _
                        MessageBoxImage.Error)
        cnn1.Close() ' Close each connection
        Exit Sub
    End Try
    Me.lblProgress.Content = ""

    Analyse     ' Call Analyse method differences between databases

    Try
        cnn1.Close() ' Close each connection
        cnn2.Close()
        Catch ex As Exception
        MessageBox.Show("Connection to Database 2 Failed - " & _
                        ex.Message, "SQL Connection", _
                        MessageBoxButton.OK, MessageBoxImage.Error)
    End Try

End Sub

然后，它创建两个列表，其中包含每个数据库中的用户表名称。 以下 SQL 返回每个表的 ID 和名称

select id,Name from sysobjects where xType='U'

然后，它遍历每个列表，检查另一个列表中的条目。 这让我们知道在哪个数据库中有一个表，但在另一个数据库中没有。 我创建了几个私有类来保存它发现的每个表的问题的详细信息，并声明这些问题的字典列表。

Private AnalyseTables As Dictionary(Of String, AnalyseTable)

#Region "Private Classes" 
  Private Class AnalyseTable
    Friend ExistsInDatabase1 As Boolean
    Friend ExistsInDatabase2 As Boolean
    Friend Database1ID As Integer
    Friend Database2ID As Integer
    Friend AnalyseColumns As New Dictionary(Of String, AnalyseColumn)

    Friend Sub New(ByVal Database1ID As Integer, ByVal Database2ID As Integer, _
                   ByVal ExistsInDatabase1 As Boolean, _
                   ByVal ExistsInDatabase2 As Boolean)
        Me.Database1ID = Database1ID
        Me.Database2ID = Database2ID
        Me.ExistsInDatabase1 = ExistsInDatabase1
        Me.ExistsInDatabase2 = ExistsInDatabase2
    End Sub
  End Class

  Private Class AnalyseColumn
    Friend Difference As String
 
    Friend Sub New(ByVal Difference As String)
    Me.Difference = Difference
    End Sub
  End Class
 
#End Region

正如您所看到的，有一个 Dictionary 类的列表 AnalysisTable，它保存每个表的详细信息，包括 AnalysisColumn 类的字典列表，该列表保存每个列问题的详细信息。

Private Sub CheckTables()
'
' Pass through each table in Database 1 and look to see if it exists in
' Database 2. Then pass through each table in database 2 and check it exists
' in Database 1.
'
' Create an entry in the collection of tables for each unique table
    Dim ds1 As New DataSet
    Dim ds2 As New DataSet
'
' Get list of user tables from database 1
'
    Try
    Dim cmd1 As New SqlCommand("select id, Name from" & _
                    " sysobjects where xType='U'", cnn1)
    Dim da1 As New SqlDataAdapter(cmd1)
    da1.Fill(ds1)
    Catch ex As Exception
    MessageBox.Show("Reading tables from Database 1 Failed - " & _
                    ex.Message, "SQL Connection", _
                    MessageBoxButton.OK, MessageBoxImage.Error)
    Exit Sub
    End Try
'
' Get list of user tables from database 2
'
    Try
        Dim cmd2 As New SqlCommand("select id,Name from " & _ 
                        "sysobjects where xType='U'", cnn2)
        Dim da2 As New SqlDataAdapter(cmd2)
        da2.Fill(ds2)
        Catch ex As Exception
        MessageBox.Show("Reading tables from Database 2 Failed - " & _
                        ex.Message, "SQL Connection", _
                        MessageBoxButton.OK, MessageBoxImage.Error)
        Exit Sub
    End Try
'
' For each table in database1 look to see if it exists in database 2
' and add the result to the tables collection
'
    For Each dr1 As DataRow In ds1.Tables(0).Rows
    Dim ExistsInDatabase2 As Boolean = False
    Dim Database2ID As Integer = 0
    For Each dr2 As DataRow In ds2.Tables(0).Rows
        If dr2("Name") = dr1("Name") Then
            ExistsInDatabase2 = True
            Database2ID = dr2("ID")
            Exit For
        End If
    Next
    AnalyseTables.Add(dr1("Name"), _
                      New AnalyseTable(dr1("ID"), _
                      Database2ID, True, ExistsInDatabase2))
    Next
'
' For each table in database2 look to see if it exists in the tables collection
' If it dosn't we need to add an item for this table to the tables collection
'
    For Each dr2 As DataRow In ds2.Tables(0).Rows
    If AnalyseTables.ContainsKey(dr2("Name")) = False Then
        AnalyseTables.Add(dr2("Name"), New AnalyseTable(0, dr2("ID"), False, True))
    End If
    Next
End Sub

在完成缺少表的分析检查后，它会查找 AnalysisTable 集合，并在其中记录表同时存在于两个数据库中，它会构建一个表列列表。 以下 SQL 执行此操作

select name,xtype,length from syscolumns where id={the id of the table}

它对列列表执行与表列表类似的任务，即，它检查列是否存在于每个列表中，并且如果它们存在，它会查看数据类型和长度是否相同。

Private Sub CheckColumns()
'
' Where the tables exists in both databases
' we need to compare the fields in each
'
'
' Pass through each table
'
For Each TableName As String In AnalyseTables.Keys
'
' Look to see if the table exists in both databases and if so
'
    Dim ds1 As New DataSet
    Dim ds2 As New DataSet
    If AnalyseTables(TableName).ExistsInDatabase1 = True And _
       AnalyseTables(TableName).ExistsInDatabase2 = True Then
'
' Get list of columns for the table from database 1
'
      Try
    Dim cmd1 As New SqlCommand("select name,xtype,length from " & _ 
             "syscolumns where id=" & _
             AnalyseTables(TableName).Database1ID, cnn1)
    Dim da1 As New SqlDataAdapter(cmd1)
    da1.Fill(ds1)
      Catch ex As Exception
    MessageBox.Show("Reading table columns from Database 1 Failed - " & _
                    ex.Message, "SQL Connection", _
                    MessageBoxButton.OK, MessageBoxImage.Error)
    Exit Sub
      End Try
'
' Get list of columns for table from database 2
'
      Try
    Dim cmd2 As New SqlCommand("select name,xtype,length from syscolumns" & _ 
             " where id=" & AnalyseTables(TableName).Database2ID, cnn2)
    Dim da2 As New SqlDataAdapter(cmd2)
    da2.Fill(ds2)
      Catch ex As Exception
    MessageBox.Show("Reading table columns from Database 2 Failed - " & _
                    ex.Message, "SQL Connection", _
                    MessageBoxButton.OK, MessageBoxImage.Error)
    Exit Sub
      End Try
'
' For each column in database1 table look to see if it exists in database 2 table
' and add the result to the tables columns collection collection
'
      For Each dr1 As DataRow In ds1.Tables(0).Rows
        Dim Difference As String = ""
        Dim ExistsInDatabase2 As Boolean = False
        For Each dr2 As DataRow In ds2.Tables(0).Rows
           If dr2("Name") = dr1("Name") Then
              If dr2("xtype") <> dr1("xtype") Then
                  Difference = "Type is Different - Database 1 has type of " & _
                               dr1("xtype") & _
                               " Database 2 has type of " & dr2("xtype")
              End If
              If dr2("length") <> dr1("length") Then
                  Difference = "Length is Different - Database 1 has length of " & _
                               dr1("length") & _
                               " Database 2 has length of " & dr2("length")
              End If
              ExistsInDatabase2 = True
              Exit For
            End If
        Next
        If ExistsInDatabase2 = False Then
          Difference = "Does not exists in Database 2"
        End If
        If Difference <> "" Then
          AnalyseTables(TableName).AnalyseColumns.Add(dr1("Name"), _
                        New AnalyseColumn(Difference))
        End If
    Next
'
' For each column in database2 table look to see if it exists in database 1 table
' If it doesn't we need to add it to the tables columns collection
'
      For Each dr2 As DataRow In ds2.Tables(0).Rows
        Dim ExistsInDatabase1 As Boolean = False
        For Each dr1 As DataRow In ds1.Tables(0).Rows
          If dr2("Name") = dr1("Name") Then
            ExistsInDatabase1 = True
            Exit For
          End If
        Next    
        If ExistsInDatabase1 = False Then
          AnalyseTables(TableName).AnalyseColumns.Add(dr2("Name"), _
                    New AnalyseColumn("Does not exist in Database 1"))
        End If
      Next
  End If
Next
End Sub

完成分析后，它会生成报告。 它首先创建一个流动文档并写入所有发现的差异的详细信息。 完成后，它将流动文档转换为 XPS 固定页面文档，以便它可以添加页眉和页脚。

Private Sub GenerateReport()
    '
    ' Produce a Flow Document containing info on the differences found
    '
    Dim MemStream As New System.IO.MemoryStream
    Dim xpsPackage As Package = Package.Open(MemStream, FileMode.CreateNew)
    Dim FlowDocument As New FlowDocument
    Dim Section As New Section
    Dim Para As Paragraph
    '
    ' Show the databases we have analysed
    '
    Para = New Paragraph
    Section.Blocks.Add(Para)
    Para.FontSize = 12
    Para.Inlines.Add("Paritor Database Compare results for the following databases.")
    Para = New Paragraph
    Section.Blocks.Add(Para)
    Para.FontSize = 12
    Para.Inlines.Add("Database 1:")
    Dim Table As Table
    Dim currentRow As TableRow
    Table = New Table
    Table.Columns.Add(New TableColumn)
    Table.Columns.Add(New TableColumn)
    Table.Columns(0).Width = New GridLength(50)
    Table.FontSize = 10
    Table.RowGroups.Add(New TableRowGroup())
    currentRow = New TableRow()
    Table.RowGroups(0).Rows.Add(currentRow)
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run("Server"))))
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run(Me.txtServer1.Text))))
    currentRow = New TableRow()
    Table.RowGroups(0).Rows.Add(currentRow)
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run("Database"))))
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run(Me.txtDatabase1.Text))))
    Section.Blocks.Add(Table)
    Para = New Paragraph
    Section.Blocks.Add(Para)
    Para.FontSize = 12
    Para.Inlines.Add("Database 2:")
    Table = New Table
    Table.Columns.Add(New TableColumn)
    Table.Columns.Add(New TableColumn)
    Table.Columns(0).Width = New GridLength(50)
    Table.FontSize = 10
    Table.RowGroups.Add(New TableRowGroup())
    currentRow = New TableRow()
    Table.RowGroups(0).Rows.Add(currentRow)
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run("Server"))))
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run(Me.txtServer2.Text))))
    currentRow = New TableRow()
    Table.RowGroups(0).Rows.Add(currentRow)
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run("Database"))))
    currentRow.Cells.Add(New TableCell(New Paragraph(New Run(Me.txtDatabase2.Text))))
    Section.Blocks.Add(Table)
    Para = New Paragraph
    Section.Blocks.Add(Para)
    Para.FontSize = 12
    Para.Inlines.Add("The following tables produced differences")
    '
    ' Pass through the table collection and print details of the differences
    '
    Dim ChangesExists As Boolean = False
    For Each TableName As String In AnalyseTables.Keys
        Dim AnalyseTable As AnalyseTable = AnalyseTables(TableName)
        If AnalyseTable.ExistsInDatabase1 <> _
           AnalyseTable.ExistsInDatabase2 Or AnalyseTable.AnalyseColumns.Count Then
            ChangesExists = True
            Para = New Paragraph
            Section.Blocks.Add(Para)
            Para.FontSize = 14
            Para.Inlines.Add(TableName)
            If AnalyseTable.ExistsInDatabase1 = False Then
                Para = New Paragraph
                Para.FontSize = 10
                Para.Foreground = Brushes.DarkBlue
                Section.Blocks.Add(Para)
                Para.Inlines.Add(" " & _
                             "This table does not exits in database 1")
            End If
            If AnalyseTable.ExistsInDatabase2 = False Then
                Para = New Paragraph
                Para.FontSize = 10
                Para.Foreground = Brushes.DarkBlue
                Section.Blocks.Add(Para)
                Para.Inlines.Add(" " & _
                     "This table does not exits in database 2")
            End If
            For Each ColumnName As String In AnalyseTable.AnalyseColumns.Keys
                Para = New Paragraph
                Section.Blocks.Add(Para)
                Para.FontSize = 10
                Para.Foreground = Brushes.Maroon
                Para.Inlines.Add(" " & ColumnName & " " & _
                   AnalyseTable.AnalyseColumns(ColumnName).Difference)
            Next
        End If
    Next
    If ChangesExists = False Then
        Para = New Paragraph
        Section.Blocks.Add(Para)
        Para.FontSize = 12
        Para.Inlines.Add("No defferences found")
    End If
    FlowDocument.Blocks.Add(Section)
    '
    ' Convert Flowdocument to Fixed Page
    '
    Dim xpsDocument As New XpsDocument(xpsPackage, _
                    CompressionOption.Maximum)
    Dim paginator As DocumentPaginator = _
        CType(FlowDocument, IDocumentPaginatorSource).DocumentPaginator
    Dim rsm As New XpsSerializationManager(New _
                   XpsPackagingPolicy(xpsDocument), False)
    paginator = New DocumentPaginatorWrapper(paginator, _
                New Size(768, 1056), New Size(48, 48))
    rsm.SaveAsXaml(paginator)
    xpsDocument.Close()
    xpsPackage.Close()
    Dim DisplayReport As New DisplayReport
    DisplayReport.OpenStream(MemStream)
    DisplayReport.Show()
End Sub

最后，它调用包含 WPF Document Viewer 控件的第二个窗体，并将文档传递给它以显示。

历史

• 2008 年 1 月 16 日 - 初始版本。