GetMigrationSQL(ComparisonSession,Boolean) Method

Gets information about the SQL script for synchronizing multiple tables.

[Visual Basic]
Overloads Public Function GetMigrationSQL( _
   ByVal session As ComparisonSession, _
   ByVal runOnTwo As Boolean _
) As ExecutionBlock

[C#]
public ExecutionBlock GetMigrationSQL( 
   ComparisonSession session,
   bool runOnTwo
);

[C++]
public: ExecutionBlock* GetMigrationSQL( 
   ComparisonSession* session,
   bool runOnTwo
)

[C++/CLI]
public:
ExecutionBlock^ GetMigrationSQL( 
   ComparisonSession^ session,
   bool runOnTwo
)

Parameters

session: The ComparisonSession used to compare the databases.
runOnTwo: true if the SQL script is to be run on the second database.

Return Type

An ExecutionBlock containing the SQL code to run.

Exceptions

Exception	Description
SqlProviderException	If attempting to migrate data from a table with no primary key, unique index, or unique constraint defined on it, which means that rows cannot be uniquely identified.

Remarks

The Selected property must be set on all the TableDifferences to be included in the SQL script.
The Messages and Warnings properties will be available

Example

This example shows how to get and run the SQL script to synchronize two databases.

[C#]

using System; 
using RedGate.SQL.Shared; 
using RedGate.SQLCompare.Engine; 
using RedGate.SQLDataCompare.Engine; 
  
namespace SQLDataCompareCodeSnippets 
{ 
    public class SqlProviderExample 
    { 
        public void RunExample() 
        { 
            Database db1=new Database(); 
            Database db2=new Database(); 
     
            db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev")); 
            db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive")); 
     
            // Create the mappings between the two databases 
            TableMappings mappings = new TableMappings(); 
            mappings.CreateMappings(db1.Tables, db2.Tables); 
     
            using (ComparisonSession session=new ComparisonSession()) 
            { 
                session.CompareDatabases(db1, db2, mappings); 
     
                // now get the ExecutionBlock containing the SQL 
                // we want to run this on WidgetLive so we pass on true as the second parameter 
                SqlProvider provider=new SqlProvider();             
                ExecutionBlock block; 
                try 
                { 
                    block = provider.GetMigrationSQL(session, true); 
     
                    Console.WriteLine("The synchronization SQL contains {0} lines in {1} batches", block.LineCount, block.BatchCount); 
     
                    // if the ExecutionBlock was very large this could cause memory problems 
                    Console.WriteLine("The SQL to be run is:"); 
                    Console.WriteLine(block.GetString()); 
     
                    // we can access the SQL in a memory efficient manner by accessing the underlying stream 
                    // FileStream stream=block.GetFileStream(); 
     
                    // run the SQL ( commented out by default ) 
                    // BlockExecutor executor = new BlockExecutor(); 
                    // executor.ExecuteBlock(block, ".", "WidgetLive"); 
                } 
                finally 
                { 
                    block = provider.Block; 
                    if (block != null) 
                    { 
                        block.Dispose();    // dispose of the objects to delete temporary files 
                    } 
                } 
            } 
            db1.Dispose(); 
            db2.Dispose(); 
        } 
    } 
}

[Visual Basic]

Option Explicit On



Imports RedGate.SQL.Shared

Imports RedGate.SQLCompare.Engine

Imports RedGate.SQLDataCompare.Engine



Class SqlProviderExample

    Sub RunExample()

        'register the databases for comparison

        Dim db1 As New Database

        Dim db2 As New Database



        db1.RegisterForDataCompare(New ConnectionProperties(".", "WidgetDev"))

        db2.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"))



        'create an intersection of the tables

        Dim mappings As New TableMappings

        mappings.CreateMappings(db1.Tables, db2.Tables)



        'compare the databases

        Dim session As New ComparisonSession

        session.CompareDatabases(db1, db2, mappings)



        'now get the ExecutionBlock containing the SQL

        'we want to run this on WidgetLive so we pass on true as the second parameter

        Dim provider As New SqlProvider

        Dim block As ExecutionBlock

        Try

            block = provider.GetMigrationSQL(session, True)



            Console.WriteLine("The synchronization SQL contains {0} lines in {1} batches", block.LineCount, block.BatchCount)



            'if the ExecutionBlock was very large this could cause memory problems

            Console.WriteLine("The SQL to be run is:")

            Console.WriteLine(block.GetString())



            'we can access the SQL in a memory efficient manner by accessing the underlying stream

            'Dim stream As FileStream=block.GetFileStream()



            'run the SQL ( commented out by default )



            'Dim executor As RedGate.SQL.Shared.BlockExecutor = New BlockExecutor

            'executor.ExecuteBlock(block, ".", "WidgetLive")

        Finally

            block = provider.Block

            If (Typeof block Is ExecutionBlock) Then

                block.Dispose()            'dispose of the objects to delete temporary files

            End If

        End Try



        'dispose of the objects to delete temporary files

        session.Dispose()

        db1.Dispose()

        db2.Dispose()

    End Sub

End Class

Requirements

Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family

See Also

Requirements

Language

GetMigrationSQL(ComparisonSession,Boolean) Method

Parameters

Return Type

Exceptions

Remarks

Example

Requirements

See Also