StoreExample

From RedGateWiki

This example shows how to use the ResultsStore class and the ResultsReader to iterate through the results of a data comparison, sorted by the values in the description column.

C#

using System;
using RedGate.SQLCompare.Engine;
using RedGate.SQLDataCompare.Engine;
using RedGate.SQLDataCompare.Engine.ResultsStore;
using System.Collections.Generic;
namespace SQLDataCompareCodeSnippets
{
/// <summary>
/// Summary description for StoreExample.
/// </summary>
public class StoreExample
{
public static void Main()
{
Database db1 = new Database();
Database db2 = new Database();
db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
using (ComparisonSession session = new ComparisonSession())
{
// Create the mappings between the two databases
TableMappings mappings = new TableMappings();
mappings.CreateMappings(db1.Tables, db2.Tables);
session.CompareDatabases(db1, db2, mappings);
// Get a ResultsReader for the Widgets table
Reader resultsReader = session.TableDifferences["[dbo].[Widgets]"].ResultsStore.GetReader();
// work out the two positions in the fields
int description1 = resultsReader.Fields["Description"].OrdinalInResults1;
int description2 = resultsReader.Fields["Description"].OrdinalInResults2;
// work out the position of the RecordID column
// this is part of the unique index we are comparing on
int recordID = resultsReader.Fields["RecordID"].OrdinalInResults1;
// sort by description in the first database
resultsReader.SortOrder.ColumnSorts.Add(new ColumnSort(resultsReader.Fields["Description"], true));
resultsReader.ApplySort();
// filter by description in the first database
ColumnConstraint c1 = new ColumnConstraint(resultsReader.Fields["Description"],true);
// DescriptionChecker.CompareTo() will contain the filtering logic
c1.ConstraintRanges.Add(new ColumnRange(new DescriptionChecker()));
resultsReader.Filter.ColumnConstraints.Add(c1);
// Apply our new filter
resultsReader.ApplyFilter(true);
foreach (Row row in resultsReader)
{
Console.Write("{0}:{1},{2}", row.Values[recordID], row.Values[description1], row.Values[description2]);
Console.WriteLine();
}
}
db1.Dispose();
db2.Dispose();
}
}
/// <summary>
/// This class implements IComparable for filtering
/// </summary>
class DescriptionChecker : IComparable
{
protected string description=String.Empty;
/// <summary>
/// The CompareTo overrides the default .net one, providing filtering logic
/// </summary>
/// <param name="Description">Data passed in by the filter</param>
/// <returns>0 to include the row in the filtered data, 1 to omit it</returns>
public int CompareTo(object Description)
{
// Omit all fields whose data contains the string "kit"
// In the Red Gate sample databases, rows 5 and 6
if (((string)Description).Contains("kit")) return 1;
else return 0;
}
}
}

Comparison results including column names

Print out the comparison results, including the original column names. The GetFieldMatrix method will return a dictionary that can resolve the offset in the row of results to the column names from the original databases.

using RedGate.SQLCompare.Engine;
using RedGate.SQLDataCompare.Engine;
using RedGate.SQLDataCompare.Engine.ResultsStore;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
/// <summary>
/// Finding differences in a named table.
/// </summary>
public class StoreExample
{
public void RunExample()
{
using (Database db1 = new Database()) {
using (Database db2 = new Database()) {
ConnectionProperties sourceConnectionProperties = new ConnectionProperties(Program.DevServerName, Program.DevDatabaseName);
ConnectionProperties targetConnectionProperties = new ConnectionProperties(Program.LiveServerName, Program.LiveDatabaseName);
try {
Console.WriteLine("Registering database " + sourceConnectionProperties.DatabaseName);
db1.RegisterForDataCompare(sourceConnectionProperties, Options.Default);
}
catch (SqlException e) {
Console.WriteLine(e.Message);
Console.WriteLine(Constants.vbCrLf + "Cannot connect to database '{0}' on server '{1}'.", sourceConnectionProperties.DatabaseName, sourceConnectionProperties.ServerName);
return;
}
try {
Console.WriteLine("Registering database " + targetConnectionProperties.DatabaseName);
db2.RegisterForDataCompare(targetConnectionProperties, Options.Default);
}
catch (SqlException e) {
Console.WriteLine(e.Message);
Console.WriteLine(Constants.vbCrLf + "Cannot connect to database '{0}' on server '{1}'.", targetConnectionProperties.DatabaseName, targetConnectionProperties.ServerName);
return;
}
TableMappings mappings = new TableMappings();
mappings.CreateMappings(db1.Tables, db2.Tables);
using (ComparisonSession session = new ComparisonSession()) {
session.CompareDatabases(db1, db2, mappings);
Reader resultsReader = session.TableDifferences("[dbo].[Widgets]").ResultsStore.GetReader;
Row row = default(Row);
int i = 0;
foreach (var row in resultsReader) {
for (i = 0; i <= row.Values.Length - 1; i++) {
Dictionary<Int32, string> fieldMatrix = GetFieldMatrix(resultsReader);
//Work out the column associated with the colun ordinal
Console.Write("{0}={1} ", fieldMatrix(i), row.Values(i));
}
Console.WriteLine();
}
}
}
}
}
public Dictionary<Int32, string> GetFieldMatrix(Reader Results)
{
Dictionary<Int32, string> dict = new Dictionary<Int32, string>();
int i = 0;
FieldPair fld = default(FieldPair);
for (i = 0; i <= Results.Fields.Count - 1; i++) {
fld = Results.Fields(i);
if (fld.OrdinalInResults1 == fld.OrdinalInResults2) dict.Add(fld.OrdinalInResults1, fld.Field1.Name);
if (fld.OrdinalInResults1 != fld.OrdinalInResults2) {
dict.Add(fld.OrdinalInResults1, fld.Field1.Name);
dict.Add(fld.OrdinalInResults2, fld.Field2.Name);
}
}
return dict;
}
}

VB

Imports System
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQLDataCompare.Engine
Imports RedGate.SQLDataCompare.Engine.ResultsStore
Imports System.Collections.Generic
Module Module1
''' <summary>
''' StoreExample.
''' </summary>
Public Class StoreExample
Shared Sub Main()
Dim db1 As New Database()
Dim db2 As New Database()
db1.RegisterForDataCompare(New ConnectionProperties(".", "WidgetDev"), Options.[Default])
db2.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.[Default])
Using session As New ComparisonSession()
' Create the mappings between the two databases
Dim mappings As New TableMappings()
mappings.CreateMappings(db1.Tables, db2.Tables)
session.CompareDatabases(db1, db2, mappings)
' Get a ResultsReader for the Widgets table
Dim resultsReader As Reader = session.TableDifferences("[dbo].[Widgets]").ResultsStore.GetReader()
' work out the two positions in the fields
Dim description1 As Integer = resultsReader.Fields("Description").OrdinalInResults1
Dim description2 As Integer = resultsReader.Fields("Description").OrdinalInResults2
' work out the position of the RecordID column
' this is part of the unique index we are comparing on
Dim recordID As Integer = resultsReader.Fields("RecordID").OrdinalInResults1
' sort by description in the first database
resultsReader.SortOrder.ColumnSorts.Add(New ColumnSort(resultsReader.Fields("Description"), True))
resultsReader.ApplySort()
' filter by description in the first database
Dim c1 As New ColumnConstraint(resultsReader.Fields("Description"), True)
' DescriptionChecker.CompareTo() will contain the filtering logic
c1.ConstraintRanges.Add(New ColumnRange(New DescriptionChecker()))
resultsReader.Filter.ColumnConstraints.Add(c1)
' Apply our new filter
resultsReader.ApplyFilter(True)
For Each row As Row In resultsReader
Console.Write("{0}:{1},{2}", row.Values(recordID), row.Values(description1), row.Values(description2))
Console.WriteLine()
Next
End Using
db1.Dispose()
db2.Dispose()
End Sub
End Class
''' <summary>
''' This class implements IComparable for filtering
''' </summary>
Class DescriptionChecker
Implements IComparable
Protected description As String = [String].Empty
''' <summary>
''' The CompareTo overrides the default .net one, providing filtering logic
''' </summary>
''' <param name="Description">Data passed in by the filter</param>
''' <returns>0 to include the row in the filtered data, 1 to omit it</returns>
Public Function CompareTo(ByVal Description As Object) As Integer Implements System.IComparable.CompareTo
' Omit all fields whose data contains the string "kit"
' In the Red Gate sample databases, rows 5 and 6
If DirectCast(Description, String).Contains("kit") Then
Return 1
Else
Return 0
End If
End Function
End Class
End Module

Comparison results including column names

Print out the comparison results, including the original column names. The GetFieldMatrix method will return a dictionary that can resolve the offset in the row of results to the column names from the original databases.

Option Explicit On
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQLDataCompare.Engine
Imports RedGate.SQLDataCompare.Engine.ResultsStore
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Collections.Generic
''' <summary>
''' Finding differences in a named table.
''' </summary>
Public Class StoreExample
Sub RunExample()
Using db1 As New Database
Using db2 As New Database
Dim sourceConnectionProperties As New ConnectionProperties(Program.DevServerName, Program.DevDatabaseName)
Dim targetConnectionProperties As New ConnectionProperties(Program.LiveServerName, Program.LiveDatabaseName)
Try
Console.WriteLine("Registering database " + sourceConnectionProperties.DatabaseName)
db1.RegisterForDataCompare(sourceConnectionProperties, Options.Default)
Catch e As SqlException
Console.WriteLine(e.Message)
Console.WriteLine(vbCrLf & _
"Cannot connect to database '{0}' on server '{1}'.", sourceConnectionProperties.DatabaseName, sourceConnectionProperties.ServerName)
Return
End Try
Try
Console.WriteLine("Registering database " + targetConnectionProperties.DatabaseName)
db2.RegisterForDataCompare(targetConnectionProperties, Options.Default)
Catch e As SqlException
Console.WriteLine(e.Message)
Console.WriteLine(vbCrLf & _
"Cannot connect to database '{0}' on server '{1}'", targetConnectionProperties.DatabaseName, targetConnectionProperties.ServerName)
Return
End Try
Dim mappings As New TableMappings
mappings.CreateMappings(db1.Tables, db2.Tables)
Using session As New ComparisonSession
session.CompareDatabases(db1, db2, mappings)
Dim resultsReader As Reader = session.TableDifferences("[dbo].[Widgets]").ResultsStore.GetReader
Dim row As Row, i As Integer
For Each row In resultsReader
For i = 0 To row.Values.Length - 1
Dim fieldMatrix As Dictionary(Of Int32, String) = GetFieldMatrix(resultsReader)
'Work out the column associated with the colun ordinal
Console.Write("{0}={1} ", fieldMatrix(i), row.Values(i))
Next i
Console.WriteLine()
Next
End Using
End Using
End Using
End Sub
Function GetFieldMatrix(ByVal Results As Reader) As Dictionary(Of Int32, String)
Dim dict As New Dictionary(Of Int32, String)
Dim i As Integer, fld As FieldPair
For i = 0 To Results.Fields.Count - 1
fld = Results.Fields(i)
If fld.OrdinalInResults1 = fld.OrdinalInResults2 Then dict.Add(fld.OrdinalInResults1, fld.Field1.Name)
If fld.OrdinalInResults1 <> fld.OrdinalInResults2 Then
dict.Add(fld.OrdinalInResults1, fld.Field1.Name)
dict.Add(fld.OrdinalInResults2, fld.Field2.Name)
End If
Next
GetFieldMatrix = dict
End Function
End Class

StoreExample

Contents

C#

Comparison results including column names

VB

Comparison results including column names

Views

Personal tools

Navigation

Search

Toolbox