WhereExample

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