SQLProviderExample

From RedGateWiki
Jump to: navigation, search

Back to SQL Comparison SDK

This example demonstrates how the SqlProvider object can be used to create SQL migration scripts.


C#

  1. using System;
  2. using RedGate.Shared.SQL;
  3. using RedGate.SQLCompare.Engine;
  4. using RedGate.SQLDataCompare.Engine;
  5. using RedGate.Shared.SQL.ExecutionBlock;
  6.  
  7. namespace SQLDataCompareCodeSnippets
  8. {
  9.         public class SqlProviderExample
  10.         {
  11.  
  12.                 public static void Main()
  13.                 {
  14.                         Database db1 = new Database();
  15.                         Database db2 = new Database();
  16.  
  17.                         db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
  18.                         db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
  19.  
  20.                         // Create the mappings between the two databases
  21.                         TableMappings mappings = new TableMappings();
  22.                         mappings.CreateMappings(db1.Tables, db2.Tables);
  23.  
  24.                         //
  25.                         //Additionally set up trim trailing spaces...
  26.                         //
  27.                         mappings.Options = new EngineDataCompareOptions(
  28.                                                 MappingOptions.Default,
  29.                                                 ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
  30.                                                 SqlOptions.Default);
  31.  
  32.                         using (ComparisonSession session = new ComparisonSession())
  33.                         {
  34.                                 //
  35.                                 // Remember to set up the session options
  36.                                 //
  37.                                 session.Options = mappings.Options;
  38.                                 session.CompareDatabases(db1, db2, mappings);
  39.  
  40.                                 // now get the ExecutionBlock containing the SQL
  41.                                 // we want to run this on WidgetLive so we pass on true as the second parameter
  42.                                 SqlProvider provider = new SqlProvider();
  43.                                 //
  44.                                 // Also remember to set up the provider options
  45.                                 //
  46.                                 provider.Options = session.Options;
  47.                                 ExecutionBlock block;
  48.                                 try
  49.                                 {
  50.                                         block = provider.GetMigrationSQL(session, true);
  51.  
  52.                                         Console.WriteLine("The synchronization SQL contains {0} lines in {1} batches", block.LineCount, block.BatchCount);
  53.                                         // if the ExecutionBlock was very large this could cause memory problems
  54.                                         Console.WriteLine("The SQL to be run is:");
  55.                                         Console.WriteLine(block.GetString());
  56.  
  57.                                         // we can access the SQL in a memory efficient manner by accessing the underlying stream
  58.                                         // FileStream stream=block.GetFileStream();
  59.  
  60.                                         // run the SQL ( commented out by default )
  61.                                         // BlockExecutor executor = new BlockExecutor();
  62.                                         // executor.ExecuteBlock(block, ".", "WidgetLive");
  63.                                 }
  64.                                 finally
  65.                                 {
  66.                                         block = provider.Block;
  67.                                         if (block != null)
  68.                                         {
  69.                                                 block.Dispose(); // dispose of the objects to delete temporary files
  70.                                         }
  71.                                 }
  72.                         }
  73.                         db1.Dispose();
  74.                         db2.Dispose();
  75.                 }
  76.         }
  77. }

VB

  1. Imports System
  2. Imports RedGate.Shared.SQL
  3. Imports RedGate.SQLCompare.Engine
  4. Imports RedGate.SQLDataCompare.Engine
  5. Imports RedGate.Shared.SQL.ExecutionBlock
  6.  
  7. Module Module1
  8.  
  9.         Sub Main()
  10.                 Dim db1 As New Database()
  11.                 Dim db2 As New Database()
  12.  
  13.                 db1.RegisterForDataCompare(New ConnectionProperties(".", "WidgetDev"), Options.[Default])
  14.                 db2.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.[Default])
  15.  
  16.                 ' Create the mappings between the two databases
  17.                 Dim mappings As New TableMappings()
  18.                 mappings.CreateMappings(db1.Tables, db2.Tables)
  19.  
  20.                 '
  21.                 'Additionally set up trim trailing spaces...
  22.                 '
  23.                 mappings.Options = New EngineDataCompareOptions(MappingOptions.[Default], ComparisonOptions.TrimTrailingSpaces Or ComparisonOptions.[Default], SqlOptions.[Default])
  24.  
  25.                 Using session As New ComparisonSession()
  26.                         '
  27.                         ' Remember to set up the session options
  28.                         '
  29.                         session.Options = mappings.Options
  30.                         session.CompareDatabases(db1, db2, mappings)
  31.  
  32.                         ' now get the ExecutionBlock containing the SQL
  33.                         ' we want to run this on WidgetLive so we pass on true as the second parameter
  34.                         Dim provider As New SqlProvider()
  35.                         '
  36.                         ' Also remember to set up the provider options
  37.                         '
  38.                         provider.Options = session.Options
  39.                         Dim block As ExecutionBlock
  40.                         Try
  41.                                 block = provider.GetMigrationSQL(session, True)
  42.  
  43.                                 Console.WriteLine("The synchronization SQL contains {0} lines in {1} batches", block.LineCount, block.BatchCount)
  44.                                 ' if the ExecutionBlock was very large this could cause memory problems
  45.                                 Console.WriteLine("The SQL to be run is:")
  46.  
  47.                                 ' we can access the SQL in a memory efficient manner by accessing the underlying stream
  48.                                 ' FileStream stream=block.GetFileStream();
  49.  
  50.                                 ' run the SQL ( commented out by default )
  51.                                 ' BlockExecutor executor = new BlockExecutor();
  52.                                 ' executor.ExecuteBlock(block, ".", "WidgetLive");
  53.                                 Console.WriteLine(block.GetString())
  54.                         Finally
  55.                                 block = provider.Block
  56.                                 If block IsNot Nothing Then
  57.                                         ' dispose of the objects to delete temporary files
  58.                                         block.Dispose()
  59.                                 End If
  60.                         End Try
  61.                 End Using
  62.                 db1.Dispose()
  63.                 db2.Dispose()
  64.         End Sub
  65. End Module
Personal tools