SQL Comparison SDK 10

Help for older versions available.

These pages cover SQL Comparison SDK 10, which is not the latest version. Help for other versions is also available.

Creating an HTML report of schema differences in C#

Page last updated 07 March 2013

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.

  1. //====================================================================
  2. // Save the following as Program.cs
  3. //====================================================================
  4. using System;
  5. using System.Data;
  6. using System.IO;
  7. using RedGate.SQLCompare.Engine; //Reference %programfiles%\Red Gate\SQL Compare 10\RedGate.SQLCompare.Engine.dll
  8. using RedGate.Shared.SQL; // Reference %programfiles%\Red Gate\SQL Compare 10\RedGate.SQL.Shared.dll
  9. using System.Diagnostics; // for ProcessStartInfo
  10. namespace SQLCompareReport
  11. {
  12. /// <summary>
  13. /// Two functions -- CreateHTMLReport and ViewReport -- create an HTML report and optionally view it.
  14. /// </summary>
  15. class Program
  16. {
  17. static void Main(string[] args)
  18. {
  19. using (Database dbSource = new Database(),
  20. dbTarget = new Database())
  21. {
  22. // Retrieve the schema information for the two databases
  23. Console.WriteLine("Registering databases");
  24. dbSource.Register(new ConnectionProperties(".", "WidgetStaging"), Options.Default);
  25. dbTarget.Register(new ConnectionProperties(".", "WidgetProduction"), Options.Default);
  26. Console.WriteLine("Comparing Databases");
  27. Differences dbSourceVsdbTarget = dbSource.CompareWith(dbTarget, Options.Default);
  28. // Set the filespec for our HTML report
  29. string ReportOutput = @"c:\Program files\Red Gate\SQL Compare 7\htmlreport.html";
  30. // Set the XSL template to use for the report. These ship with the SQL Compare software
  31. string xsltemplate = @"c:\Program files\Red Gate\SQL Compare 7\SQLCompareInteractiveReportTemplate.xsl";
  32. Console.WriteLine("Creating report...");
  33. HTMLReport.CreateHtmlReport(ReportOutput, dbSource, dbTarget, dbSourceVsdbTarget, Options.Default,xsltemplate);
  34. Console.WriteLine("Finished creating {0}, viewing", ReportOutput);
  35. HTMLReport.ViewReport(ReportOutput);
  36. }
  37. Console.WriteLine("Press any key to continue");
  38. Console.ReadLine();
  39. }
  40. }
  41. }
  42. //====================================================================
  43. // EOF Program.cs
  44. //====================================================================
  45. //====================================================================
  46. // Save the following as HTMLReport.cs
  47. //====================================================================
  48. using System;
  49. using System.Collections;
  50. using System.Data;
  51. using System.IO;
  52. using System.Xml;
  53. using System.Xml.Xsl;
  54. using System.Text;
  55. using System.Reflection;
  56. using RedGate.SQLCompare.Engine;
  57. using RedGate.Shared.SQL;
  58. using System.Diagnostics;
  59. using RedGate.Shared.Utils;
  60. using System.Collections.Generic; // for ProcessStartInfo
  61. namespace SQLCompareReport
  62. {
  63. public class HTMLReport
  64. {
  65. // This method will create the XML needed for the report and transform it to an HTML page
  66. // specified by fileName. It looks in the current folder for the template file.
  67. // Please supply the two database objects, the Differences object that you get after a comparison,
  68. // and the set of options that you used for the comparison.
  69. 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)
  70. {
  71. string tempFile = Path.GetTempFileName();
  72. XslCompiledTransform xslt = new XslCompiledTransform();
  73.         //Load the XSL template
  74.         XsltSettings xSettings = new XsltSettings();
  75.         xSettings.EnableScript = true;
  76.         xslt.Load(xlstemplate, xSettings, new XmlUrlResolver());
  77.         try
  78.         {
  79.                 XmlTextWriter writer = new XmlTextWriter(tempFile, Encoding.Unicode);
  80.                 //Generate the raw data that will go into the report
  81.                 GenerateXml(writer, dbSourceDatabase, dbTargetDatabase, obDatabaseDifferences, enOptions);
  82.                 writer.Close();
  83.                 xslt.Transform(tempFile, fileName);
  84.         }
  85.         catch (Exception e)
  86.         {
  87.                 Console.WriteLine("Unable to generate html report " + e.Message);
  88.         }
  89.         finally
  90.         {
  91.                 File.Delete(tempFile);
  92.         }
  93.   }
  94. /// <summary>
  95. /// This is the method that creates the XML data used in the report (SQL Compare v7)
  96. /// </summary>
  97. /// <param name="writer">XmlTextWriter object</param>
  98. /// <param name="dbSourceDatabase">A registered database</param>
  99. /// <param name="dbTargetDatabase">The second registered database</param>
  100. /// <param name="obDatabaseDifferences">The differences between the two databases</param>
  101. /// <param name="m_Options">Set of options used during the comparison process</param>
  102. 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)
  103. {
  104.         writer.WriteStartDocument();
  105.         //Header
  106.         writer.WriteStartElement("comparison");
  107.         writer.WriteAttributeString("direction", "1to2");
  108.         writer.WriteAttributeString("timestamp", DateTime.Now.ToString());
  109.         //Datasources
  110.         writer.WriteStartElement("datasources");
  111.         writer.WriteStartElement("datasource");
  112.         writer.WriteAttributeString("type", "live");
  113.         writer.WriteAttributeString("id", "1");
  114.         writer.WriteStartElement("server");
  115.         writer.WriteString(dbSourceDatabase.ConnectionProperties.ServerName);
  116.         writer.WriteEndElement(); // </server>
  117.         writer.WriteStartElement("database");
  118.         writer.WriteString(dbSourceDatabase.ConnectionProperties.DatabaseName);
  119.         writer.WriteEndElement(); // </database>
  120.         writer.WriteEndElement(); // <datasource[@id=1]>
  121.         //Second database
  122.         writer.WriteStartElement("datasource");
  123.         writer.WriteAttributeString("type", "live");
  124.         writer.WriteAttributeString("id", "2");
  125.         writer.WriteStartElement("server");
  126.         writer.WriteString(dbTargetDatabase.ConnectionProperties.ServerName);
  127.         writer.WriteEndElement(); // </server>
  128.         writer.WriteStartElement("database");
  129.         writer.WriteString(dbTargetDatabase.ConnectionProperties.DatabaseName);
  130.         writer.WriteEndElement(); // </database>
  131.         writer.WriteEndElement(); // </datasource>
  132.         writer.WriteEndElement(); // </datasources>
  133.         //Differences collection
  134.         writer.WriteStartElement("differences");
  135.         foreach (Difference d in obDatabaseDifferences)
  136.         {
  137.         if (d.Type == DifferenceType.Equal)
  138.                 continue;
  139.         if (!d.Selected)
  140.                 continue;
  141.                 writer.WriteStartElement("difference");
  142.                 writer.WriteAttributeString("objecttype", d.DatabaseObjectType.ToString().ToLower());
  143.                 writer.WriteAttributeString("status", d.Type.ToString().Trim().ToLower());
  144.                 writer.WriteAttributeString("fqn", String.Format("{0}-{1}",d.DatabaseObjectType.ToString().ToLower(),d.Name.ToString().ToLower()));
  145.                 switch (d.Type){
  146.                         case DifferenceType.OnlyIn1:
  147.                                 writer.WriteStartElement("object");
  148.                                 writer.WriteAttributeString("owner", d.ObjectIn1.Owner);
  149.                                 writer.WriteAttributeString("id", "1");
  150.                                 writer.WriteString(d.ObjectIn1.FullyQualifiedName);
  151.                                 writer.WriteEndElement();
  152.                                 writer.WriteStartElement("object");
  153.                                 writer.WriteAttributeString("owner","");
  154.                                 writer.WriteAttributeString("id", "2");
  155.                                 writer.WriteEndElement();
  156.                         break;
  157.                         case DifferenceType.OnlyIn2:
  158.                                 writer.WriteStartElement("object");
  159.                                 writer.WriteAttributeString("owner", "");
  160.                                 writer.WriteAttributeString("id", "1");
  161.                                 writer.WriteEndElement();
  162.                                 writer.WriteStartElement("object");
  163.                                 writer.WriteAttributeString("owner", d.ObjectIn2.Owner);
  164.                                 writer.WriteAttributeString("id", "2");
  165.                                 writer.WriteString(d.ObjectIn2.FullyQualifiedName);
  166.                                 writer.WriteEndElement();
  167.                         break;
  168.                         default: // object exists in both
  169.                                 writer.WriteStartElement("object");
  170.                                 writer.WriteAttributeString("owner", d.ObjectIn1.Owner);
  171.                                 writer.WriteAttributeString("id", "1");
  172.                                 writer.WriteString(d.ObjectIn1.FullyQualifiedName);
  173.                                 writer.WriteEndElement();
  174.                                 writer.WriteStartElement("object");
  175.                                 writer.WriteAttributeString("owner", d.ObjectIn2.Owner);
  176.                                 writer.WriteAttributeString("id", "2");
  177.                                 writer.WriteString(d.ObjectIn2.FullyQualifiedName);
  178.                                 writer.WriteEndElement();
  179.                         break;
  180.         }
  181.         // Now we write the actual SQL code for the objects in database 1 and 2
  182.         // Since the reordering of lines is copyright code in SQL Compare
  183.         // we are going to simply dump the SQL in the order it comes
  184.                 writer.WriteStartElement("comparisonstrings");
  185.                 Work w = new Work();
  186.                 Regions regions1 = w.ScriptObject(d.ObjectIn1, options);
  187.                 Regions regions2 = w.ScriptObject(d.ObjectIn2, options);
  188.                 // Work out which region is "shortest"
  189.                 int regionCount=regions1.Count;
  190.                 bool oneIsLonger=true;
  191.                 if (regions2.Count>regions1.Count)
  192.                 {
  193.                         regionCount=regions2.Count;
  194.                         oneIsLonger=false;
  195.                 }
  196.                 //loop through all SQL regions -- append the longer lines
  197.                 int j = 0;
  198.                 for (j=0; j < regioncount;="">
  199.                 {
  200.                         //Start writing out the lines of SQL code
  201.                         bool oneHasMoreLines = false;
  202.                         string[] linesFrom1;
  203.                         string[] linesFrom2;
  204.                         try
  205.                         {
  206.                                 linesFrom1 = regions1[j].SQL.Split('\n');
  207.                         }
  208.                         catch (ArgumentOutOfRangeException) // There are more regions in region2
  209.                         {
  210.                                 linesFrom1 = new string[regions2[j].SQL.Split('\n').Length];
  211.                                 for (int y = 0; y < linesfrom1.length;="">
  212.                                 {
  213.                                         linesFrom1[y] = String.Empty;
  214.                                 }
  215.                         }
  216.                         try
  217.                         {
  218.                                 linesFrom2 = regions2[j].SQL.Split('\n');
  219.                         }
  220.                         catch (ArgumentOutOfRangeException) // There are more regions in region1
  221.                         {
  222.                                 linesFrom2 = new string[regions1[j].SQL.Split('\n').Length];
  223.                                 for(int y=0;y<>
  224.                                 {
  225.                                         linesFrom2[y] = String.Empty;
  226.                                 }
  227.                         }
  228.                         int sqlLineCount = linesFrom1.Length;
  229.                         int sqlLineCount2 = linesFrom2.Length;
  230.                         if (sqlLineCount > sqlLineCount2)
  231.                         {
  232.                                 sqlLineCount = sqlLineCount2;
  233.                                 oneHasMoreLines = true;
  234.                         }
  235.                         int l=0;
  236.                         for (; l <>
  237.                         {
  238.                                 writer.WriteStartElement("line");
  239.                                 writer.WriteAttributeString("type", String.Compare(linesFrom1[l],linesFrom2[l], true) != 0 ?"different" : "same");
  240.                                 // Dump the line of SQL from db1
  241.                                 writer.WriteStartElement("left");
  242.                                 writer.WriteString(linesFrom1[l].Trim());
  243.                                 writer.WriteEndElement(); // </left>
  244.                                 // ...and db2
  245.                                 writer.WriteStartElement("right");
  246.                                 writer.WriteString(linesFrom2[l].Trim());
  247.                                 writer.WriteEndElement(); // </right>
  248.                                 writer.WriteEndElement(); //</line>
  249.                         }
  250.                         // Write out any "leftover" SQL
  251.                         string[] leftoverSql=linesFrom2;
  252.                         if (oneHasMoreLines) leftoverSql=linesFrom1;
  253.                         for (int m = l; m < leftoversql.length;="">
  254.                         {
  255.                                 writer.WriteStartElement("line");
  256.                                 writer.WriteAttributeString("type", "different");
  257.                                 writer.WriteStartElement("left");
  258.                                 if (oneHasMoreLines) writer.WriteString(leftoverSql[m].Trim());
  259.                                 writer.WriteEndElement(); // </left>
  260.                                 writer.WriteStartElement("right");
  261.                                 if (!oneHasMoreLines) writer.WriteString(leftoverSql[m].Trim());
  262.                                 writer.WriteEndElement(); // </right>
  263.                                 writer.WriteEndElement(); //</line>
  264.                         }
  265.                 }
  266.                 writer.WriteEndElement(); // </comparisonStrings>
  267.                 writer.WriteEndElement(); // </difference>
  268.         }
  269.         writer.WriteEndElement(); // </differences>
  270.         writer.WriteEndElement(); // </comparison>
  271.         writer.WriteEndDocument(); //EOF
  272. }
  273. //Feed the .htm file to Windows and let it start the viewer (IE)
  274. public static void ViewReport(string sPath)
  275. {
  276. if (sPath == string.Empty)
  277. return;
  278. // view the doc
  279. try
  280. {
  281. ProcessStartInfo psi = new ProcessStartInfo(sPath);
  282. psi.UseShellExecute = true;
  283. Process.Start(psi);
  284. }
  285. catch { }
  286. }
  287. } //end class
  288. }
  289. //====================================================================
  290. // EOF HTMLReport.cs
  291. //====================================================================

Didn't find what you were looking for?

Product Articles

Tips and how-to guides for Redgate products

University

Easy to follow video courses

Community Forums

Ask, discuss, and solve questions about Redgate's tools

Events & Friends

Meet us at an event, get sponsored, and join our Friends of Redgate

Simple Talk

In-depth articles and opinion from Redgate's technical journal

Cookies on red-gate.com

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy