Worked Example

This worked example shows you how to create a command-line program to compare and synchronize the schema of two databases, WidgetStaging and WidgetProduction.

You will see how to:

Set up the databases.
Set up the project.
Create a database object.
Compare the databases.
Script the differences.
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 exist already, delete the databases WidgetStaging and WidgetProduction 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 using Visual Studio® 2005.
Add references to RedGate.SQLCompare.Engine.dll and RedGate.SQL.Shared.dll

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

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

Create a database object

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

[Visual Basic]

Imports RedGate.SQL.Shared
Imports RedGate.SQLCompare.Engine

Module Module1

  Sub Main()

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

     db1.Register(New ConnectionProperties(".", "WidgetStaging"), Options.Default)
     db2.Register(New ConnectionProperties(".", "WidgetProduction"), Options.Default)

  End Sub

End Module

[C#]

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

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

        db1.Register(new ConnectionProperties(".", "WidgetStaging"), Options.Default);
        db2.Register(new ConnectionProperties(".", "WidgetProduction"), Options.Default);
     }
  }
}

Compare the databases

When you have registered both databases:

Use the CompareWith method to generate a Differences collection.
Iterate through the collection to examine the schema differences in detail, and set the Selected property to true for the objects that you want to synchronize, or false for those that you do not.

All objects that differ are selected for synchronization by default. However, you should explicitly set the Selected property to true for the objects that you want to synchronize; this will ensure the objects are included in the synchronization script when you script the differences.

[Visual Basic]

Dim stagingVsProduction As Differences = db1.CompareWith(db2, Options.Default)
Dim difference As Difference

For Each difference In stagingVsProduction
  Console.WriteLine("{0} {1} {2}", difference.Type.ToString(),
                    difference.DatabaseObjectType.ToString(), difference.Name)
  difference.Selected = True
Next

[C#]

Differences stagingVsProduction=db1.CompareWith(db2, Options.Default);

foreach (Difference difference in stagingVsProduction)
{
  Console.WriteLine("{0} {1} {2}", difference.Type.ToString(),
                    difference.DatabaseObjectType.ToString(), difference.Name);
  difference.Selected=true;
}

Script the differences

Create a Work object, and build it from the differences generated in the previous step.
Use the ExecutionBlock property to access the script to synchronize the databases.

[Visual Basic]

Dim work As New Work()

work.BuildFromDifferences(stagingVsProduction, Options.Default, True)

Dim block As ExecutionBlock = work.ExecutionBlock

Console.WriteLine("SQL to synchronize:")
Console.WriteLine(block.GetString())

[C#]

Work work=new Work();

work.BuildFromDifferences(stagingVsProduction, Options.Default, true);

ExecutionBlock block=work.ExecutionBlock;

Console.WriteLine("SQL to synchronize:");
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, ".", "WidgetProduction")

[C#]

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

Dispose of the objects

The Database and ExecutionBlock objects make extensive use of temporary files. To ensure that these files are deleted you must dispose of the objects you have used.

[Visual Basic]

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

[C#]

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

It is better practise to use a Using block as it will also dispose of objects in the event of errors.

[Visual Basic]

Using widgetStaging As New Database(), widgetProduction As New Database()

    ' Connect to the two databases and read the schema
    widgetStaging.Register(New ConnectionProperties(".", "WidgetStaging"), Options.Default)
    widgetProduction.Register(New ConnectionProperties(".", "WidgetProduction"), Options.Default)
    
    ' Use the databases...

End Using

[C#]

using (Database widgetStaging = new Database(),
                widgetProduction = new Database())
{
    // Connect to the two databases and read the schema
    widgetStaging.Register(new ConnectionProperties(".", "WidgetStaging"), Options.Default);
    widgetProduction.Register(new ConnectionProperties(".", "WidgetProduction"), Options.Default);

    // Use the databases...
}

The SQL Compare API	Submit feedback on this topic
Worked Example