Project Description
As a software product evolves it is inevitable that the underlying database schema changes and you have to make sure any changes you make in the development cycle are made to all other database as the software is distributed. In all our products we include a database management module that propagates changes out to live databases and ensures the version of the software matches the database schema.

In the early stages of development though the schema changes made by designers and developers come fast and furious and it can be difficult keeping track of them and propagating them to other databases. At Paritor we have this problem and find it can be a long winded job comparing databases manually. There are tools out in the marketplace to analyse databases, show differences and even script any necessary changes but apart from the fact that they all cost money they all seem to be a "Sledge Hammer to Crack a Walnut". I therefore sat down the other morning and put together a simple utility to open up two database, analyse the differences and report on what it finds.

I wrote it as a WPF application, mainly because I wanted to use the WPF Document namespace to create the report and show it using the WPF Document Viewer. You could easily write it as a Windows forms and use System.Drawing.Print or even produce a Crystal Report.


pic1.jpg

pic2.jpg

The program requests details of the two databases to compare and opens up a SQL connection to each.

CollapsePrivate 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

It then creates two lists containing the user table names in each database. The following SQL returns the ID and Name of each table.

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

It then makes a pass through each list checking for an entry in the other list. This lets us know where we have a table in one database but not in the other. I created a couple of private classes to hold details of the issues it finds with each table and declare a dictionary list of these.

CollapsePrivate 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



As you can see there is a Dictionary list of the class AnalysisTable which holds details of each table including a dictionary list of the AnalysisColumn class which holds details of each column issue.

CollapsePrivate 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


Once the analysis has completed checking for missing tables if then looks though the AnalysisTable collection and where its recorded the table existing in both databases it build a list of the tables columns. The following SQL provides this.

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

It performs a similar task on these column lists as it did with the table lists i.e. it checks to see if te columns exists in each list and where they do it looks to see that the data type and length are the same.

CollapsePrivate 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


After completing the analysis it generates a report. It first creates a flow document and writes details of all the differences found. When complete it converts the flow document into a XPS Fixed Page document so that it can add a header and footer.

CollapsePrivate 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


Finally it calls a second form that contains a WPF Document Viewer control and passes it the document to display.

Last edited Jan 17, 2008 at 8:34 AM by SimonDutton, version 2