FilterSQLExample

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