FilterSQLExample

From RedGateWiki
Jump to: navigation, search

Back to SQL Comparison SDK

This example displays the selection process for extracting certain records from a database table – it operates by using SelectionDelegate

Alternatively selection can achieved by using the Where clause listed in the WhereExample.cs


C#

  1. using System;
  2. using RedGate.Shared.SQL;
  3. using RedGate.SQLCompare.Engine;
  4. using RedGate.SQLDataCompare.Engine;
  5. using RedGate.SQLDataCompare.Engine.ResultsStore;
  6. using RedGate.Shared.SQL.ExecutionBlock;
  7. using System.Data.SqlClient;
  8.  
  9.  
  10. namespace SQLDataCompareCodeSnippets
  11. {
  12.         public class FilterSQLExample
  13.         {
  14.                 static TableDifferences m_TableDifferences;
  15.  
  16.                 public static void Main(string[] args)
  17.                 {
  18.                         Database db1 = new Database();
  19.                         Database db2 = new Database();
  20.  
  21.                         db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
  22.                         db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
  23.  
  24.                         // Create the mappings between the two databases
  25.                         TableMappings mappings = new TableMappings();
  26.                         mappings.CreateMappings(db1.Tables, db2.Tables);
  27.  
  28.                         mappings.Options = new EngineDataCompareOptions(
  29.                                                 MappingOptions.Default,
  30.                                                 ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
  31.                                                 SqlOptions.Default);
  32.  
  33.                         using (ComparisonSession session = new ComparisonSession())
  34.                         {
  35.                                 session.Options = mappings.Options;
  36.                                 session.CompareDatabases(db1, db2, mappings);
  37.  
  38.                                 m_TableDifferences = session.TableDifferences;
  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.                                 provider.Options = session.Options;
  44.                                 ExecutionBlock block;
  45.                                 try
  46.                                 {
  47.                                         block = provider.GetMigrationSQL(session, new SelectionDelegate(SyncRecord), true);
  48.  
  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.                                         string sqlstuff = block.GetString();
  54.                                         Console.WriteLine(sqlstuff);
  55.  
  56.                                 }
  57.                                 finally
  58.                                 {
  59.                                         block = provider.Block;
  60.                                         if (block != null)
  61.                                         {
  62.                                                 block.Dispose(); // dispose of the objects to delete temporary files
  63.                                         }
  64.                                 }
  65.                         }
  66.                         db1.Dispose();
  67.                         db2.Dispose();
  68.                 }
  69.                 static bool SyncRecord(SynchronizationRecord syncRecordObject)
  70.                 {
  71.                         // Check only the Widgets table
  72.                         if (syncRecordObject.TableName == "[dbo].[Widgets]")
  73.                         {
  74.                                 // Create a results reader for this table
  75.                                 Reader resultsReader = m_TableDifferences[syncRecordObject.TableName].ResultsStore.GetReader();
  76.                                 // Get a reference for the row to check. Bookmark is the row number submitted for this table
  77.                                 Row myRow = resultsReader.GetRow(syncRecordObject.Bookmark);
  78.                                 // Work out which column of the ResultsReader corresponds to the "RecordID" column in database1
  79.                                 int idOrdinal=m_TableDifferences[syncRecordObject.TableName].ResultsStore.Fields["RecordID"].OrdinalInResults1;
  80.                                 if ((Int64)myRow.Values[idOrdinal] > 3)
  81.                                 {
  82.                                         // Select record for synchronization
  83.                                         return true;
  84.                                 }
  85.                         }
  86.                         // If table != Widgets or Widgets.RecordID < 4, do not select record for synchronization
  87.                         return false;
  88.                 }
  89.         }
  90. }

VB

  1. Imports System
  2. Imports RedGate.Shared.SQL
  3. Imports RedGate.SQLCompare.Engine
  4. Imports RedGate.SQLDataCompare.Engine
  5. Imports RedGate.SQLDataCompare.Engine.ResultsStore
  6. Imports RedGate.Shared.SQL.ExecutionBlock
  7. Imports System.Data.SqlClient
  8.  
  9. Module Module1
  10.         Dim m_TableDifferences As TableDifferences
  11.  
  12.         Sub Main(ByVal args As String())
  13.                 Dim db1 As New Database()
  14.                 Dim db2 As New Database()
  15.  
  16.                 db1.RegisterForDataCompare(New ConnectionProperties(".", "WidgetDev"), Options.[Default])
  17.                 db2.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.[Default])
  18.  
  19.                 ' Create the mappings between the two databases
  20.                 Dim mappings As New TableMappings()
  21.                 mappings.CreateMappings(db1.Tables, db2.Tables)
  22.  
  23.                 mappings.Options = New EngineDataCompareOptions(MappingOptions.[Default], ComparisonOptions.TrimTrailingSpaces Or ComparisonOptions.[Default], SqlOptions.[Default])
  24.  
  25.                 Using session As New ComparisonSession()
  26.                         session.Options = mappings.Options
  27.                         session.CompareDatabases(db1, db2, mappings)
  28.  
  29.                         m_TableDifferences = session.TableDifferences
  30.  
  31.                         ' now get the ExecutionBlock containing the SQL
  32.                         ' we want to run this on WidgetLive so we pass on true as the second parameter
  33.                         Dim provider As New SqlProvider()
  34.                         provider.Options = session.Options
  35.                         Dim block As ExecutionBlock
  36.                         Try
  37.                                 block = provider.GetMigrationSQL(session, New SelectionDelegate(AddressOf SyncRecord), True)
  38.  
  39.                                 Console.WriteLine("The synchronization SQL contains {0} lines in {1} batches", block.LineCount, block.BatchCount)
  40.  
  41.                                 ' if the ExecutionBlock was very large this could cause memory problems
  42.                                 Console.WriteLine("The SQL to be run is:")
  43.                                 Dim sqlstuff As String = block.GetString()
  44.  
  45.                                 Console.WriteLine(sqlstuff)
  46.                         Finally
  47.                                 block = provider.Block
  48.                                 If block IsNot Nothing Then
  49.                                         ' dispose of the objects to delete temporary files
  50.                                         block.Dispose()
  51.                                 End If
  52.                         End Try
  53.                 End Using
  54.                 db1.Dispose()
  55.                 db2.Dispose()
  56.         End Sub
  57.         Private Function SyncRecord(ByVal syncRecordObject As SynchronizationRecord) As Boolean
  58.                 ' Check only the Widgets table
  59.                 If syncRecordObject.TableName = "[dbo].[Widgets]" Then
  60.                         ' Create a results reader for this table
  61.                         Dim resultsReader As Reader = m_TableDifferences(syncRecordObject.TableName).ResultsStore.GetReader()
  62.                         ' Get a reference for the row to check. Bookmark is the row number submitted for this table
  63.                         Dim myRow As Row = resultsReader.GetRow(syncRecordObject.Bookmark)
  64.                         ' Work out which column of the ResultsReader corresponds to the "RecordID" column in database1
  65.                         Dim idOrdinal As Integer = m_TableDifferences(syncRecordObject.TableName).ResultsStore.Fields("RecordID").OrdinalInResults1
  66.                         If DirectCast(myRow.Values(idOrdinal), Int64) > 3 Then
  67.                                 ' Select record for synchronization
  68.                                 Return True
  69.                         End If
  70.                 End If
  71.                 ' If table != Widgets or Widgets.RecordID < 4, do not select record for synchronization
  72.                 Return False
  73.         End Function
  74. End Module
Personal tools