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