SQL Data Compare project

From RedGateWiki
Jump to: navigation, search

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
Personal tools