Worked Example

This worked example shows you how to create a command-line program to compare and synchronize the data in two online databases, WidgetDev and WidgetLive.

You will see how to:

Set up the databases.
Set up the project.
Create a database object.
Compare the databases.
Print the changes to be made.
Get the synchronization SQL.
Run the SQL script.
Dispose of the objects.

For detailed information about the API, see the Reference topics provided in this online help.

Set up the databases

If they already exist, delete WidgetDev and WidgetLive from your SQL Server.
Click here to open the SQL creation script for the databases, and then run the script in your SQL application.

Set up the project

Create a new console application project ConsoleApplication1 using Visual Studio® .NET
Add references to RedGate.SQLCompare.Engine.dll, RedGate.SQLDataCompare.Engine.dll, and RedGate.SQL.Shared.dll

You can find the .dll files in the folder where you installed SQL Data Compare. The default location is Program Files\Red Gate\SQL Data Compare 5
Create a file licenses.licx and add it to the project, then add the following lines to it:

RedGate.SQLCompare.Engine.Database, RedGate.SQLCompare.Engine

RedGate.SQLDataCompare.Engine.ComparisonSession, RedGate.SQLDataCompare.Engine

Create a database object

Ensure the default module or class is importing or using the RedGate.SQLCompare.Engine, RedGate.SQLDataCompare.Engine, and RedGate.SQL.Shared assemblies.
Alter the Main method to instantiate two Database classes and call RegisterForDataCompare on them, passing in the details of the SQL Servers and databases that you want to connect to.

[Visual Basic]

Imports RedGate.SQL.Shared
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQLDataCompare.Engine
Imports RedGate.SQLDataCompare.Engine.ResultsStore

Module Module1

  Sub Main()
     Dim db1 As New Database
     Dim db2 As New Database

     db1.RegisterForDataCompare(New ConnectionProperties(".", "WidgetDev"))
     db2.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive")) 
  End Sub

End Module

[C#]

using System;
using RedGate.SQL.Shared;
using RedGate.SQLCompare.Engine;
using RedGate.SQLDataCompare.Engine;
using RedGate.SQLDataCompare.Engine.ResultsStore;

namespace ConsoleApplication1
{
  class Class1
     {
        [STAThread]
         static void Main(string[] args)
         {
            Database db1=new Database();
            Database db2=new Database();

            db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"));
            db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive")); 
         }
     }
}

Compare the databases

SQL Data Compare works best on databases that have identical structures. Because real databases often do not have identical structures, you can use SQL Data Compare to determine which tables are common to both databases, and which fields and indexes are common to these tables. SQL Data Compare provides the CreateMappings method for this.

When you have done this, create a ComparisonSession object and call CompareDatabases.

[Visual Basic]

Dim mappings As New SchemaMappings
mappings.CreateMappings(db1, db2) 

Dim session As New ComparisonSession()
session.CompareDatabases(db1, db2, mappings)

[C#]

SchemaMappings mappings = new SchemaMappings();
mappings.CreateMappings(db1, db2);

ComparisonSession session=new ComparisonSession();
session.CompareDatabases(db1, db2, mappings);

Print the changes to be made

You can use the TableDifferences property to view summary information about the data differences between the two databases. This collection contains a number of TableDifference objects, indexed by fully-qualified table name. Each of these objects has a property ResultsStore that is a pointer to a ResultsStore.Store object. This object contains a list of the records that are identical, different, or present in one database but not the other.

You must use the OrdinalInResults1 and OrdinalInResults2 properties of a FieldPair class to retrieve the data from the Values property of a ResultsStore.Row.

If the ResultsStore represents identical records, or records that exist in only one database, the OrdinalInResults1 and OrdinalInResults2 are the same and indicate the position of the data for the field in the ResultsStore.
If the ResultsStore represents different records and the field is part of the unique index being used for the comparison, OrdinalInResults1 and OrdinalInResults2 are the same and indicate the position of the data.
If the ResultsStore represents different records and the field is not part of the unique index, OrdinalInResults1 and OrdinalInResults2 indicate the position of the data in the first database and second database respectively.

For example, if you are comparing two tables with the following structures:

*RecordID, FirstName, LastName

where * indicates a unique index, the ResultsStore representing identical records, or records present in only one database, will have the following structure:

RecordID, FirstName, LastName

and the OrdinalInResults1 and OrdinalInResults2 properties will have the following values in the Fields collection:

RecordID : 0
FirstName : 1
LastName : 2

If, however, the ResultsStore represents different records, it will have the following structure:

RecordID, FirstName, FirstName, LastName, LastName

The FirstName and LastName fields appear twice because they contain data from the two databases. The OrdinalInResults1 and OrdinalInResults2 properties on the Fields collection will have the following values:

RecordID : 0,0
FirstName : 1,2
LastName : 3,4

[Visual Basic]

Dim mapping As TableMapping

For Each mapping In mappings.TableMappings
  Dim table As ViewTableSuperClass = mapping.Obj1
  Dim difference As TableDifference = session.TableDifferences(table.FullyQualifiedName)
  Dim row As Row
  For Each row In difference.ResultsStore  'loop through all the rows
     If (row.Type <> row.RowType.Same) Then 'go through the non same records
        Dim field As FieldPair
        Dim i As Int32 = 0
        Console.WriteLine("{0} Row {1} type {2}", table.FullyQualifiedName, row.Index, row.Type.ToString())
        For Each field In difference.ResultsStore.Fields
           'work out where about in the results the field data is stored
           'if we were comparing identical records, or records present in one
           'database but not the other then we would not need to 
           'use the OrdinalInResults1 and OrdinalInResults2 properties
           'but just OrdinalInResults
           Dim field1 As Int32 = field.OrdinalInResults1
           Dim field2 As Int32 = field.OrdinalInResults2

           If (field1 <> field2) Then
              'get the values
              Dim value1 As Object = row.Values(field1)
              Dim value2 As Object = row.Values(field2)
              If (value1 Is Nothing) Then
                  value1 = "NULL"
              End If
              If (value2 Is Nothing) Then
                  value2 = "NULL"
              End If
              If row.FieldDifferent(i) Then
                 Console.WriteLine("{0}:{1} <> {2}", field.Field(False).Name, value1.ToString(), value2.ToString())
              Else
                 Console.WriteLine("{0}:{1} == {2}", field.Field(False).Name, value1.ToString(), value2.ToString())
              End If
           Else
              'this is part of the unique index we are comparing on
              Dim value As Object = row.Values(field1)
              Console.WriteLine("*{0}:{1}", field.Field(False).Name, value.ToString())
           End If
              i += 1
        Next
     End If
  Next
Next

[C#]

foreach (TableMapping mapping in mappings.TableMappings)
{    
  TableDifference difference=session.TableDifferences[mapping.Obj1.FullyQualifiedName];                
  //display the different records
                         
  //loop through all the rows
  foreach(Row row in difference.ResultsStore)
  {
     //go through the non same records
     if (row.Type != Row.RowType.Same)
     {
        Console.WriteLine("{0} Row {1} type {2}", mapping.Obj1.FullyQualifiedName, row.Index, row.Type.ToString());
        int i=0;
        foreach (FieldPair field in difference.ResultsStore.Fields)
        {
           int field1=field.OrdinalInResults1;
           int field2=field.OrdinalInResults2;
            
           if (field1 != field2)
           {
              //get the values
              object value1=row.Values[field1];
              object value2=row.Values[field2];
              if (value1 == null)
                  value1="NULL";
              if (value2 == null)
                  value2="NULL";
              Console.WriteLine("{0}\t{1}\t{2}\t{3}",field.Field(false).Name, value1.ToString(),row.FieldDifferent(i)?"<>":"==",  value2.ToString());
            }
            else
            {
               //this is part of the unique index we are comparing on
               object value=row.Values[field1];    
               Console.WriteLine("*{0}\t{1}",field.Field(false).Name, value.ToString());
            }
            i++;
         }
      }
   }
}

Get the synchronization SQL

Ensure the Selected property is set to true only on the TableDifference objects that you want to synchronize.
In this worked example, we will synchronize all changes.
Call GetMigrationSQL to get an ExecutionBlock containing the synchronization SQL.

[Visual Basic]

Dim provider as New SQLProvider
'we want to run the SQL on WidgetLive so pass in true as the second parameter
Dim block As ExecutionBlock = provider.GetMigrationSQL(session, True)
Console.WriteLine("Migration SQL:")
Console.WriteLine(block.GetString())

[C#]

SqlProvider provider = New SqlProvider();
//we want to run the SQL on WidgetLive so pass in true as the second parameter
ExecutionBlock block=provider.GetMigrationSQL(session, true);
Console.WriteLine("Migration SQL:");
Console.WriteLine(block.GetString());

Run the SQL script

Instantiate a BlockExecutor object and call the ExecuteBlock method to run the SQL script. Ensure you run it on the correct database.

[Visual Basic]

Dim executor As BlockExecutor = New BlockExecutor
executor.ExecuteBlock(block, ".", "WidgetLive")

[C#]

BlockExecutor executor = new BlockExecutor();
executor.ExecuteBlock(block, ".", "WidgetLive");

Dispose of the objects

SQL Data Compare makes extensive use of temporary files. To ensure that these files are deleted you must dispose of the objects you have used.

[Visual Basic]

session.Dispose()
block.Dispose()
db1.Dispose()
db2.Dispose()

[C#]

session.Dispose();
block.Dispose();
db1.Dispose();
db2.Dispose();

The SQL Data Compare API	Submit feedback on this topic
Worked Example