Generate report within toolkit

Brian Donahue

Hi Steve,

Like I said in the previous post about this, there is no method on any
of the toolkit objects that will produce a report. This is done in the
actual program's UI layer rather than down in the Engine, so you'd have to
write your own code to produce the report.

Creating an HTML report is probably the easiest option, since less
people understand RTF (and even fewer understand Word!). You have to convert
the database differences to XML, then transform the XML into HTML using an
XSL template.

I've attached an example that you can use to create an HTML report with
the SQLCompare Engine. It uses the XSL file that ships with Red Gate SQL
Bundle, aptly called SQLCompareReportTemplate.xsl', that you will need to
copy into the same folder as your executable.

There are some differences between this HTML report and the commercial
version -- mainly that it does not try to match lines of SQL: it merely
displays the SQL side-by-side. This is because the code for matching the
lines of SQL is proprietary. (Sorry!)

If anyone has any comments about the code, feel free to post them!

Regards,

Brian Donahue
Red Gate Technical Support

Brian Donahue

Here is the report code. This exampines the differences, generates suitable XML, and converts it to HTML using the SQLCompareReportTemplate.XSL template, which must exist in the same directory.

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];
				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