SQL Compare's API does not directly support the creation of HTML reports as the user interface of the program does. By manually generating an XML document, a report can be generated by transforming the XML using the XSL template supplied with the SQL Compare program. This report code examines the differences between databases WidgetStaging and WidgetProduction, generates suitable XML, and converts it to HTML using the template SQLCompareInteractiveReportTemplate.xsl.

'====================================================================
' Save the following as Module1.vb
'====================================================================
Imports RedGate.SQLCompare.Engine
Imports RedGate.Shared.SQL
Imports System.Diagnostics
Module Module1
        Sub Main()
                Using dbSource As New Database(), dbTarget As New Database()
                        'Retrieve the schema information for the two databases
                        Console.WriteLine("Registering databases")
                        dbSource.Register(New ConnectionProperties(".", "WidgetStaging"), Options.Default)
                        dbTarget.Register(New ConnectionProperties(".", "WidgetProduction"), Options.Default)
                        Console.WriteLine("Comparing Databases")
                        Dim dbSourceVsdbTarget As Differences = dbSource.CompareWith(dbTarget, Options.Default)
                        ' Set the filespec for our HTML report
                        Dim ReportOutput As String = "c:\Program files\Red Gate\SQL Compare 7\htmlreport.html"
                        ' Set the XSL template to use for the report. These ship with the SQL Compare software
                        Dim xsltemplate As String = "c:\Program files\Red Gate\SQL Compare 7\SQLCompareInteractiveReportTemplate.xsl"
                        Console.WriteLine("Creating report...")
                        HTMLReport.CreateHtmlReport(ReportOutput, dbSource, dbTarget, dbSourceVsdbTarget, Options.Default, xsltemplate)
                        Console.WriteLine("Finished creating {0}, viewing", ReportOutput)
                        HTMLReport.ViewReport(ReportOutput)
                End Using
                Console.WriteLine("Press any key to continue")
                Console.ReadLine()
        End Sub
End Module
'====================================================================
' EOF Module1.vb
'====================================================================
'====================================================================
' Save the following as HTMLReport.vb
'====================================================================
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Xml
Imports System.Xml.Xsl
Imports System.Text
Imports System.Reflection
Imports RedGate.SQLCompare.Engine
Imports RedGate.Shared.SQL
Imports System.Diagnostics
Imports RedGate.Shared.Utils
Imports System.Collections.Generic
Public Class HTMLReport
        ' This method will create the XML needed for the report and transform it to an HTML page
        ' specified by fileName. It looks in the current folder for the template file.
        ' Please supply the two database objects, the Differences object that you get after a comparison,
        ' and the set of options that you used for the comparison.
        Public Shared Sub CreateHtmlReport(ByVal fileName As String, ByVal dbSourceDatabase As Database,ByVal dbTargetDatabase As Database, ByVal obDatabaseDifferences As Differences, ByVal enOptions AsOptions, ByVal xlstemplate As String)
                Dim tempFile As String = Path.GetTempFileName()
                Dim xslt As XslCompiledTransform = New XslCompiledTransform()
                'Load the XSL template
                Dim xSettings As XsltSettings = New XsltSettings()
                xSettings.EnableScript = True
                xslt.Load(xlstemplate, xSettings, New XmlUrlResolver())
                Try
                        Dim writer As XmlTextWriter = New XmlTextWriter(tempFile, Encoding.Unicode)
                        ' Generate the raw data that will go into the report
                        GenerateXml(writer, dbSourceDatabase, dbTargetDatabase, obDatabaseDifferences, enOptions)
                        writer.Close()
                        xslt.Transform(tempFile, fileName)
                Catch e As Exception
                        Console.WriteLine("Unable to generate html report " + e.Message)
                Finally
                        File.Delete(tempFile)
                End Try
        End Sub
        ''' <summary>
        ''' This is the method that creates the XML data used in the report (SQL Compare v7)
        ''' </summary>
        ''' <param name="writer">XmlTextWriter object</param>
        ''' <param name="dbSourceDatabase">A registered database</param>
        ''' <param name="dbTargetDatabase">The second registered database</param>
        ''' <param name="obDatabaseDifferences">The differences between the two databases</param>
        ''' <param name="m_Options">Set of options used during the comparison process</param>
        Private Shared Sub GenerateXml(ByVal writer As XmlTextWriter, ByVal dbSourceDatabase As Database,ByVal dbTargetDatabase As Database, ByVal obDatabaseDifferences As Differences, ByVal options As Options)
                writer.WriteStartDocument()
                ' Header
                writer.WriteStartElement("comparison")
                writer.WriteAttributeString("direction", "1to2")
                writer.WriteAttributeString("timestamp", DateTime.Now.ToString())
                'Datasources
                writer.WriteStartElement("datasources")
                writer.WriteStartElement("datasource")
                writer.WriteAttributeString("type", "live")
                writer.WriteAttributeString("id", "1")
                writer.WriteStartElement("server")
                writer.WriteString(dbSourceDatabase.ConnectionProperties.ServerName)
                writer.WriteEndElement() ' </server>
                writer.WriteStartElement("database")
                writer.WriteString(dbSourceDatabase.ConnectionProperties.DatabaseName)
                writer.WriteEndElement() ' </database>
                writer.WriteEndElement() ' <datasource[@id=1]>
                'Second database
                writer.WriteStartElement("datasource")
                writer.WriteAttributeString("type", "live")
                writer.WriteAttributeString("id", "2")
                writer.WriteStartElement("server")
                writer.WriteString(dbTargetDatabase.ConnectionProperties.ServerName)
                writer.WriteEndElement() ' </server>
                writer.WriteStartElement("database")
                writer.WriteString(dbTargetDatabase.ConnectionProperties.DatabaseName)
                writer.WriteEndElement() ' </database>
                writer.WriteEndElement() ' </datasource>
                writer.WriteEndElement() ' </datasources>
                'Differences collection
                writer.WriteStartElement("differences")
                Dim d As Difference
                For Each d In obDatabaseDifferences
                        If d.Type = DifferenceType.Equal Then Continue For
                        If d.Selected = False Then Continue For ' do not report equal and nonselected objects
                        writer.WriteStartElement("difference")
                        writer.WriteAttributeString("objecttype", d.DatabaseObjectType.ToString().ToLower())
                        writer.WriteAttributeString("status", d.Type.ToString().Trim().ToLower())
                        writer.WriteAttributeString("fqn", String.Format("{0}-{1}", d.DatabaseObjectType.ToString().ToLower(), d.Name.ToString().ToLower()))
                        Select Case d.Type
                                Case DifferenceType.OnlyIn1
                                        writer.WriteStartElement("object")
                                        writer.WriteAttributeString("owner", d.ObjectIn1.Owner)
                                        writer.WriteAttributeString("id", "1")
                                        writer.WriteString(d.ObjectIn1.FullyQualifiedName)
                                        writer.WriteEndElement()
                                        writer.WriteStartElement("object")
                                        writer.WriteAttributeString("owner", "")
                                        writer.WriteAttributeString("id", "2")
                                        writer.WriteEndElement()
                                Case DifferenceType.OnlyIn2
                                        writer.WriteStartElement("object")
                                        writer.WriteAttributeString("owner", "")
                                        writer.WriteAttributeString("id", "1")
                                        writer.WriteEndElement()
                                        writer.WriteStartElement("object")
                                        writer.WriteAttributeString("owner", d.ObjectIn2.Owner)
                                        writer.WriteAttributeString("id", "2")
                                        writer.WriteString(d.ObjectIn2.FullyQualifiedName)
                                        writer.WriteEndElement()
                                Case Else ' object exists in both
                                        writer.WriteStartElement("object")
                                        writer.WriteAttributeString("owner", d.ObjectIn1.Owner)
                                        writer.WriteAttributeString("id", "1")
                                        writer.WriteString(d.ObjectIn1.FullyQualifiedName)
                                        writer.WriteEndElement()
                                        writer.WriteStartElement("object")
                                        writer.WriteAttributeString("owner", d.ObjectIn2.Owner)
                                        writer.WriteAttributeString("id", "2")
                                        writer.WriteString(d.ObjectIn2.FullyQualifiedName)
                                        writer.WriteEndElement()
                        End Select
                        ' Now we write the actual SQL code for the objects in database 1 and 2
                        ' Since the reordering of lines is copyright code in SQL Compare
                        ' we are going to simply dump the SQL in the order it comes
                        writer.WriteStartElement("comparisonstrings")
                        Dim w As Work = New Work()
                        Dim regions1 As Regions = w.ScriptObject(d.ObjectIn1, options)
                        Dim regions2 As Regions = w.ScriptObject(d.ObjectIn2, options)
                        ' Work out which region is "shortest"
                        Dim regionCount As Integer = regions1.Count
                        Dim oneIsLonger As Boolean = True
                        If regions2.Count > regions1.Count Then
                                regionCount = regions2.Count
                                oneIsLonger = False
                        End If
                        'loop through all SQL regions -- append the longer lines
                        Dim j As Integer = 0
                        For j = 0 To regionCount - 1
                                'Start writing out the lines of SQL code
                                Dim oneHasMoreLines As Boolean = False
                                Dim linesFrom1() As String
                                Dim linesFrom2() As String
                                Try
                                        linesFrom1 = regions1(j).SQL.Split(vbCrLf)
                                Catch a As ArgumentOutOfRangeException ' There are more regions in region2
                                        ReDim Preserve linesFrom1(regions2(j).SQL.Split(vbCrLf).Length)
                                        Dim y As Integer = 0
                                        For y = 0 To linesFrom1.Length - 1
                                                linesFrom1(y) = String.Empty
                                        Next y
                                End Try
                                Try
                                        linesFrom2 = regions2(j).SQL.Split(vbCrLf)
                                Catch a As ArgumentOutOfRangeException ' There are more regions in region1
                                        ReDim Preserve linesFrom2(regions1(j).SQL.Split(vbCrLf).Length)
                                        Dim y As Integer = 0
                                        For y = 0 To linesFrom2.Length - 1
                                                linesFrom2(y) = String.Empty
                                        Next y
                                End Try
                                Dim sqlLineCount As Integer = linesFrom1.Length
                                Dim sqlLineCount2 As Integer = linesFrom2.Length
                                If sqlLineCount > sqlLineCount2 Then
                                        sqlLineCount = sqlLineCount2
                                        oneHasMoreLines = True
                                End If
                                Dim l As Integer = 0
                                For l = 0 To sqlLineCount - 1
                                        writer.WriteStartElement("line")
                                        writer.WriteAttributeString("type", IIf(Not String.Compare(linesFrom1(l), linesFrom2(l), True) = 0, "different", "same"))
                                        ' Dump the line of SQL from db1
                                        writer.WriteStartElement("left")
                                        writer.WriteString(linesFrom1(l).Trim())
                                        writer.WriteEndElement() ' </left>
                                        ' ...and db2
                                        writer.WriteStartElement("right")
                                        writer.WriteString(linesFrom2(l).Trim())
                                        writer.WriteEndElement() ' </right>
                                        writer.WriteEndElement() '</line>
                                Next l
                                'Write out any "leftover" SQL
                                Dim leftoverSql() As String = linesFrom2
                                If oneHasMoreLines = True Then leftoverSql = linesFrom1
                                Dim m As Integer = 0
                                For m = l To leftoverSql.Length - 1
                                        writer.WriteStartElement("line")
                                        writer.WriteAttributeString("type", "different")
                                        writer.WriteStartElement("left")
                                        If oneHasMoreLines = True Then writer.WriteString(leftoverSql(m).Trim())
                                        writer.WriteEndElement() ' </left>
                                        writer.WriteStartElement("right")
                                        If oneHasMoreLines = False Then writer.WriteString(leftoverSql(m).Trim())
                                        writer.WriteEndElement() ' </right>
                                        writer.WriteEndElement() '</line>
                                Next m
                        Next j
                        writer.WriteEndElement() ' </comparisonStrings>
                        writer.WriteEndElement() ' </difference>
                Next
                writer.WriteEndElement() ' </differences>
                writer.WriteEndElement() ' </comparison>
                writer.WriteEndDocument() 'EOF
        End Sub
        ' Feed the .htm file to Windows and let it start the viewer (IE)
        Public Shared Sub ViewReport(ByVal sPath As String)
                If sPath = String.Empty Then Return
                'view the doc
                Try
                        Dim psi As ProcessStartInfo = New ProcessStartInfo(sPath)
                        psi.UseShellExecute = True
                        Process.Start(psi)
                Catch
                End Try
        End Sub
End Class
'====================================================================
' EOF HTMLReport.vb
'====================================================================