SQLProviderExample

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