Scripts Folder

From RedGateWiki
Jump to: navigation, search

Back to SQL Comparison SDK

This code demonstrates how to synchronize data from a SQL Server database into an empty folder containing SQL scripts. First, backup the example database "WidgetDev" because in order to synchronize the data, a schema has to be created in the scripts folder and this example takes that schema from the WidgetDev backup file.

C#

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data.SqlClient;
  4. using System.IO;
  5. using System.Text;
  6. using RedGate.Shared.SQL.ExecutionBlock;
  7. using RedGate.Shared.Utils;
  8. using RedGate.SQLCompare.Engine;
  9. using RedGate.SQLCompare.Engine.ReadFromFolder;
  10. using RedGate.SQLDataCompare.Engine;
  11. using RedGate.SQLDataCompare.Engine.ResultsStore;
  12.  
  13. namespace SQLDataCompareCodeSnippets
  14. {
  15.         /// <summary>
  16.         /// Create an empty (schema only) script folder, synchronize static data into it
  17.         /// </summary>
  18.         public class StaticDataWithScriptsExample
  19.         {
  20.                 public void RunExample()
  21.                 {
  22.                         // Constant information for the test
  23.                         // To run this test alter these settings to reflect local configuration
  24.                         const string ServerNameToScript = @".";
  25.                         const string DatabaseNameToScript = @"WidgetDevScripts";
  26.                         string[] StaticDataTableNames = new string[]
  27.                                                                                                                                         {
  28.                                                                                                                                                 "Widgets",
  29.                                                                                                                                                 "WidgetDescriptions",
  30.                                                                                                                                                 "WidgetPrices"
  31.                                                                                                                                         };
  32.                         const string UserName = "";
  33.                         const string Password = "";
  34.                         bool UseIntegratedAuthentication = String.IsNullOrEmpty(UserName);
  35.                         const string ScriptFolderPath = @"WidgetDev";
  36.                         const string BackupPath = @"WidgetDev.bak";
  37.                         const bool CleanUpAfterExecution = true;
  38.  
  39.                         // First restore the Backup of WidgetDev
  40.                         Console.WriteLine(String.Format("RESTORE DATABASE - Creating new database {0}.{1} from {2}", ServerNameToScript, DatabaseNameToScript, BackupPath));
  41.  
  42.                         if (!RestoreDatabase(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, UserName, Password, BackupPath))
  43.                         {
  44.                                 // Couldn't restore database
  45.                                 Console.WriteLine("Database couldn't be restored.");
  46.                                 return;
  47.                         }
  48.  
  49.                         Console.WriteLine("END RESTORE DATABASE");
  50.  
  51.                         // Create the script folder containing the schema information
  52.                         // Uses SQL Compare
  53.  
  54.                         Console.WriteLine("BEGIN - Create Schema Scripts");
  55.                         Console.WriteLine(String.Format("From {0}.{1} -> {2}", ServerNameToScript, DatabaseNameToScript, ScriptFolderPath));
  56.  
  57.                         if (!CreateSchemaScripts(
  58.                                                                                 ServerNameToScript,
  59.                                                                                 DatabaseNameToScript,
  60.                                                                                 UseIntegratedAuthentication,
  61.                                                                                 UserName,
  62.                                                                                 Password,
  63.                                                                                 ScriptFolderPath
  64.                                                                         ))
  65.                         {
  66.                                 // Couldn't create schema scripts
  67.                                 Console.WriteLine("Schema scripts couldn't be created.");
  68.                                 return;
  69.                         }
  70.  
  71.                         Console.WriteLine("END - Create Schema Scripts");
  72.  
  73.                         Console.WriteLine(String.Format("BEGIN - Synchronize Static Data into Scripts Folder {0} ", ScriptFolderPath));
  74.  
  75.                         if (!SynchronizeStaticDataIntoScriptFolder(
  76.                                                                                 ServerNameToScript,
  77.                                                                                 DatabaseNameToScript,
  78.                                                                                 UseIntegratedAuthentication,
  79.                                                                                 UserName,
  80.                                                                                 Password,
  81.                                                                                 ScriptFolderPath,
  82.                                                                                 StaticDataTableNames))
  83.                         {
  84.                                 // Couldn't synchronize static data into Script Folder
  85.                                 Console.WriteLine("Couldn't Synchronize Static Data into Scripts Folder.");
  86.                                 return;
  87.                         }
  88.  
  89.                         Console.WriteLine("END - Synchronize Static Data into Scripts Folder");
  90.  
  91.                         //Perform execution cleanup
  92.                         if (CleanUpAfterExecution)
  93.                         {
  94.                                 Console.WriteLine("BEGIN CLEANUP");
  95.  
  96.                                 Cleanup(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, UserName, Password,
  97.                                                 ScriptFolderPath);
  98.  
  99.                                 Console.WriteLine("CLEANUP COMPLETE");
  100.                         }
  101.                 }
  102.                 /// <summary>
  103.                 /// Cleans up after the running of this program
  104.                 /// </summary>
  105.                 /// <param name="sServerName">SQL Server name</param>
  106.                 /// <param name="sDatabaseName">Database name</param>
  107.                 /// <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
  108.                 /// <param name="sUserName">Username or null if using Integrated Authentication</param>
  109.                 /// <param name="sPassword">Password or null if using Integrated Authentication</param>
  110.                 /// <param name="sScriptFolderPath">Destination of the Script Folder</param>
  111.                 /// <returns></returns>
  112.                 private static void Cleanup(
  113.                         string sServerName,
  114.                         string sDatabaseName,
  115.                         bool bUseIntegratedAuthentication,
  116.                         string sUserName,
  117.                         string sPassword,
  118.                         string sScriptFolderPath)
  119.                 {
  120.                         try
  121.                         {
  122.                                 // Check whether the destination directory exists
  123.                                 if (Directory.Exists(sScriptFolderPath))
  124.                                 {
  125.                                         Directory.Delete(sScriptFolderPath, true);
  126.                                 }
  127.                         }
  128.                         catch (Exception ex)
  129.                         {
  130.                                 LogException(ex);
  131.                         }
  132.  
  133.                         try
  134.                         {
  135.                                 // Connect to SQL Server
  136.                                 ConnectionProperties connProp = CreateConnectionProperties(sServerName, "master",
  137.                                                                                                                                                       bUseIntegratedAuthentication, sUserName,
  138.                                                                                                                                                       sPassword);
  139.                                 using (SqlConnection sqlConn = new SqlConnection(connProp.ConnectionString))
  140.                                 {
  141.                                         sqlConn.Open();
  142.  
  143.                                         // Drop the database we created
  144.                                         using (SqlCommand sqlCommand = sqlConn.CreateCommand())
  145.                                         {
  146.                                                 sqlCommand.CommandTimeout = 5000;
  147.                                                 sqlCommand.CommandText = String.Format("DROP DATABASE {0}", sDatabaseName);
  148.                                                 sqlCommand.ExecuteNonQuery();
  149.                                         }
  150.                                 }
  151.                         }
  152.                         catch (Exception ex)
  153.                         {
  154.                                 LogException(ex);
  155.                         }
  156.                 }
  157.                 /// <summary>
  158.                 /// Restore the database from the specified backup
  159.                 /// </summary>
  160.                 /// <param name="sServerName">SQL Server name</param>
  161.                 /// <param name="sDatabaseName">Database name</param>
  162.                 /// <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
  163.                 /// <param name="sUserName">Username or null if using Integrated Authentication</param>
  164.                 /// <param name="sPassword">Password or null if using Integrated Authentication</param>
  165.                 /// <param name="sBackupToRestore">Backup file to restore</param>
  166.                 /// <returns></returns>
  167.                 private static bool RestoreDatabase(
  168.                         string sServerName,
  169.                         string sDatabaseName,
  170.                         bool bUseIntegratedAuthentication,
  171.                         string sUserName,
  172.                         string sPassword,
  173.                         string sBackupToRestore)
  174.                 {
  175.                         try
  176.                         {
  177.                                 if (!File.Exists(sBackupToRestore))
  178.                                 {
  179.                                         Console.WriteLine(String.Format("ERROR: Backup file {0} doesn't exist.", sBackupToRestore));
  180.                                         return false;
  181.                                 }
  182.                         }
  183.                         catch (Exception ex)
  184.                         {
  185.                                 LogException(ex);
  186.                                 return false;
  187.                         }
  188.  
  189.                         try
  190.                         {
  191.                                 // Connect to SQL Server
  192.                                 ConnectionProperties connProp = CreateConnectionProperties(sServerName, "master",
  193.                                                                                                                                                       bUseIntegratedAuthentication, sUserName,
  194.                                                                                                                                                       sPassword);
  195.                                 using (SqlConnection sqlConn = new SqlConnection(connProp.ConnectionString))
  196.                                 {
  197.                                         sqlConn.Open();
  198.  
  199.                                         // be optomistic about the drop command, database may not exist
  200.                                         try
  201.                                         {
  202.                                                 using (SqlCommand sqlCommand = sqlConn.CreateCommand())
  203.                                                 {
  204.                                                         sqlCommand.CommandTimeout = 5000;
  205.                                                         sqlCommand.CommandText = String.Format("DROP DATABASE {0}", sDatabaseName);
  206.                                                         sqlCommand.ExecuteNonQuery();
  207.                                                 }
  208.                                         }
  209.                                         catch (Exception)
  210.                                         {
  211.                                         }
  212.  
  213.                                         // Create new database
  214.                                         using (SqlCommand sqlCommand = sqlConn.CreateCommand())
  215.                                         {
  216.                                                 sqlCommand.CommandTimeout = 5000;
  217.                                                 sqlCommand.CommandText = String.Format("CREATE DATABASE {0}", sDatabaseName);
  218.                                                 sqlCommand.ExecuteNonQuery();
  219.                                         }
  220.  
  221.                                         // Get the backup filelist
  222.                                         List<string> primaryDataFiles = new List<string>();
  223.                                         List<string> logFiles = new List<string>();
  224.  
  225.                                         using (SqlCommand sqlCommand = sqlConn.CreateCommand())
  226.                                         {
  227.                                                 sqlCommand.CommandTimeout = 5000;
  228.                                                 sqlCommand.CommandText = String.Format("RESTORE FILELISTONLY FROM DISK = '{0}'", sBackupToRestore);
  229.                                                 using (SqlDataReader dataReader = sqlCommand.ExecuteReader())
  230.                                                 {
  231.                                                         while(dataReader.Read())
  232.                                                         {
  233.                                                                 if (dataReader["type"].Equals("D"))
  234.                                                                 {
  235.                                                                         primaryDataFiles.Add(dataReader["LogicalName"] as string);
  236.                                                                 }
  237.                                                                 else if (dataReader["type"].Equals("L"))
  238.                                                                 {
  239.                                                                         logFiles.Add(dataReader["LogicalName"] as string);
  240.                                                                 }
  241.                                                         }
  242.                                                 }
  243.                                         }
  244.  
  245.                                         //restore the backup file into the database
  246.                                         using (SqlCommand sqlCommand = sqlConn.CreateCommand())
  247.                                         {
  248.                                                 sqlCommand.CommandTimeout = 5000;
  249.                                                 StringBuilder restoreCommand = new StringBuilder();
  250.                                                 restoreCommand.AppendFormat(
  251.                                                         "RESTORE DATABASE {0} FROM DISK = '{1}' WITH FILE=1, REPLACE", sDatabaseName,
  252.                                                         sBackupToRestore);
  253.                                                 int count = 0;
  254.                                                 foreach (string logicalFile in primaryDataFiles)
  255.                                                 {
  256.                                                         restoreCommand.AppendFormat(", MOVE N'{0}' TO N'{1}'", logicalFile,
  257.                                                                                                                 String.Format("{0}{1}{2}{3}.mdf", new FileInfo(sBackupToRestore).DirectoryName, Path.DirectorySeparatorChar, sDatabaseName, count));
  258.                                                         count++;
  259.                                                 }
  260.                                                 count = 0;
  261.                                                 foreach (string logFile in logFiles)
  262.                                                 {
  263.                                                         restoreCommand.AppendFormat(", MOVE N'{0}' TO N'{1}'", logFile,
  264.                                                                                                                 String.Format("{0}{1}{2}{3}.ldf", new FileInfo(sBackupToRestore).DirectoryName, Path.DirectorySeparatorChar, sDatabaseName, count));
  265.                                                         count++;
  266.                                                 }
  267.                                                 sqlCommand.CommandText = restoreCommand.ToString();
  268.                                                 sqlCommand.ExecuteNonQuery();
  269.                                         }
  270.                                 }
  271.                         }
  272.                         catch (Exception ex)
  273.                         {
  274.  
  275.                                 LogException(ex);
  276.                                 return false;
  277.                         }
  278.                         return true;
  279.                 }
  280.  
  281.                 /// <summary>
  282.                 /// Create the Schema Scripts in the specified folder for the specified database
  283.                 /// </summary>
  284.                 /// <param name="sServerName">SQL Server name</param>
  285.                 /// <param name="sDatabaseName">Database to script</param>
  286.                 /// <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
  287.                 /// <param name="sUserName">Username or null if using Integrated Authentication</param>
  288.                 /// <param name="sPassword">Password or null if using Integrated Authentication</param>
  289.                 /// <param name="sScriptFolderPath">Destination of the Script Folder</param>
  290.                 /// <returns></returns>
  291.                 private static bool CreateSchemaScripts(
  292.                         string sServerName,
  293.                         string sDatabaseName,
  294.                         bool bUseIntegratedAuthentication,
  295.                         string sUserName,
  296.                         string sPassword,
  297.                         string sScriptFolderPath)
  298.                 {
  299.                         try
  300.                         {
  301.                                 // Check whether the destination directory exists
  302.                                 if (Directory.Exists(sScriptFolderPath))
  303.                                 {
  304.                                         Directory.Delete(sScriptFolderPath,true);
  305.                                 }
  306.                                 // Create the script folder directory
  307.                                 Directory.CreateDirectory(sScriptFolderPath);
  308.                         }
  309.                         catch (Exception ex)
  310.                         {
  311.                                 LogException(ex);
  312.                                 return false;
  313.                         }
  314.  
  315.                         try
  316.                         {
  317.                                 using (Database dLiveDatabase = new Database())
  318.                                 {
  319.                                         // Register the live database whose schema will be written to the script folder
  320.                                         Console.WriteLine(String.Format("Registering Live Database {0}.{1}...", sServerName, sDatabaseName));
  321.  
  322.                                         dLiveDatabase.Status += StatusCallBack;
  323.  
  324.                                         try
  325.                                         {
  326.                                                 dLiveDatabase.Register(CreateConnectionProperties(sServerName, sDatabaseName, bUseIntegratedAuthentication, sUserName, sPassword), Options.Default);
  327.                                         }
  328.                                         catch (SqlException sqlEx)
  329.                                         {
  330.                                                 Console.WriteLine(sqlEx.Message);
  331.                                                 Console.WriteLine(@"</li>
  332. Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:</li>
  333.                 o The sample databases are not installed</li>
  334.                 o ServerName not set to the location of the target database</li>
  335.                 o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor</li>
  336.                 o Remote connections not enabled", sDatabaseName, sServerName);
  337.                                                 return false;
  338.                                         }
  339.  
  340.                                         // Save the registered database as a Schema Scripts Folder
  341.                                         Console.WriteLine(String.Format("Saving Database to {0}", sScriptFolderPath));
  342.  
  343.                                         // Configure Script Folder settings
  344.                                         ScriptDatabaseInformation sdiDatabaseInformation = new ScriptDatabaseInformation();
  345.  
  346.                                         // save the scripts as Unicode
  347.                                         sdiDatabaseInformation.ScriptEncodingType = ScriptEncodingType.UTF16;
  348.  
  349.                                         dLiveDatabase.SaveToFolder(sScriptFolderPath, sdiDatabaseInformation);
  350.                                 }
  351.                         }
  352.                         catch (Exception ex)
  353.                         {
  354.                                 LogException(ex);
  355.                                 return false;
  356.                         }
  357.  
  358.                         return true;
  359.                 }
  360.                 /// <summary>
  361.                 /// Using the existing Script Folder sync the static data from the Static Tables specified into the folder
  362.                 /// </summary>
  363.                 /// <param name="sServerName">SQL Server name</param>
  364.                 /// <param name="sDatabaseName">Database to script</param>
  365.                 /// <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
  366.                 /// <param name="sUserName">Username or null if using Integrated Authentication</param>
  367.                 /// <param name="sPassword">Password or null if using Integrated Authentication</param>
  368.                 /// <param name="sScriptFolderPath">The Script Folder to synchronize the static data into</param>
  369.                 /// <param name="sStaticDataTables">The Tables within the database that contain the static data</param>
  370.                 /// <returns></returns>
  371.                 private static bool SynchronizeStaticDataIntoScriptFolder(
  372.                         string sServerName,
  373.                         string sDatabaseName,
  374.                         bool bUseIntegratedAuthentication,
  375.                         string sUserName,
  376.                         string sPassword,
  377.                         string sScriptFolderPath,
  378.                         IEnumerable<string> sStaticDataTables)
  379.                 {
  380.                         try
  381.                         {
  382.                                 // Check whether the destination directory exists
  383.                                 if (!Directory.Exists(sScriptFolderPath))
  384.                                 {
  385.                                         Console.WriteLine(String.Format("ERROR: Script folder {0} doesn't exist.", sScriptFolderPath));
  386.                                         return false;
  387.                                 }
  388.                                 // Check whether the destination folder contains a Script Folder
  389.                                 if (!FolderDataSource.DoesDatabaseInformationFileExist(sScriptFolderPath))
  390.                                 {
  391.                                         Console.WriteLine(String.Format("ERROR: Folder {0} doesn't contain a Script Folder.", sScriptFolderPath));
  392.                                         return false;
  393.                                 }
  394.                         }
  395.                         catch (Exception ex)
  396.                         {
  397.                                 LogException(ex);
  398.                                 return false;
  399.                         }
  400.  
  401.                         try
  402.                         {
  403.                                 using (Database dScriptFolder = new Database())
  404.                                 using (Database dLiveDatabase = new Database())
  405.                                 using (ComparisonSession csComparison = new ComparisonSession())
  406.                                 {
  407.                                         // Register the live database whose schema will be written to the script folder
  408.                                         Console.WriteLine(String.Format("Registering Live Database {0}.{1}...", sServerName, sDatabaseName));
  409.  
  410.                                         // Now we have built the script folder we can register the database using RegisterForDataCompare
  411.                                         dLiveDatabase.Status += StatusCallBack;
  412.  
  413.                                         try
  414.                                         {
  415.                                                 dLiveDatabase.RegisterForDataCompare(
  416.                                                         CreateConnectionProperties(sServerName, sDatabaseName, bUseIntegratedAuthentication,
  417.                                                                                                               sUserName, sPassword), Options.Default);
  418.                                         }
  419.                                         catch (SqlException sqlEx)
  420.                                         {
  421.                                                 Console.WriteLine(sqlEx.Message);
  422.                                                 Console.WriteLine(
  423.                                                         @"</li>
  424. Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:</li>
  425.                 o The sample databases are not installed</li>
  426.                 o ServerName not set to the location of the target database</li>
  427.                 o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor</li>
  428.                 o Remote connections not enabled",
  429.                                                         sDatabaseName, sServerName);
  430.                                                 return false;
  431.                                         }
  432.  
  433.                                         // Register the script database
  434.                                         Console.WriteLine(String.Format("Registering Script Database from {0}", sScriptFolderPath));
  435.  
  436.                                         dScriptFolder.Status += StatusCallBack;
  437.                                         // NOTE - always pass null for Script Database Information in Data Compare (will read script settings from the xml file in the folder)
  438.                                         dScriptFolder.RegisterForDataCompare(sScriptFolderPath, null, Options.Default);
  439.  
  440.                                         Console.WriteLine("Creating Mappings...");
  441.  
  442.                                         SchemaMappings smMappings = new SchemaMappings();
  443.                                         smMappings.CreateMappings(dLiveDatabase, dScriptFolder);
  444.  
  445.                                         // Only compare tables that contain static data
  446.                                         // First exclude all tables
  447.                                         foreach (TableMapping tmMapping in smMappings.TableMappings)
  448.                                         {
  449.                                                 tmMapping.Include = false;
  450.                                         }
  451.  
  452.                                         // Now re-enable for the comparison those tables that contain static data
  453.                                         // N^2 algorithm only used for clarity
  454.                                         foreach (string staticDataTable in sStaticDataTables)
  455.                                         {
  456.                                                 foreach (TableMapping tmMapping in smMappings.TableMappings)
  457.                                                 {
  458.                                                         IDatabaseObject scriptFolderTable = tmMapping.Obj2;
  459.                                                         if (scriptFolderTable != null &&
  460.                                                                 scriptFolderTable.Name == staticDataTable)
  461.                                                         {
  462.                                                                 if (tmMapping.Status != TableMappingStatus.UnableToCompare)
  463.                                                                 {
  464.                                                                         tmMapping.Include = true;
  465.                                                                 }
  466.                                                                 else
  467.                                                                 {
  468.                                                                         Console.WriteLine(
  469.                                                                                 String.Format("Static Data Table {0} can't be included for comparison",
  470.                                                                                                             staticDataTable));
  471.                                                                 }
  472.                                                         }
  473.                                                 }
  474.                                         }
  475.  
  476.                                         Console.WriteLine("Performing comparison (before sync)...");
  477.  
  478.                                         // Perform the comparison
  479.                                         csComparison.Status += StatusCallBack;
  480.                                         csComparison.CompareDatabases(dLiveDatabase, dScriptFolder, smMappings);
  481.  
  482.                                         Console.WriteLine("Summary of Results of Comparison (before sync)...");
  483.  
  484.                                         SummarizeDifferences(csComparison);
  485.  
  486.                                         Console.WriteLine("Perform Synchronization...");
  487.  
  488.                                         SqlProvider provider = new SqlProvider();
  489.  
  490.                                         // Get the Migration SQL to synchronize the scripts folder
  491.  
  492.                                         using (ExecutionBlock block = provider.GetMigrationSQL(csComparison,delegate { return true; },true))
  493.                                         {
  494.                                                 // The execution block contains a script version of the changes that will be
  495.                                                 // applied to the script folder for review purposes
  496.                                                 block.Dispose();
  497.                                         }
  498.  
  499.                                         // perform the synchronization
  500.  
  501.                                         provider.Synchronize();
  502.  
  503.                                         Console.WriteLine("Performing comparison (after sync)...");
  504.  
  505.                                         // Perform the comparison
  506.                                         csComparison.CompareDatabases(dLiveDatabase, dScriptFolder, smMappings);
  507.  
  508.                                         Console.WriteLine("Summary of Results of Comparison (after sync)...");
  509.  
  510.                                         SummarizeDifferences(csComparison);
  511.                                 }
  512.                         }
  513.                         catch (Exception ex)
  514.                         {
  515.                                 LogException(ex);
  516.                                 return false;
  517.                         }
  518.  
  519.                         return true;
  520.                 }
  521.                 /// <summary>
  522.                 /// Provide a summary of the differences
  523.                 /// </summary>
  524.                 /// <param name="csComparison">Comparison Session to summarize</param>
  525.                 private static void SummarizeDifferences(ComparisonSession csComparison)
  526.                 {
  527.                         foreach (TableDifference difference in csComparison.TableDifferences)
  528.                         {
  529.                                 if (difference.TableMapping.Include) // included difference
  530.                                 {
  531.                                         DifferencesSummary summary = difference.DifferencesSummary;
  532.  
  533.                                         Console.WriteLine(String.Format("Table {0} ", difference.TargetTable(true).Name));
  534.                                         Console.WriteLine(String.Format("Live Only {0}", summary.DifferenceCount(Row.RowType.In1)));
  535.                                         Console.WriteLine(String.Format("Script Folder Only {0}", summary.DifferenceCount(Row.RowType.In2)));
  536.                                         Console.WriteLine(String.Format("In both but Different {0}", summary.DifferenceCount(Row.RowType.Different)));
  537.                                         Console.WriteLine(String.Format("In both and the Same {0}", summary.DifferenceCount(Row.RowType.Same)));
  538.                                 }
  539.                         }
  540.                 }
  541.                 /// <summary>
  542.                 /// Create a connection properties object to connect to the current Server/Database specified
  543.                 /// </summary>
  544.                 /// <param name="sServerName">SQL Server name</param>
  545.                 /// <param name="sDatabaseName">Database to script</param>
  546.                 /// <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
  547.                 /// <param name="sUserName">Username or null if using Integrated Authentication</param>
  548.                 /// <param name="sPassword">Password or null if using Integrated Authentication</param>
  549.                 /// <returns>A connection properties</returns>
  550.                 private static ConnectionProperties CreateConnectionProperties(
  551.                         string sServerName,
  552.                         string sDatabaseName,
  553.                         bool bUseIntegratedAuthentication,
  554.                         string sUserName,
  555.                         string sPassword)
  556.                 {
  557.                         ConnectionProperties connProp = new ConnectionProperties();
  558.                         connProp.ServerName = sServerName;
  559.                         connProp.DatabaseName = sDatabaseName;
  560.                         connProp.IntegratedSecurity = bUseIntegratedAuthentication;
  561.  
  562.                         if (!bUseIntegratedAuthentication)
  563.                         {
  564.                                 connProp.UserName = sUserName;
  565.                                 connProp.Password = sPassword;
  566.                         }
  567.  
  568.                         return connProp;
  569.                 }
  570.                 /// <summary>
  571.                 /// Log an exception to the command line
  572.                 /// </summary>
  573.                 /// <param name="ex">The exception to log</param>
  574.                 private static void LogException(Exception ex)
  575.                 {
  576.                         if (ex == null)
  577.                         {
  578.                                 return;
  579.                         }
  580.                         Console.WriteLine("\n-------------------");
  581.                         Console.WriteLine(String.Format("Caught {0}",ex.GetType().Name));
  582.                         Console.WriteLine(ex.Message);
  583.                         Console.WriteLine(ex.StackTrace);
  584.                         Console.WriteLine("-------------------");
  585.                 }
  586.                 private static void StatusCallBack(object sender, StatusEventArgs e)
  587.                 {
  588.                         if (e.Message != null)
  589.                         {
  590.                                 Console.WriteLine(e.Message);
  591.                         }
  592.  
  593.                         if (e.Percentage != -1)
  594.                         {
  595.                                 Console.WriteLine("{0}%", e.Percentage);
  596.                         }
  597.                 }
  598.         }
  599. }
  600.  

VB

  1. Option Explicit On
  2.  
  3. Imports System
  4. Imports System.Collections.Generic
  5. Imports System.Data.SqlClient
  6. Imports System.IO
  7. Imports RedGate.SQLCompare.Engine
  8. Imports RedGate.SQLCompare.Engine.ReadFromFolder
  9. Imports RedGate.SQLDataCompare.Engine
  10. Imports RedGate.SQLDataCompare.Engine.ResultsStore
  11. Imports RedGate.Shared.Utils
  12. Imports System.Text
  13.  
  14. ''' <summary>
  15. ''' Create an empty (schema only) script folder, synchronize static data into it
  16. ''' </summary>
  17. Public Class StaticDataWithScriptsExample
  18.         Public Sub RunExample()
  19.                 ' Constant information for the test
  20.                 ' To run this test alter these settings to reflect local configuration
  21.                 Dim ServerNameToScript As String = "."
  22.                 Dim DatabaseNameToScript As String = "WidgetDevScripts"
  23.                 Dim StaticDataTableNames() As String = {"Widgets", "WidgetDescriptions", "WidgetPrices"}
  24.                 Dim Username As String = &quot;&quot;
  25.                 Dim Password As String = &quot;&quot;
  26.                 Dim UseIntegratedAuthentication As Boolean = String.IsNullOrEmpty(Username)
  27.                 Dim ScriptFolderPath As String = "WidgetDev"
  28.                 Dim BackupPath As String = "WidgetDev.bak"
  29.                 Dim CleanUpAfterExecution As Boolean = True
  30.  
  31.                 ' First restore the Backup of WidgetDev
  32.                 Console.WriteLine(String.Format("RESTORE DATABASE - Creating new database {0}.{1} from {2}", ServerNameToScript, DatabaseNameToScript, BackupPath))
  33.  
  34.                 If (Not RestoreDatabase(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, Username, Password, BackupPath)) Then
  35.                         ' Couldn't restore database
  36.                         Console.WriteLine("Database couldn't be restored.")
  37.                         Return
  38.                 End If
  39.  
  40.                 Console.WriteLine("END RESTORE DATABASE")
  41.  
  42.                 ' Create the script folder containing the schema information
  43.                 ' Uses SQL Compare
  44.  
  45.                 Console.WriteLine("BEGIN - Create Schema Scripts")
  46.                 Console.WriteLine(String.Format("From {0}.{1} -> {2}", ServerNameToScript, DatabaseNameToScript, ScriptFolderPath))
  47.  
  48.                 If (Not CreateSchemaScripts(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, Username, Password, ScriptFolderPath)) Then
  49.                         ' Couldn't create schema scripts
  50.                         Console.WriteLine("Schema scripts couldn't be created.")
  51.                         Return
  52.                 End If
  53.  
  54.                 Console.WriteLine("END - Create Schema Scripts")
  55.  
  56.                 Console.WriteLine(String.Format("BEGIN - Synchronize Static Data into Scripts Folder {0} ", ScriptFolderPath))
  57.  
  58.                 If (Not SynchronizeStaticDataIntoScriptFolder(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, Username, Password, ScriptFolderPath, StaticDataTableNames)) Then
  59.                         ' Couldn't synchronize static data into Script Folder
  60.                         Console.WriteLine("Couldn't Synchronize Static Data into Scripts Folder.")
  61.                         Return
  62.                 End If
  63.  
  64.                 Console.WriteLine("END - Synchronize Static Data into Scripts Folder")
  65.  
  66.                 'Perform execution cleanup
  67.                 If (CleanUpAfterExecution) Then
  68.                         Console.WriteLine("BEGIN CLEANUP")
  69.  
  70.                         Cleanup(ServerNameToScript, DatabaseNameToScript, UseIntegratedAuthentication, Username, Password, ScriptFolderPath)
  71.  
  72.                         Console.WriteLine("CLEANUP COMPLETE")
  73.                 End If
  74.         End Sub
  75.         ''' <summary>
  76.         ''' Cleans up after the running of this program
  77.         ''' </summary>
  78.         ''' <param name="sServerName">SQL Server name</param>
  79.         ''' <param name="sDatabaseName">Database name</param>
  80.         ''' <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
  81.         ''' <param name="sUserName">Username or null if using Integrated Authentication</param>
  82.         ''' <param name="sPassword">Password or null if using Integrated Authentication</param>
  83.         ''' <param name="sScriptFolderPath">Destination of the Script Folder</param>
  84.         Private Sub Cleanup(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal bUseIntegratedAuthentication As Boolean, ByVal sUserName As String, ByVal sPassword As String, ByVal sScriptFolderPath As String)
  85.                 Try
  86.                         ' Check whether the destination directory exists
  87.                         If (Directory.Exists(sScriptFolderPath)) Then
  88.                                 Directory.Delete(sScriptFolderPath, True)
  89.                         End If
  90.                 Catch ex As Exception
  91.                         LogException(ex)
  92.                 End Try
  93.  
  94.                 Try
  95.                         ' Connect to SQL Server
  96.                         Dim connProp As ConnectionProperties = CreateConnectionProperties(sServerName, "master", bUseIntegratedAuthentication, sUserName, sPassword)
  97.                         Using sqlConn As New SqlConnection(connProp.ConnectionString)
  98.  
  99.                                 sqlConn.Open()
  100.  
  101.                                 ' Drop the database we created
  102.                                 Dim sqlCommand As SqlCommand = sqlConn.CreateCommand
  103.                                 Using sqlCommand
  104.                                         sqlCommand.CommandTimeout = 5000
  105.                                         sqlCommand.CommandText = String.Format("DROP DATABASE {0}", sDatabaseName)
  106.                                         sqlCommand.ExecuteNonQuery()
  107.                                 End Using
  108.                         End Using
  109.                 Catch ex As Exception
  110.                         LogException(ex)
  111.                 End Try
  112.         End Sub
  113.         ''' <summary>
  114.         ''' Restore the database from the specified backup
  115.         ''' </summary>
  116.         ''' <param name="sServerName">SQL Server name</param>
  117.         ''' <param name="sDatabaseName">Database name</param>
  118.         ''' <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
  119.         ''' <param name="sUserName">Username or null if using Integrated Authentication</param>
  120.         ''' <param name="sPassword">Password or null if using Integrated Authentication</param>
  121.         ''' <param name="sBackupToRestore">Backup file to restore</param>
  122.         ''' <returns></returns>
  123.         Private Function RestoreDatabase(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal bUseIntegratedAuthentication As Boolean, ByVal sUserName As String, ByVal sPassword As String, ByVal sBackupToRestore As String) As Boolean
  124.                 Try
  125.                         If (Not File.Exists(sBackupToRestore)) Then
  126.                                 Console.WriteLine(String.Format("ERROR: Backup file {0} doesn't exist.", sBackupToRestore))
  127.                                 Return False
  128.                         End If
  129.                 Catch ex As Exception
  130.                         LogException(ex)
  131.                         Return False
  132.                 End Try
  133.  
  134.                 Try
  135.                         ' Connect to SQL Server
  136.                         Dim connProp As ConnectionProperties = CreateConnectionProperties(sServerName, "master", bUseIntegratedAuthentication, sUserName, sPassword)
  137.  
  138.                         Using sqlConn As New SqlConnection(connProp.ConnectionString)
  139.  
  140.                                 sqlConn.Open()
  141.  
  142.                                 Dim sqlCommand As SqlCommand
  143.  
  144.                                 ' be optomistic about the drop command, database may not exist
  145.                                 Try
  146.                                         sqlCommand = sqlConn.CreateCommand()
  147.                                         Using sqlCommand
  148.                                                 sqlCommand.CommandTimeout = 5000
  149.                                                 sqlCommand.CommandText = String.Format("DROP DATABASE {0}", sDatabaseName)
  150.                                                 sqlCommand.ExecuteNonQuery()
  151.                                         End Using
  152.                                 Catch e As Exception
  153.                                 End Try
  154.  
  155.                                 ' Create new database
  156.                                 sqlCommand = sqlConn.CreateCommand()
  157.                                 Using sqlCommand
  158.                                         sqlCommand.CommandTimeout = 5000
  159.                                         sqlCommand.CommandText = String.Format("CREATE DATABASE {0}", sDatabaseName)
  160.                                         sqlCommand.ExecuteNonQuery()
  161.                                 End Using
  162.  
  163.                                 ' Get the backup filelist
  164.                                 Dim primaryDataFiles As New List(Of String)
  165.                                 Dim logFiles As New List(Of String)
  166.  
  167.                                 sqlCommand = sqlConn.CreateCommand()
  168.                                 Using sqlCommand
  169.                                         sqlCommand.CommandTimeout = 5000
  170.                                         sqlCommand.CommandText = String.Format("RESTORE FILELISTONLY FROM DISK = '{0}'", sBackupToRestore)
  171.                                         Dim dataReader As SqlDataReader = sqlCommand.ExecuteReader()
  172.                                         Using dataReader
  173.                                                 While (dataReader.Read())
  174.                                                         If dataReader("type").Equals("D") Then
  175.                                                                 primaryDataFiles.Add(dataReader("LogicalName"))
  176.                                                         ElseIf dataReader("type").Equals("L") Then
  177.                                                                 logFiles.Add(dataReader("LogicalName"))
  178.                                                         End If
  179.                                                 End While
  180.                                         End Using
  181.                                 End Using
  182.  
  183.                                 'restore the backup file into the database
  184.                                 sqlCommand = sqlConn.CreateCommand()
  185.                                 Using sqlCommand
  186.                                         sqlCommand.CommandTimeout = 5000
  187.                                         Dim restoreCommand As StringBuilder = New StringBuilder()
  188.                                         restoreCommand.AppendFormat("RESTORE DATABASE {0} FROM DISK = '{1}' WITH FILE=1, REPLACE", sDatabaseName, sBackupToRestore)
  189.                                         Dim count As Int32 = 0
  190.  
  191.                                         For Each logicalFile As String In primaryDataFiles
  192.                                                 restoreCommand.AppendFormat(", MOVE N'{0}' TO N'{1}'", logicalFile, String.Format("{0}{1}{2}{3}.mdf", New FileInfo(sBackupToRestore).DirectoryName, Path.DirectorySeparatorChar, sDatabaseName, count))
  193.                                                 count += 1
  194.                                         Next logicalFile
  195.  
  196.                                         count = 0
  197.                                         For Each logFile As String In logFiles
  198.                                                 restoreCommand.AppendFormat(", MOVE N'{0}' TO N'{1}'", logFile, String.Format("{0}{1}{2}{3}.ldf", New FileInfo(sBackupToRestore).DirectoryName, Path.DirectorySeparatorChar, sDatabaseName, count))
  199.                                                 count += 1
  200.                                         Next logFile
  201.  
  202.                                         sqlCommand.CommandText = restoreCommand.ToString()
  203.                                         sqlCommand.ExecuteNonQuery()
  204.                                 End Using
  205.                         End Using
  206.                 Catch ex As Exception
  207.                         LogException(ex)
  208.                         Return False
  209.                 End Try
  210.                 Return True
  211.         End Function
  212.  
  213.         ''' <summary>
  214.         ''' Create the Schema Scripts in the specified folder for the specified database
  215.         ''' </summary>
  216.         ''' <param name="sServerName">SQL Server name</param>
  217.         ''' <param name="sDatabaseName">Database to script</param>
  218.         ''' <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
  219.         ''' <param name="sUserName">Username or null if using Integrated Authentication</param>
  220.         ''' <param name="sPassword">Password or null if using Integrated Authentication</param>
  221.         ''' <param name="sScriptFolderPath">Destination of the Script Folder</param>
  222.         ''' <returns></returns>
  223.         Private Function CreateSchemaScripts(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal bUseIntegratedAuthentication As Boolean, ByVal sUserName As String, ByVal sPassword As String, ByVal sScriptFolderPath As String) As Boolean
  224.                 Try
  225.                         ' Check whether the destination directory exists
  226.                         If (Directory.Exists(sScriptFolderPath)) Then
  227.                                 Directory.Delete(sScriptFolderPath, True)
  228.                         End If
  229.                         ' Create the script folder directory
  230.                         Directory.CreateDirectory(sScriptFolderPath)
  231.                 Catch ex As Exception
  232.                         LogException(ex)
  233.                         Return False
  234.                 End Try
  235.  
  236.                 Try
  237.                         Using dLiveDatabase As New Database()
  238.                                 ' Register the live database whose schema will be written to the script folder
  239.                                 Console.WriteLine(String.Format("Registering Live Database {0}.{1}...", sServerName, sDatabaseName))
  240.                                 dLiveDatabase.Status = New StatusEventHandler(AddressOf StatusCallback)
  241.                                 Try
  242.                                         dLiveDatabase.Register(CreateConnectionProperties(sServerName, sDatabaseName, bUseIntegratedAuthentication, sUserName, sPassword), Options.Default)
  243.                                 Catch sqlEx As SqlException
  244.                                         Console.WriteLine(sqlEx.Message)
  245.                                         Console.WriteLine(vbCrLf & _
  246.                 "Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:" & vbCrLf & _
  247.                 " o The sample databases are not installed" & vbCrLf & _
  248.                 " o ServerName not set to the location of the target database" & vbCrLf & _
  249.                 " o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor" & vbCrLf & _
  250.                 " o Remote connections not enabled", sDatabaseName, sServerName)
  251.                                         Return False
  252.                                 End Try
  253.  
  254.                                 ' Save the registered database as a Schema Scripts Folder
  255.                                 Console.WriteLine(String.Format("Saving Database to {0}", sScriptFolderPath))
  256.  
  257.                                 ' Configure Script Folder settings
  258.                                 Dim sdiDatabaseInformation As New ScriptDatabaseInformation()
  259.  
  260.                                 ' save the scripts as Unicode
  261.                                 sdiDatabaseInformation.ScriptEncodingType = ScriptEncodingType.UTF16
  262.  
  263.                                 dLiveDatabase.SaveToFolder(sScriptFolderPath, sdiDatabaseInformation)
  264.                         End Using
  265.                 Catch ex As Exception
  266.                         LogException(ex)
  267.                         Return False
  268.                 End Try
  269.                 Return True
  270.         End Function
  271.         ''' <summary>
  272.         ''' A function that always returns true
  273.         ''' </summary>
  274.         ''' <remarks></remarks>
  275.   Private Function TrueDelegate(ByVal syncRecord As SynchronizationRecord) As Boolean
  276.     Return True
  277.   End Function
  278.         ''' <summary>
  279.         ''' Using the existing Script Folder sync the static data from the Static Tables specified into the folder
  280.         ''' </summary>
  281.         ''' <param name="sServerName">SQL Server name</param>
  282.         ''' <param name="sDatabaseName">Database to script</param>
  283.         ''' <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
  284.         ''' <param name="sUserName">Username or null if using Integrated Authentication</param>
  285.         ''' <param name="sPassword">Password or null if using Integrated Authentication</param>
  286.         ''' <param name="sScriptFolderPath">The Script Folder to synchronize the static data into</param>
  287.         ''' <param name="sStaticDataTables">The Tables within the database that contain the static data</param>
  288.         ''' <returns></returns>
  289.         Private Function SynchronizeStaticDataIntoScriptFolder(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal bUseIntegratedAuthentication As Boolean, ByVal sUserName As String, ByVal sPassword As String, ByVal sScriptFolderPath As String, ByVal sStaticDataTables As IEnumerable(Of String)) As Boolean
  290.                 Try
  291.                         ' Check whether the destination directory exists
  292.                         If Not Directory.Exists(sScriptFolderPath) Then
  293.                                 Console.WriteLine(String.Format("ERROR: Script folder {0} doesn't exist.", sScriptFolderPath))
  294.                                 Return False
  295.                         End If
  296.                         ' Check whether the destination folder contains a Script Folder
  297.                         If Not FolderDataSource.DoesDatabaseInformationFileExist(sScriptFolderPath) Then
  298.                                 Console.WriteLine(String.Format("ERROR: Folder {0} doesn't contain a Script Folder.", sScriptFolderPath))
  299.                                 Return False
  300.                         End If
  301.                 Catch ex As Exception
  302.                         LogException(ex)
  303.                         Return False
  304.                 End Try
  305.  
  306.                 Try
  307.                         Using dScriptFolder As New Database()
  308.                                 Using dLiveDatabase As New Database()
  309.                                         Using csComparison As New ComparisonSession()
  310.                                                 ' Register the live database whose schema will be written to the script folder
  311.                                                 Console.WriteLine(String.Format("Registering Live Database {0}.{1}...", sServerName, sDatabaseName))
  312.  
  313.                                                 ' Now we have built the script folder we can register the database using RegisterForDataCompare
  314.                                                 dLiveDatabase.Status = New StatusEventHandler(AddressOf StatusCallback)
  315.  
  316.                                                 Try
  317.                                                         dLiveDatabase.RegisterForDataCompare(CreateConnectionProperties(sServerName, sDatabaseName, bUseIntegratedAuthentication, sUserName, sPassword), Options.Default)
  318.                                                 Catch sqlEx As SqlException
  319.                                                         Console.WriteLine(sqlEx.Message)
  320.                                                         Console.WriteLine(vbCrLf & _
  321.                                                             "Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:" & vbCrLf & _
  322.                                                             " o The sample databases are not installed" & vbCrLf & _
  323.                                                             " o ServerName not set to the location of the target database" & vbCrLf & _
  324.                                                             " o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor" & vbCrLf & _
  325.                                                             " o Remote connections not enabled", sDatabaseName, sServerName)
  326.                                                         Return False
  327.                                                 End Try
  328.  
  329.                                                 ' Register the script database
  330.                                                 Console.WriteLine(String.Format("Registering Script Database from {0}", sScriptFolderPath))
  331.  
  332.                                                 dScriptFolder.Status = New StatusEventHandler(AddressOf StatusCallback)
  333.                                                 ' NOTE - always pass Nothing for Script Database Information in Data Compare (will read script settings from the xml file in the folder)
  334.                                                 dScriptFolder.RegisterForDataCompare(sScriptFolderPath, Nothing, Options.Default)
  335.  
  336.                                                 Console.WriteLine("Creating Mappings...")
  337.  
  338.                                                 Dim smMappings As SchemaMappings = New SchemaMappings()
  339.                                                 smMappings.CreateMappings(dLiveDatabase, dScriptFolder)
  340.  
  341.                                                 ' Only compare tables that contain static data
  342.                                                 ' First exclude all tables
  343.                                                 For Each tmMapping As TableMapping In smMappings.TableMappings
  344.                                                         tmMapping.Include = False
  345.                                                 Next tmMapping
  346.  
  347.                                                 ' Now re-enable for the comparison those tables that contain static data
  348.                                                 ' N^2 algorithm only used for clarity
  349.                                                 For Each staticDataTable As String In sStaticDataTables
  350.                                                         For Each tmMapping As TableMapping In smMappings.TableMappings
  351.                                                                 Dim scriptFolderTable As IDatabaseObject = tmMapping.Obj2
  352.                                                                 If ((Not scriptFolderTable Is Nothing) And scriptFolderTable.Name.Equals(staticDataTable)) Then
  353.                                                                         If tmMapping.Status <> TableMappingStatus.UnableToCompare Then
  354.                                                                                 tmMapping.Include = True
  355.                                                                         Else
  356.                                                                                 Console.WriteLine(String.Format("Static Data Table {0} can't be included for comparison", staticDataTable))
  357.                                                                         End If
  358.                                                                 End If
  359.                                                         Next tmMapping
  360.                                                 Next staticDataTable
  361.  
  362.                                                 Console.WriteLine("Performing comparison (before sync)...")
  363.  
  364.                                                 ' Perform the comparison
  365.                                                 csComparison.Status = New StatusEventHandler(AddressOf StatusCallback)
  366.                                                 csComparison.CompareDatabases(dLiveDatabase, dScriptFolder, smMappings)
  367.  
  368.                                                 Console.WriteLine("Summary of Results of Comparison (before sync)...")
  369.  
  370.                                                 SummarizeDifferences(csComparison)
  371.  
  372.                                                 Console.WriteLine("Perform Synchronization...")
  373.  
  374.                                                 Dim provider As New SqlProvider()
  375.  
  376.                                                 ' Get the Migration SQL to synchronize the scripts folder
  377.  
  378.                                                 Using block = provider.GetMigrationSQL(csComparison, AddressOf TrueDelegate, True)
  379.  
  380.                                                         ' The execution block contains a script version of the changes that will be
  381.                                                         ' applied to the script folder for review purposes
  382.                                                         block.Dispose()
  383.                                                 End Using
  384.  
  385.                                                 ' perform the synchronization
  386.  
  387.                                                 provider.Synchronize()
  388.  
  389.                                                 Console.WriteLine("Performing comparison (after sync)...")
  390.  
  391.                                                 ' Perform the comparison
  392.                                                 csComparison.CompareDatabases(dLiveDatabase, dScriptFolder, smMappings)
  393.  
  394.                                                 Console.WriteLine("Summary of Results of Comparison (after sync)...")
  395.  
  396.                                                 SummarizeDifferences(csComparison)
  397.                                         End Using
  398.                                 End Using
  399.                         End Using
  400.                         catch ex As Exception
  401.                         LogException(ex)
  402.                         Return False
  403.                 End Try
  404.                 Return True
  405.         End Function
  406.         ''' <summary>
  407.         ''' Provide a summary of the differences
  408.         ''' </summary>
  409.         ''' <param name="csComparison">Comparison Session to summarize</param>
  410.         Private Sub SummarizeDifferences(ByVal csComparison As ComparisonSession)
  411.                 For Each diff As TableDifference In csComparison.TableDifferences
  412.                         If diff.TableMapping.Include Then ' included difference
  413.                                 Dim summary As DifferencesSummary = diff.DifferencesSummary
  414.                                 Console.WriteLine(String.Format("Table {0} ", diff.TargetTable(True).Name))
  415.                                 Console.WriteLine(String.Format("Live Only {0}", summary.DifferenceCount(Row.RowType.In1)))
  416.                                 Console.WriteLine(String.Format("Script Folder Only {0}", summary.DifferenceCount(Row.RowType.In2)))
  417.                                 Console.WriteLine(String.Format("In both but Different {0}", summary.DifferenceCount(Row.RowType.Different)))
  418.                                 Console.WriteLine(String.Format("In both and the Same {0}", summary.DifferenceCount(Row.RowType.Same)))
  419.                         End If
  420.                 Next diff
  421.         End Sub
  422.         ''' <summary>
  423.         ''' Create a connection properties object to connect to the current Server/Database specified
  424.         ''' </summary>
  425.         ''' <param name="sServerName">SQL Server name</param>
  426.         ''' <param name="sDatabaseName">Database to script</param>
  427.         ''' <param name="bUseIntegratedAuthentication">true to use Integrated Authentication, false otherwise</param>
  428.         ''' <param name="sUserName">Username or null if using Integrated Authentication</param>
  429.         ''' <param name="sPassword">Password or null if using Integrated Authentication</param>
  430.         ''' <returns>A connection properties</returns>
  431.         Private Function CreateConnectionProperties(ByVal sServerName As String, ByVal sDatabaseName As String, ByVal bUseIntegratedAuthentication As Boolean, ByVal sUserName As String, ByVal sPassword As String) As ConnectionProperties
  432.                 Dim connProp As New ConnectionProperties()
  433.                 connProp.ServerName = sServerName
  434.                 connProp.DatabaseName = sDatabaseName
  435.                 connProp.IntegratedSecurity = bUseIntegratedAuthentication
  436.  
  437.                 If Not bUseIntegratedAuthentication Then
  438.                         connProp.UserName = sUserName
  439.                         connProp.Password = sPassword
  440.                 End If
  441.  
  442.                 Return connProp
  443.         End Function
  444.         ''' <summary>
  445.         ''' Log an exception to the command line
  446.         ''' </summary>
  447.         ''' <param name="ex">The exception to log</param>
  448.         Private Sub LogException(ByVal ex As Exception)
  449.                 If ex Is Nothing Then
  450.                         Return
  451.                 End If
  452.                 Console.WriteLine("\n-------------------")
  453.                 Console.WriteLine(String.Format("Caught {0}", ex.GetType().Name))
  454.                 Console.WriteLine(ex.Message)
  455.                 Console.WriteLine(ex.StackTrace)
  456.                 Console.WriteLine("-------------------")
  457.         End Sub
  458.  
  459.         Private Sub StatusCallback(ByVal sender As Object, ByVal e As StatusEventArgs)
  460.  
  461.                 If Not (e.Message Is Nothing) Then
  462.                         Console.WriteLine(e.Message)
  463.                 End If
  464.  
  465.                 If e.Percentage <> -1 Then
  466.                         Console.WriteLine("{0}%", e.Percentage)
  467.                 End If
  468.         End Sub
  469. End Class
Personal tools