WhereExample

From RedGateWiki
Jump to: navigation, search

Back to SQL Comparison SDK

This example shows how a WHERE clause can be used to filter the data that will be compared. In addition to providing granularity to the data being synchronized, it can also improve performance by cutting down the amount of data retrieval necessary to do the comparison.


C#

  1. using System;
  2. using RedGate.Shared.SQL;
  3. using RedGate.Shared.Utils;
  4. using RedGate.SQLCompare.Engine;
  5. using RedGate.SQLDataCompare.Engine;
  6. using RedGate.Shared.SQL.ExecutionBlock;
  7.  
  8. namespace SQLDataCompareCodeSnippets
  9. {
  10.             /// <summary>
  11.             /// Summary description for WhereExample.
  12.             /// </summary>
  13.             public class WhereExample
  14.             {
  15.                         private static void AddWhereClause(TableMappings mappings)
  16.                         {
  17.                                     foreach (TableMapping mapping in mappings)
  18.                                     {
  19.                                                 if (mapping.Obj1.FullyQualifiedName=="[dbo].[Widgets]")
  20.                                                 {
  21.                                                             mapping.Include=true;
  22.                                                             mapping.Where = new WhereClause("RecordID>=2");
  23.                                                 }
  24.                                                 else
  25.                                                 {
  26.                                                             mapping.Include = false;
  27.                                                 }
  28.                                     }
  29.                         }
  30.  
  31.                         public void RunExample()
  32.                         {
  33.                                     Database db1=new Database();
  34.                                     Database db2=new Database();
  35.  
  36.                                     db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetStaging"), Options.Default);
  37.                                     db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetProduction"), Options.Default);
  38.  
  39.                                     // Create the mappings between the two databases
  40.                                     TableMappings mappings = new TableMappings();
  41.                                     mappings.CreateMappings(db1.Tables, db2.Tables);
  42.  
  43.                                     AddWhereClause(mappings);
  44.  
  45.                                     mappings.Options = new EngineDataCompareOptions(
  46.                                                 MappingOptions.Default,
  47.                                                 ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
  48.                                                 SqlOptions.Default);
  49.  
  50.                                     using (ComparisonSession session=new ComparisonSession())
  51.                                     {
  52.                                                 session.Options = mappings.Options;
  53.                                                 session.CompareDatabases(db1, db2, mappings);
  54.  
  55.                                                 // now get the ExecutionBlock containing the SQL
  56.                                                 // we want to run this on WidgetLive so we pass on true as the second parameter
  57.                                                 SqlProvider provider=new SqlProvider();
  58.                                                 provider.Options = session.Options;
  59.                                                 ExecutionBlock block;
  60.                                                 try
  61.                                               {
  62.                                                             block = provider.GetMigrationSQL(session, true);
  63.                                                               Console.WriteLine("The synchronization SQL contains {0} lines in {1} batches", block.LineCount, block.BatchCount);
  64.  
  65.                                                           // if the ExecutionBlock was very large this could cause memory problems
  66.                                                             Console.WriteLine("The SQL to be run is:");
  67.                                                             Console.WriteLine(block.GetString());
  68.  
  69.                                                             // we can access the SQL in a memory efficient manner by accessing the underlying stream
  70.                                                             // FileStream stream=block.GetFileStream();
  71.  
  72.                                                             // run the SQL ( commented out by default )
  73.                                                             // BlockExecutor executor = new BlockExecutor();
  74.                                                             // executor.ExecuteBlock(block, ".", "WidgetLive");
  75.                                                 }
  76.                                                 finally
  77.                                                 {
  78.                                                             block = provider.Block;
  79.                                                             if (block != null)
  80.                                                             {
  81.                                                                         block.Dispose(); // dispose of the objects to delete temporary files
  82.                                                             }
  83.                                                 }
  84.                                   }
  85.                                     db1.Dispose();
  86.                                     db2.Dispose();
  87.                         }
  88.  
  89.             }
  90. }
  91.  

VB

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