Class used to compare two databases.

Namespace:  RedGate.SQLDataCompare.Engine
Assembly:  RedGate.SQLDataCompare.Engine (in RedGate.SQLDataCompare.Engine.dll)

Syntax

Visual Basic (Declaration)
Public Class ComparisonSession _
	Implements ICancellable, IDisposable
C#
public class ComparisonSession : ICancellable, 
	IDisposable
Visual C++
public ref class ComparisonSession : ICancellable, 
	IDisposable

Remarks

The ComparisonSession class carries out the majority of the work to compare the data in two databases.

You need to instantiate a ComparisonSession class and then call CompareDatabases(Database, Database, SchemaMappings). You can then access the results from the TableDifferences property.

Use the Status event handler to receive feedback about the comparison.

The operation can be canceled by calling CancelOperation()()().

Note that you must not re-use a ComparisonSession to carry out multiple comparisons; create a new ComparisonSession each time

SQL Data Compare makes extensive use of temporary files. It is essential that you call Dispose()()() to ensure that these are cleaned up.

Examples

These examples show complete code samples for printing the different records in two databases. It shows how to get feedback during a long operation, and how to cancel part-way through.
C# Copy Code
            using System;
            using RedGate.SQLCompare.Engine;
            using RedGate.SQLDataCompare.Engine;
            using RedGate.SQLDataCompare.Engine.ResultsStore;
             
            namespace SQLDataCompareCodeSnippets
            {
                public class ComparisonSessionExample
                {
                    public void RunExample()
                    {
                        Database db1=new Database();
                        Database db2=new Database();
                        ComparisonSession session=new ComparisonSession();
                        SchemaMappings mappings = new SchemaMappings();        
                       
                        try
                        {
                            db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"));
                            db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"));                                    
                            
                            // Create the mappings between the two databases
                            mappings.CreateMappings(db1, db2);
                            session.CompareDatabases(db1, db2, mappings);
                            
                            foreach (TableMapping mapping in mappings.TableMappings)
                            {    
                                TableDifference difference=session.TableDifferences[mapping.Obj1.FullyQualifiedName];
            
                                // Any tables that couldn't be compared we don't output the results
                                if (difference == null)
                                    continue;
                            
                                //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++;
                                        }// End of foreach field pair
                                    }
                                }// End of foreach row
                            }// End of foreach mappings
                        }
                        finally
                        {
                            session.Dispose();
                            db1.Dispose();
                            db2.Dispose();
                        }
                    }
                }
            }
                
Visual Basic Copy Code
            Option Explicit On 
             
            Imports RedGate.SQLCompare.Engine
            Imports RedGate.SQLDataCompare.Engine
            Imports RedGate.SQLDataCompare.Engine.ResultsStore
             
            Public Class ComparisonSessionExample
                Sub RunExample()
                    Dim session As New ComparisonSession
             
                    '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"))
             
             
                    Dim mappings As New SchemaMappings
                    mappings.CreateMappings(db1, db2)
             
                    'compare the databases
                    session.CompareDatabases(db1, db2, mappings)
             
                    Dim mapping As TableMapping
             
                    For Each mapping In mappings.TableMappings
                        Dim table As ViewTableSuperClass = mapping.Obj1
                        Dim difference As TableDifference = session.TableDifferences(table.FullyQualifiedName)
            
                        ' Any tables that couldn't be compared we don't output the results
                        If difference Is Nothing Then
                            Continue For
                        End If
            
                        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
                    'dispose of the objects
                    session.Dispose()
                    db1.Dispose()
                    db2.Dispose()
                End Sub
            End Class
                
C# Copy Code
            using System;
            using RedGate.SQL.Shared;
            using RedGate.SQLCompare.Engine;
            using RedGate.SQLDataCompare.Engine;
             
            namespace SQLDataCompareCodeSnippets
            {
                public class ComparisonSessionWithProgressExample
                {
                    private ComparisonSession m_Session=null;
             
                    private void StatusCallback(object sender, StatusEventArgs e)
                    {
                        //fired by the SqlProvider to indicate events
                    
                        if (e.Message!=null)
                        {
                            Console.WriteLine(e.Message);
                        }
                    
                        if (e.Percentage!=-1)
                        {
                            Console.WriteLine("{0}%", e.Percentage);
                        }
                    
                        //cancel at random
                    
                        Random r=new Random();
                        if (r.Next(100)<10)
                        {
                            m_Session.CancelOperation();
                        }
                    }
                    
                    public void RunExample()
                    {
                        Database db1=new Database();
                        Database db2=new Database();
                        TableMappings mappings = new TableMappings();
                        
                        db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"));
                        db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"));
                        
                        mappings.Options =  new EngineDataCompareOptions(MappingOptions.Default,ComparisonOptions.Default,SqlOptions.Default);
                        
                        mappings.CreateMappings(db1.Tables, db2.Tables);
                        mappings.CreateMappings(db1.Views, db2.Views);    
                        
                        using (m_Session = new ComparisonSession())
                        {
                            m_Session.Options = mappings.Options;
                            m_Session.Status+=new StatusEventHandler(StatusCallback);
                            try
                            {
                                m_Session.CompareDatabases( db1, db2, mappings, SessionSettings.Default);                    
                            }
                            catch (OperationCancelledException)
                            {
                                Console.WriteLine("Operation canceled!");
                            }
                            Console.WriteLine("Done!");
                        }
                        db1.Dispose();
                        db2.Dispose();
                    }
                }
            }
                
Visual Basic Copy Code
            Option Explicit On 
             
            Imports RedGate.SQL.Shared
            Imports RedGate.SQLCompare.Engine
            Imports RedGate.SQLDataCompare.Engine
             
            Public Class ComparisonSessionWithProgressExample
             
                Private m_Session As ComparisonSession
             
                Sub StatusCallback(ByVal sender As Object, ByVal e As StatusEventArgs)
             
                    If Not (e.Message Is Nothing) Then
                        Console.WriteLine(e.Message)
                    End If
             
                    If e.Percentage <> -1 Then
                        Console.WriteLine("{0}%", e.Percentage)
                    End If
             
                    'Cancel the operation at random
             
                    Dim r As Random = New Random
             
                    If r.Next(100) < 10 Then
                        m_Session.CancelOperation()
                    End If
                End Sub
             
                Sub RunExample()
                    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.Options = New EngineDataCompareOptions(MappingOptions.Default, ComparisonOptions.Default, SqlOptions.Default)
             
                    mappings.CreateMappings(db1.Tables, db2.Tables)
                    mappings.CreateMappings(db1.Views, db2.Views)
             
                    m_Session = New ComparisonSession
                    m_Session.Options = mappings.Options
                    m_Session.Status = New StatusEventHandler(AddressOf StatusCallback)
             
                    Try
                        m_Session.CompareDatabases(db1, db2, mappings, SessionSettings.Default)
                    Catch e As OperationCancelledException
                        Console.WriteLine("Operation canceled!")
                    End Try
             
                    'dispose of the objects to delete temporary files
                    m_Session.Dispose()
                    db1.Dispose()
                    db2.Dispose()
                End Sub
            End Class
                
C# Copy Code
            using System;
            using RedGate.SQL.Shared;
            using RedGate.SQLCompare.BackupReader;
            using RedGate.SQLCompare.Engine;
            using RedGate.SQLDataCompare.Engine;
            using RedGate.SQLDataCompare.Engine.ResultsStore;
            
            namespace SQLDataCompareCodeSnippets
            {
                public class BackupComparisonExample
                {
                    public void RunExample()
                    {
                        BackupDatabase backupDB = new BackupDatabase();
                        Database liveDB = new Database();
                        ComparisonSession session = new ComparisonSession();
                        SchemaMappings mappings = new SchemaMappings();
            
                        try
                        {
                            // First register the backup file
                            Console.WriteLine("Registering backup");
                            backupDB.Status += new StatusEventHandler(StatusCallBack);
                            backupDB.RegisterForDataCompare(new string[] { "c:\\widgetdev.bak" }, null);
            
                            // Secondly register the live database
                            Console.WriteLine("Registering live database");
                            liveDB.Status += new StatusEventHandler(StatusCallBack);
                            liveDB.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
            
                            // Create the mappings between the two databases
                            mappings.CreateMappings(backupDB, liveDB);
                            session.CompareDatabases(backupDB, liveDB, mappings);
            
                            foreach (TableMapping mapping in mappings.TableMappings)
                            {
                                TableDifference difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];
            
                                // Any tables that couldn't be compared we don't output the results
                                if (difference == null)
                                    continue;
            
                                //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++;
                                        }// End of foreach field pair
                                    }
                                }// End of foreach row
                            }// End of foreach mappings
                        }
                        finally
                        {
                            session.Dispose();
                            backupDB.Dispose();
                            liveDB.Dispose();
                        }
                    }
            
                    private static void StatusCallBack(object sender, StatusEventArgs e)
                    {
                        if (e.Message != null)
                        {
                            Console.WriteLine(e.Message);
                        }
            
                        if (e.Percentage != -1)
                        {
                            Console.WriteLine("{0}%", e.Percentage);
                        }
                    }
                }
            }
            
Visual Basic Copy Code
            Option Explicit On
            
            Imports RedGate.SQLCompare.BackupReader
            Imports RedGate.SQLCompare.Engine
            Imports RedGate.SQLDataCompare.Engine
            Imports RedGate.SQLDataCompare.Engine.ResultsStore
            Imports RedGate.SQL.Shared
            
            Public Class BackupComparisonExample
                Sub RunExample()
                    Dim session As New ComparisonSession
            
                    'register the databases for comparison
                    Dim backupDB As New BackupDatabase
                    Dim liveDB As New Database
            
                    Try
                        ' First register the backup file
                        Console.WriteLine("Registering backup")
                        backupDB.Status = New StatusEventHandler(AddressOf StatusCallback)
                        backupDB.RegisterForDataCompare(New String() {"c:\\widgetdev.bak"}, Nothing)
            
                        ' Secondly register the live database
                        Console.WriteLine("Registering live database")
                        liveDB.Status = New StatusEventHandler(AddressOf StatusCallback)
                        liveDB.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.Default)
            
            
                        Dim mappings As New SchemaMappings
                        mappings.CreateMappings(backupDB, liveDB)
            
                        'compare the databases
                        session.CompareDatabases(backupDB, liveDB, mappings)
            
                        Dim mapping As TableMapping
            
                        For Each mapping In mappings.TableMappings
                            Dim table As ViewTableSuperClass = mapping.Obj1
                            Dim difference As TableDifference = session.TableDifferences(table.FullyQualifiedName)
            
                            ' Any tables that couldn't be compared we don't output the results
                            If difference Is Nothing Then
                                Continue For
                            End If
            
                            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
                    Finally
                        'dispose of the objects
                        session.Dispose()
                        backupDB.Dispose()
                        liveDB.Dispose()
                    End Try
                End Sub
            
                Sub StatusCallback(ByVal sender As Object, ByVal e As StatusEventArgs)
            
                    If Not (e.Message Is Nothing) Then
                        Console.WriteLine(e.Message)
                    End If
            
                    If e.Percentage <> -1 Then
                        Console.WriteLine("{0}%", e.Percentage)
                    End If
                End Sub
            End Class
            
            

Inheritance Hierarchy

System..::.Object
  RedGate.SQLDataCompare.Engine..::.ComparisonSession

See Also