STK 3 - Including an HTML report in a Toolkit project

Brian Donahue

• Date: 17 Sep 2004
• Versions affected: 3.x

In SQL Compare, you can generate a report of the differences in a database schema in HTML format. Since this code exists in the GUI portion of the SQL Compare software rather than in RedGate.SQLCompare.Engine.dll, there is no method of producing an HTML report from a Toolkit application other than coding the report yourself.

You can use the following C# code to generate a report in your project. Please read the comments carefully!

using System;
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.SQL.Shared;
using System.Diagnostics; // for ProcessStartInfo

namespace SQLCompareReport
{
	/// <summary>
	/// Two functions -- CreateHTMLReport and ViewReport -- create an HTML report and optionally view it.
	/// Support functions GenerateXml and CapElement are needed by CreateHTMLReport
	/// </summary>
	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.

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 tempFile = Path.GetTempFileName();
			XslTransform xslt = new XslTransform();
			xslt.Load(Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase), "SqlCompareReportTemplate.xsl"));

			try
			{
				XmlTextWriter writer = new XmlTextWriter(tempFile, Encoding.Unicode);
				GenerateXml(writer, dbSourceDatabase, dbTargetDatabase, obDatabaseDifferences, enOptions);
				writer.Close();
				xslt.Transform(tempFile, fileName);
				return;
			}
			catch(Exception e)
			{
				Console.WriteLine("Unable to generate html report " + e.Message);
				return;
			}
			finally
			{
				File.Delete(tempFile);
			}
		}
		static void GenerateXml(XmlTextWriter writer, RedGate.SQLCompare.Engine.Database dbSourceDatabase, RedGate.SQLCompare.Engine.Database dbTargetDatabase, RedGate.SQLCompare.Engine.Differences obDatabaseDifferences, RedGate.SQLCompare.Engine.Options enOptions)
		{
			writer.WriteStartElement("root");
			writer.WriteStartElement("databases");
			string line1 = string.Format("Databases compared on {0}", DateTime.Now.ToString());
			writer.WriteElementString("timestamp", line1);

			writer.WriteStartElement("database");
			writer.WriteAttributeString("id", "1");

			writer.WriteElementString("servername", dbSourceDatabase.ConnectionProperties.ServerName);
			writer.WriteElementString("databasename", dbSourceDatabase.ConnectionProperties.DatabaseName);
			
			writer.WriteElementString("description", "Database 1 - " +dbSourceDatabase.ConnectionProperties.ServerName+ "/" +dbSourceDatabase.ConnectionProperties.DatabaseName);
			
			writer.WriteEndElement();

			writer.WriteStartElement("database");
			writer.WriteAttributeString("id", "2");
			writer.WriteElementString("servername", dbTargetDatabase.ConnectionProperties.ServerName);
			writer.WriteElementString("databasename", dbTargetDatabase.ConnectionProperties.DatabaseName);
			
			writer.WriteElementString("description", "Database 2 - " +dbTargetDatabase.ConnectionProperties.ServerName + "/" + dbTargetDatabase.ConnectionProperties.DatabaseName);
			writer.WriteEndElement();

			// databases
			writer.WriteEndElement();
			// Start writing the database differences.
			writer.WriteStartElement("differences");
			
			for(int i = 0; i <= obDatabaseDifferences.Count-1; i++)
			{
				Difference diff = (Difference)obDatabaseDifferences[i];
/* Note: to filter the report to show only selected objects that are different, change the following line to:
if (diff.Selected == true && !diff.ObjectIn1.Equals(diff.ObjectIn2)) */

				if (diff.Selected == true) // only report selected objects!
				{					
					writer.WriteStartElement("difference");
					writer.WriteAttributeString("id", i.ToString());

					writer.WriteStartElement("name");
					writer.WriteString(diff.Name);
					writer.WriteEndElement();

					writer.WriteStartElement("objecttype");
					writer.WriteString(diff.DatabaseObjectType.ToString());
					writer.WriteEndElement();

					int existsin1 = -1;
					if(diff.Type == DifferenceType.OnlyIn2)
						existsin1 = 0;

					writer.WriteStartElement("existsin1");
					writer.WriteString(existsin1.ToString());
					writer.WriteEndElement();
					
					int existsin2 = -1;
					if(diff.Type == DifferenceType.OnlyIn1)
						existsin2 = 0;

					writer.WriteStartElement("existsin2");
					writer.WriteString(existsin2.ToString());
					writer.WriteEndElement();


					writer.WriteStartElement("action");
					writer.WriteString(diff.Type.ToString());
					writer.WriteEndElement();
					//Begin bit to show the SQL
					writer.WriteStartElement("comparestrings");
					Work w = new Work();
								
					Regions regions1 = w.ScriptObject(diff.ObjectIn1, enOptions);
					Regions regions2 = w.ScriptObject(diff.ObjectIn2, enOptions);
				
					string[] string1;
					string[] string2;
					int iCounter = 0;
					int iCommonLength=0;

					string1 = regions1.ToString().Split('\n');
					string2 = regions2.ToString().Split('\n');
					// Get the length of the shortest SQL block
					// Compare the shortest number of lines
					// to prevent an index out-of-range error
					if (string1.Length < string2.Length) iCommonLength = string1.Length;
					else iCommonLength = string2.Length;
				
					while (iCounter < iCommonLength) 
					{
						writer.WriteStartElement("comparestring");
						writer.WriteStartElement("action");
						if (string1[iCounter].Equals(string2[iCounter])) writer.WriteString("equal");
						else writer.WriteString("different");
						CapElement(writer, string1[iCounter], string2[iCounter]);
						iCounter++;
					}
					//fill out the longest SQL block
					if (string1.Length > string2.Length) 
					{
						while (iCounter < string1.Length) 
						{
							writer.WriteStartElement("comparestring");
							writer.WriteStartElement("action");
							writer.WriteString("leftonly");
							CapElement(writer, string1[iCounter], "");
							iCounter++;
						}
					}
					if (string1.Length < string2.Length) 
					{
						while (iCounter < string2.Length) 
						{
							writer.WriteStartElement("comparestring");
							writer.WriteStartElement("action");
							writer.WriteString("rightonly");
							CapElement(writer, "", string2[iCounter]);
							iCounter++;
						}
					}
								
					writer.WriteEndElement(); // end comparestrings tag
					writer.WriteEndElement(); // end difference tag
				}
			} //end if diff.Selected == true
				writer.WriteEndElement(); // end differences tag
				writer.WriteEndElement(); // end root tag
			
			writer.Close();
			
		}
		// This function will write the comparestring and close
		// the XML tag -- needed by GenerateXml()
		static void CapElement(XmlTextWriter writer, string left, string right) 
		{
			writer.WriteEndElement(); // end action
			writer.WriteStartElement("leftstring");
			writer.WriteString(left);
			writer.WriteEndElement();
			writer.WriteStartElement("rightstring");
			writer.WriteString(right);
			writer.WriteEndElement(); // end rightstring
			writer.WriteEndElement(); // end comparestring
		}
		
		//Feed the .htm file to Windows and let it start the viewer (IE)
		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
	} //end NameSpace

Brian Donahue

This functionality is now available using the command-line tools supplied with SQL Toolkit. To produce a SQL Compare report, simply call SQLCOMPARE.exe with the /r option.

Please see the SQL Comparison and Synchronization Toolkit documentation for more information on the command-line tool usage.