Reporting Schema Differences

From RedGateWiki
Jump to: navigation, search

Back to SQL Comparison SDK

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.


Assembly references:

  • RedGate.Shared.SQL.dll
  • RedGate.Shared.Utils.dll
  • RedGate.SQLCompare.Engine.dll

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