SQL Data Compare project

Back to SQL Comparison SDK

This example shows how you can create and load SQL Data Compare project files programmatically. The projects saved by the full commercial SQL Data Compare project can be loaded by the SDK to specify the data sources as well as preset table, view, and column mappings.

---

C#

1. using System;
2. using System.Data.SqlClient;
3. using RedGate.SQLCompare.Engine;
4. using RedGate.SQLDataCompare.Engine;
5.  
6. namespace SQLDataCompareCodeSnippets
7. {
8.         public class ProjectExample
9.         {
10.                 private const string projectName = @"testproject.sdc";
11.  
12.                 public static void Main()
13.                 {
14.                         //load up the project
15.                         Project project = Project.LoadFromDisk(projectName);
16.                         Console.WriteLine("Project loaded");
17.  
18.                         //get the two databases
19.                         using (Database db1 = new Database())
20.                         using (Database db2 = new Database())
21.                         {
22.                                 SchemaMappings mappings = new SchemaMappings();
23.  
24.                                 //Should check if this is true
25.                                 LiveDatabaseSource liveDb1 = project.DataSource1 as LiveDatabaseSource;
26.                                 ConnectionProperties sourceConnectionProperties = liveDb1.ToConnectionProperties();
27.  
28.                                 //Should check if this is true
29.                                 LiveDatabaseSource liveDb2 = project.DataSource2 as LiveDatabaseSource;
30.                                 ConnectionProperties targetConnectionProperties = liveDb2.ToConnectionProperties();
31.  
32.                                 try
33.                                 {
34.                                         Console.WriteLine("Registering database " + sourceConnectionProperties.DatabaseName);
35.                                         db1.RegisterForDataCompare(sourceConnectionProperties, Options.Default);
36.                                 }
37.                                 catch (SqlException e)
38.                                 {
39.                                         Console.WriteLine(e.Message);
40.                                         Console.WriteLine(@"</li>
41. Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:</li>
42.                 o The sample databases are not installed</li>
43.                 o ServerName not set to the location of the target database</li>
44.                 o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor</li>
45.                 o Remote connections not enabled", sourceConnectionProperties.DatabaseName, sourceConnectionProperties.ServerName);
46.                                         return;
47.                                 }
48.                                 try
49.                                 {
50.                                         Console.WriteLine("Registering database " + targetConnectionProperties.DatabaseName);
51.                                         db2.RegisterForDataCompare(targetConnectionProperties, Options.Default);
52.                                 }
53.                                 catch (SqlException e)
54.                                 {
55.                                         Console.WriteLine(e.Message);
56.                                         Console.WriteLine(@"</li>
57. Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:</li>
58.                 o The sample databases are not installed</li>
59.                 o ServerName not set to the location of the target database</li>
60.                 o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor</li>
61.                 o Remote connections not enabled", targetConnectionProperties.DatabaseName, targetConnectionProperties.ServerName);
62.                                         return;
63.                                 }
64.  
65.                                 mappings.Options = project.DCOptions;
66.                                 mappings.CreateMappings(db1, db2); // Create a set of base mappings
67.                                 //Modify the default mappings using what has been saved in the project
68.                                 RedGate.SQLDataCompare.Engine.DataCompareUserActions.ReplayUserActions(
69.                                         project.SelectTableActions,
70.                                         project.DataSource1,
71.                                         project.DataSource2,
72.                                         ref mappings);
73.  
74.                                 using (ComparisonSession session = new ComparisonSession())
75.                                 {
76.                                         session.Options = project.DCOptions;
77.                                         session.CompareDatabases(db1, db2, mappings);
78.                                         SqlProvider sp = new SqlProvider();
79.                                         Console.WriteLine(sp.GetMigrationSQL(session, true));
80.                                         Console.WriteLine("Comparison run");
81.                                         Console.ReadLine();
82.                                 }
83.                         }
84.                 }
85.         }
86. }

---

VB

1. Imports System
2. Imports System.Data.SqlClient
3. Imports RedGate.SQLCompare.Engine
4. Imports RedGate.SQLDataCompare.Engine
5.  
6. Namespace SQLDataCompareCodeSnippets
7.         Public Class ProjectExample
8.                 Private Const projectName As String = "testproject.sdc"
9.  
10.                 Public Shared Sub Main()
11.                         'load up the project
12.                         Dim project__1 As Project = Project.LoadFromDisk(projectName)
13.                         Console.WriteLine("Project loaded")
14.  
15.                         'get the two databases
16.                         Using db1 As New Database()
17.                                 Using db2 As New Database()
18.                                         Dim mappings As New SchemaMappings()
19.  
20.                                         'Should check if this is true
21.                                         Dim liveDb1 As LiveDatabaseSource = TryCast(project__1.DataSource1, LiveDatabaseSource)
22.                                         Dim sourceConnectionProperties As ConnectionProperties = liveDb1.ToConnectionProperties()
23.  
24.                                         'Should check if this is true
25.                                         Dim liveDb2 As LiveDatabaseSource = TryCast(project__1.DataSource2, LiveDatabaseSource)
26.                                         Dim targetConnectionProperties As ConnectionProperties = liveDb2.ToConnectionProperties()
27.  
28.                                         Try
29.                                                 Console.WriteLine("Registering database " & sourceConnectionProperties.DatabaseName)
30.                                                 db1.RegisterForDataCompare(sourceConnectionProperties, Options.[Default])
31.                                         Catch e As SqlException
32.                                                 Console.WriteLine(e.Message)
33.                                                 Console.WriteLine(vbCr & vbLf & "Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:" & vbCr & vbLf & " o The sample databases are not installed" & vbCr & vbLf & " o ServerName not set to the location of the target database" & vbCr & vbLf & " o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor" & vbCr & vbLf & " o Remote connections not enabled", sourceConnectionProperties.DatabaseName, sourceConnectionProperties.ServerName)
34.                                                 Exit Sub
35.                                         End Try
36.                                         Try
37.                                                 Console.WriteLine("Registering database " & targetConnectionProperties.DatabaseName)
38.                                                 db2.RegisterForDataCompare(targetConnectionProperties, Options.[Default])
39.                                         Catch e As SqlException
40.                                                 Console.WriteLine(e.Message)
41.                                                 Console.WriteLine(vbCr & vbLf & "Cannot connect to database '{0}' on server '{1}'. The most common causes of this error are:" & vbCr & vbLf & " o The sample databases are not installed" & vbCr & vbLf & " o ServerName not set to the location of the target database" & vbCr & vbLf & " o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor" & vbCr & vbLf & " o Remote connections not enabled", targetConnectionProperties.DatabaseName, targetConnectionProperties.ServerName)
42.                                                 Exit Sub
43.                                         End Try
44.  
45.                                         mappings.Options = project__1.DCOptions
46.                                         mappings.CreateMappings(db1, db2)
47.                                         ' Create a set of base mappings
48.                                         'Modify the default mappings using what has been saved in the project
49.                                         RedGate.SQLDataCompare.Engine.DataCompareUserActions.ReplayUserActions(project__1.SelectTableActions, project__1.DataSource1, project__1.DataSource2, mappings)
50.  
51.                                         Using session As New ComparisonSession()
52.                                                 session.Options = project__1.DCOptions
53.                                                 session.CompareDatabases(db1, db2, mappings)
54.                                                 Dim sp As New SqlProvider()
55.                                                 Console.WriteLine(sp.GetMigrationSQL(session, True))
56.                                                 Console.WriteLine("Comparison run")
57.                                                 Console.ReadLine()
58.                                         End Using
59.                                 End Using
60.                         End Using
61.                 End Sub
62.         End Class
63. End Namespace