Creating an HTML report of schema differences in C#

Category: How do I?
Date: 20 Sep 2012
Product: SQL Comparison SDK
Versions: 7.x

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.

[C#]
//====================================================================
// Save the following as Program.cs
//====================================================================
using System;
using System.Data;
using System.IO;
using RedGate.SQLCompare.Engine; //Reference %programfiles%\Red Gate\SQL Compare 6\RedGate.SQLCompare.Engine.dll
using RedGate.Shared.SQL; // Reference %programfiles%\Red Gate\SQL Compare 6\RedGate.SQL.Shared.dll
using System.Diagnostics; // for ProcessStartInfo
namespace SQLCompareReport
{
/// <summary>
/// Two functions -- CreateHTMLReport and ViewReport -- create an HTML report and optionally view it.
/// </summary>
class Program
{
static void Main(string[] args)
{
using (Database dbSource = new Database(),
dbTarget = 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");
Differences dbSourceVsdbTarget = dbSource.CompareWith(dbTarget, Options.Default);
// Set the filespec for our HTML report
string ReportOutput = @"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
string xsltemplate = @"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);
}
Console.WriteLine("Press any key to continue");
Console.ReadLine();
}
}
}
//====================================================================
// EOF Program.cs
//====================================================================
//====================================================================
// Save the following as HTMLReport.cs
//====================================================================
using System;
using System.Collections;
using System.Data;
using System.IO;
using System.Xml;
using System.Xml.Xsl;
using System.Text;
using System.Reflection;
using RedGate.SQLCompare.Engine;
using RedGate.Shared.SQL;
using System.Diagnostics;
using RedGate.Shared.Utils;
using System.Collections.Generic; // for ProcessStartInfo
namespace SQLCompareReport
{
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 static void CreateHtmlReport(string fileName, RedGate.SQLCompare.Engine.Database dbSourceDatabase, RedGate.SQLCompare.Engine.Database dbTargetDatabase, RedGate.SQLCompare.Engine.Differences obDatabaseDifferences, RedGate.SQLCompare.Engine.Options enOptions,string xlstemplate)
{
string tempFile = Path.GetTempFileName();
XslCompiledTransform xslt = new XslCompiledTransform();
//Load the XSL template
XsltSettings xSettings = new XsltSettings();
xSettings.EnableScript = true;
xslt.Load(xlstemplate, xSettings, new XmlUrlResolver());
try
{
XmlTextWriter writer = 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 (Exception e)
{
Console.WriteLine("Unable to generate html report " + e.Message);
}
finally
{
File.Delete(tempFile);
}
}
/// <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 static void GenerateXml(XmlTextWriter writer, RedGate.SQLCompare.Engine.Database dbSourceDatabase, RedGate.SQLCompare.Engine.Database dbTargetDatabase, RedGate.SQLCompare.Engine.Differences obDatabaseDifferences, RedGate.SQLCompare.Engine.Options 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");
foreach (Difference d in obDatabaseDifferences)
{
if (d.Type == DifferenceType.Equal)
continue;
if (!d.Selected)
continue;
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()));
switch (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();
break;
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();
break;
default: // 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();
break;
}
// 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");
Work w = new Work();
Regions regions1 = w.ScriptObject(d.ObjectIn1, options);
Regions regions2 = w.ScriptObject(d.ObjectIn2, options);
// Work out which region is "shortest"
int regionCount=regions1.Count;
bool oneIsLonger=true;
if (regions2.Count>regions1.Count)
{
regionCount=regions2.Count;
oneIsLonger=false;
}
//loop through all SQL regions -- append the longer lines
int j = 0;
for (j=0; j < regioncount;="">
{
//Start writing out the lines of SQL code
bool oneHasMoreLines = false;
string[] linesFrom1;
string[] linesFrom2;
try
{
linesFrom1 = regions1[j].SQL.Split('\n');
}
catch (ArgumentOutOfRangeException) // There are more regions in region2
{
linesFrom1 = new string[regions2[j].SQL.Split('\n').Length];
for (int y = 0; y < linesfrom1.length;="">
{
linesFrom1[y] = String.Empty;
}
}
try
{
linesFrom2 = regions2[j].SQL.Split('\n');
}
catch (ArgumentOutOfRangeException) // There are more regions in region1
{
linesFrom2 = new string[regions1[j].SQL.Split('\n').Length];
for(int y=0;y<>
{
linesFrom2[y] = String.Empty;
}
}
int sqlLineCount = linesFrom1.Length;
int sqlLineCount2 = linesFrom2.Length;
if (sqlLineCount > sqlLineCount2)
{
sqlLineCount = sqlLineCount2;
oneHasMoreLines = true;
}
int l=0;
for (; l <>
{
writer.WriteStartElement("line");
writer.WriteAttributeString("type", 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>
}
// Write out any "leftover" SQL
string[] leftoverSql=linesFrom2;
if (oneHasMoreLines) leftoverSql=linesFrom1;
for (int m = l; m < leftoversql.length;="">
{
writer.WriteStartElement("line");
writer.WriteAttributeString("type", "different");
writer.WriteStartElement("left");
if (oneHasMoreLines) writer.WriteString(leftoverSql[m].Trim());
writer.WriteEndElement(); // </left>
writer.WriteStartElement("right");
if (!oneHasMoreLines) writer.WriteString(leftoverSql[m].Trim());
writer.WriteEndElement(); // </right>
writer.WriteEndElement(); //</line>
}
}
writer.WriteEndElement(); // </comparisonStrings>
writer.WriteEndElement(); // </difference>
}
writer.WriteEndElement(); // </differences>
writer.WriteEndElement(); // </comparison>
writer.WriteEndDocument(); //EOF
}
//Feed the .htm file to Windows and let it start the viewer (IE)
public static void ViewReport(string sPath)
{
if (sPath == string.Empty)
return;
// view the doc
try
{
ProcessStartInfo psi = new ProcessStartInfo(sPath);
psi.UseShellExecute = true;
Process.Start(psi);
}
catch { }
}
} //end class
}
//====================================================================
// EOF HTMLReport.cs
//====================================================================
[/C#]