StoreExample

From RedGateWiki
Jump to: navigation, search

Back to SQL Comparison SDK

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.


Contents

C#

  1. using System;
  2. using RedGate.SQLCompare.Engine;
  3. using RedGate.SQLDataCompare.Engine;
  4. using RedGate.SQLDataCompare.Engine.ResultsStore;
  5. using System.Collections.Generic;
  6.  
  7. namespace SQLDataCompareCodeSnippets
  8. {
  9.         /// <summary>
  10.         /// Summary description for StoreExample.
  11.         /// </summary>
  12.         public class StoreExample
  13.         {
  14.                 public static void Main()
  15.                 {
  16.                         Database db1 = new Database();
  17.                         Database db2 = new Database();
  18.  
  19.                         db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
  20.                         db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
  21.  
  22.                         using (ComparisonSession session = new ComparisonSession())
  23.                         {
  24.                                 // Create the mappings between the two databases
  25.                                 TableMappings mappings = new TableMappings();
  26.                                 mappings.CreateMappings(db1.Tables, db2.Tables);
  27.  
  28.                                 session.CompareDatabases(db1, db2, mappings);
  29.  
  30.                                 // Get a ResultsReader for the Widgets table
  31.                                 Reader resultsReader = session.TableDifferences["[dbo].[Widgets]"].ResultsStore.GetReader();
  32.  
  33.                                 // work out the two positions in the fields
  34.                                 int description1 = resultsReader.Fields["Description"].OrdinalInResults1;
  35.                                 int description2 = resultsReader.Fields["Description"].OrdinalInResults2;
  36.  
  37.                                 // work out the position of the RecordID column
  38.                                 // this is part of the unique index we are comparing on
  39.                                 int recordID = resultsReader.Fields["RecordID"].OrdinalInResults1;
  40.  
  41.                                 // sort by description in the first database
  42.                                 resultsReader.SortOrder.ColumnSorts.Add(new ColumnSort(resultsReader.Fields["Description"], true));
  43.                                 resultsReader.ApplySort();
  44.  
  45.                                 // filter by description in the first database
  46.                                 ColumnConstraint c1 = new ColumnConstraint(resultsReader.Fields["Description"],true);
  47.                                 // DescriptionChecker.CompareTo() will contain the filtering logic
  48.                                 c1.ConstraintRanges.Add(new ColumnRange(new DescriptionChecker()));
  49.                                 resultsReader.Filter.ColumnConstraints.Add(c1);
  50.                                 // Apply our new filter
  51.                                 resultsReader.ApplyFilter(true);
  52.  
  53.                                 foreach (Row row in resultsReader)
  54.                                 {
  55.                                         Console.Write("{0}:{1},{2}", row.Values[recordID], row.Values[description1], row.Values[description2]);
  56.                                         Console.WriteLine();
  57.                                 }
  58.                         }
  59.                         db1.Dispose();
  60.                         db2.Dispose();
  61.                 }
  62.         }
  63.         /// <summary>
  64.         /// This class implements IComparable for filtering
  65.         /// </summary>
  66.         class DescriptionChecker : IComparable
  67.         {
  68.                 protected string description=String.Empty;
  69.                 /// <summary>
  70.                 /// The CompareTo overrides the default .net one, providing filtering logic
  71.                 /// </summary>
  72.                 /// <param name="Description">Data passed in by the filter</param>
  73.                 /// <returns>0 to include the row in the filtered data, 1 to omit it</returns>
  74.                 public int CompareTo(object Description)
  75.                 {
  76.                         // Omit all fields whose data contains the string "kit"
  77.                         // In the Red Gate sample databases, rows 5 and 6
  78.                         if (((string)Description).Contains("kit")) return 1;
  79.                         else return 0;
  80.                 }
  81.         }
  82. }

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.


  1.   using RedGate.SQLCompare.Engine;
  2.   using RedGate.SQLDataCompare.Engine;
  3.   using RedGate.SQLDataCompare.Engine.ResultsStore;
  4.   using System.Data.SqlClient;
  5.   using System.Collections;
  6.   using System.Collections.Generic;
  7.  
  8.   /// <summary>
  9.   /// Finding differences in a named table.
  10.   /// </summary>
  11.   public class StoreExample
  12.   {
  13.           public void RunExample()
  14.           {
  15.                   using (Database db1 = new Database()) {
  16.                           using (Database db2 = new Database()) {
  17.  
  18.                                   ConnectionProperties sourceConnectionProperties = new ConnectionProperties(Program.DevServerName, Program.DevDatabaseName);
  19.                                   ConnectionProperties targetConnectionProperties = new ConnectionProperties(Program.LiveServerName, Program.LiveDatabaseName);
  20.                                   try {
  21.                                           Console.WriteLine("Registering database " + sourceConnectionProperties.DatabaseName);
  22.                                           db1.RegisterForDataCompare(sourceConnectionProperties, Options.Default);
  23.                                   }
  24.                                   catch (SqlException e) {
  25.                                           Console.WriteLine(e.Message);
  26.                                           Console.WriteLine(Constants.vbCrLf + "Cannot connect to database '{0}' on server '{1}'.", sourceConnectionProperties.DatabaseName, sourceConnectionProperties.ServerName);
  27.                                           return;
  28.                                   }
  29.                                   try {
  30.                                         Console.WriteLine("Registering database " + targetConnectionProperties.DatabaseName);
  31.                                           db2.RegisterForDataCompare(targetConnectionProperties, Options.Default);
  32.                                   }
  33.                                   catch (SqlException e) {
  34.                                           Console.WriteLine(e.Message);
  35.                                           Console.WriteLine(Constants.vbCrLf + "Cannot connect to database '{0}' on server '{1}'.", targetConnectionProperties.DatabaseName, targetConnectionProperties.ServerName);
  36.                                           return;
  37.                                   }
  38.  
  39.                                   TableMappings mappings = new TableMappings();
  40.                                   mappings.CreateMappings(db1.Tables, db2.Tables);
  41.  
  42.                                   using (ComparisonSession session = new ComparisonSession()) {
  43.  
  44.                                           session.CompareDatabases(db1, db2, mappings);
  45.  
  46.                                           Reader resultsReader = session.TableDifferences("[dbo].[Widgets]").ResultsStore.GetReader;
  47.  
  48.                                           Row row = default(Row);
  49.                                           int i = 0;
  50.                                           foreach (var row in resultsReader) {
  51.                                                   for (i = 0; i <= row.Values.Length - 1; i++) {
  52.                                                           Dictionary<Int32, string> fieldMatrix = GetFieldMatrix(resultsReader);
  53.  
  54.                                                           //Work out the column associated with the colun ordinal
  55.                                                           Console.Write("{0}={1} ", fieldMatrix(i), row.Values(i));
  56.                                                   }
  57.                                                   Console.WriteLine();
  58.                                           }
  59.                                   }
  60.                           }
  61.                   }
  62.           }
  63.           public Dictionary<Int32, string> GetFieldMatrix(Reader Results)
  64.           {
  65.                   Dictionary<Int32, string> dict = new Dictionary<Int32, string>();
  66.                   int i = 0;
  67.                   FieldPair fld = default(FieldPair);
  68.                   for (i = 0; i <= Results.Fields.Count - 1; i++) {
  69.                           fld = Results.Fields(i);
  70.                           if (fld.OrdinalInResults1 == fld.OrdinalInResults2) dict.Add(fld.OrdinalInResults1, fld.Field1.Name);
  71.                           if (fld.OrdinalInResults1 != fld.OrdinalInResults2) {
  72.                                   dict.Add(fld.OrdinalInResults1, fld.Field1.Name);
  73.                                   dict.Add(fld.OrdinalInResults2, fld.Field2.Name);
  74.                           }
  75.                   }
  76.                   return dict;
  77.           }
  78.   }

VB

  1. Imports System
  2. Imports RedGate.SQLCompare.Engine
  3. Imports RedGate.SQLDataCompare.Engine
  4. Imports RedGate.SQLDataCompare.Engine.ResultsStore
  5. Imports System.Collections.Generic
  6.  
  7. Module Module1
  8.         ''' <summary>
  9.         ''' StoreExample.
  10.         ''' </summary>
  11.         Public Class StoreExample
  12.                 Shared Sub Main()
  13.                         Dim db1 As New Database()
  14.                         Dim db2 As New Database()
  15.  
  16.                         db1.RegisterForDataCompare(New ConnectionProperties(".", "WidgetDev"), Options.[Default])
  17.                         db2.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.[Default])
  18.  
  19.                         Using session As New ComparisonSession()
  20.                                 ' Create the mappings between the two databases
  21.                                 Dim mappings As New TableMappings()
  22.                                 mappings.CreateMappings(db1.Tables, db2.Tables)
  23.  
  24.                                 session.CompareDatabases(db1, db2, mappings)
  25.  
  26.                                 ' Get a ResultsReader for the Widgets table
  27.                                 Dim resultsReader As Reader = session.TableDifferences("[dbo].[Widgets]").ResultsStore.GetReader()
  28.  
  29.                                 ' work out the two positions in the fields
  30.                                 Dim description1 As Integer = resultsReader.Fields("Description").OrdinalInResults1
  31.                                 Dim description2 As Integer = resultsReader.Fields("Description").OrdinalInResults2
  32.  
  33.                                 ' work out the position of the RecordID column
  34.                                 ' this is part of the unique index we are comparing on
  35.                                 Dim recordID As Integer = resultsReader.Fields("RecordID").OrdinalInResults1
  36.  
  37.                                 ' sort by description in the first database
  38.                                 resultsReader.SortOrder.ColumnSorts.Add(New ColumnSort(resultsReader.Fields("Description"), True))
  39.                                 resultsReader.ApplySort()
  40.  
  41.                                 ' filter by description in the first database
  42.                                 Dim c1 As New ColumnConstraint(resultsReader.Fields("Description"), True)
  43.                                 ' DescriptionChecker.CompareTo() will contain the filtering logic
  44.                                 c1.ConstraintRanges.Add(New ColumnRange(New DescriptionChecker()))
  45.                                 resultsReader.Filter.ColumnConstraints.Add(c1)
  46.                                 ' Apply our new filter
  47.                                 resultsReader.ApplyFilter(True)
  48.  
  49.                                 For Each row As Row In resultsReader
  50.                                         Console.Write("{0}:{1},{2}", row.Values(recordID), row.Values(description1), row.Values(description2))
  51.                                         Console.WriteLine()
  52.                                 Next
  53.                         End Using
  54.                         db1.Dispose()
  55.                         db2.Dispose()
  56.                 End Sub
  57.         End Class
  58.         ''' <summary>
  59.         ''' This class implements IComparable for filtering
  60.         ''' </summary>
  61.         Class DescriptionChecker
  62.                 Implements IComparable
  63.                 Protected description As String = [String].Empty
  64.                 ''' <summary>
  65.                 ''' The CompareTo overrides the default .net one, providing filtering logic
  66.                 ''' </summary>
  67.                 ''' <param name="Description">Data passed in by the filter</param>
  68.                 ''' <returns>0 to include the row in the filtered data, 1 to omit it</returns>
  69.                 Public Function CompareTo(ByVal Description As Object) As Integer Implements System.IComparable.CompareTo
  70.                         ' Omit all fields whose data contains the string "kit"
  71.                         ' In the Red Gate sample databases, rows 5 and 6
  72.                         If DirectCast(Description, String).Contains("kit") Then
  73.                                 Return 1
  74.                         Else
  75.                                 Return 0
  76.                         End If
  77.                 End Function
  78.         End Class
  79. 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.


  1. Option Explicit On
  2.  
  3. Imports RedGate.SQLCompare.Engine
  4. Imports RedGate.SQLDataCompare.Engine
  5. Imports RedGate.SQLDataCompare.Engine.ResultsStore
  6. Imports System.Data.SqlClient
  7. Imports System.Collections
  8. Imports System.Collections.Generic
  9.  
  10. ''' <summary>
  11. ''' Finding differences in a named table.
  12. ''' </summary>
  13. Public Class StoreExample
  14.         Sub RunExample()
  15.                 Using db1 As New Database
  16.                         Using db2 As New Database
  17.  
  18.                                 Dim sourceConnectionProperties As New ConnectionProperties(Program.DevServerName, Program.DevDatabaseName)
  19.                                 Dim targetConnectionProperties As New ConnectionProperties(Program.LiveServerName, Program.LiveDatabaseName)
  20.                                 Try
  21.                                         Console.WriteLine("Registering database " + sourceConnectionProperties.DatabaseName)
  22.                                         db1.RegisterForDataCompare(sourceConnectionProperties, Options.Default)
  23.                                 Catch e As SqlException
  24.                                         Console.WriteLine(e.Message)
  25.                                         Console.WriteLine(vbCrLf & _
  26.                                             "Cannot connect to database '{0}' on server '{1}'.", sourceConnectionProperties.DatabaseName, sourceConnectionProperties.ServerName)
  27.                                         Return
  28.                                 End Try
  29.                                 Try
  30.                                         Console.WriteLine("Registering database " + targetConnectionProperties.DatabaseName)
  31.                                         db2.RegisterForDataCompare(targetConnectionProperties, Options.Default)
  32.                                 Catch e As SqlException
  33.                                         Console.WriteLine(e.Message)
  34.                                         Console.WriteLine(vbCrLf & _
  35.                                             "Cannot connect to database '{0}' on server '{1}'", targetConnectionProperties.DatabaseName, targetConnectionProperties.ServerName)
  36.                                         Return
  37.                                 End Try
  38.  
  39.                                 Dim mappings As New TableMappings
  40.                                 mappings.CreateMappings(db1.Tables, db2.Tables)
  41.  
  42.                                 Using session As New ComparisonSession
  43.  
  44.                                         session.CompareDatabases(db1, db2, mappings)
  45.  
  46.                                         Dim resultsReader As Reader = session.TableDifferences("[dbo].[Widgets]").ResultsStore.GetReader
  47.  
  48.                                         Dim row As Row, i As Integer
  49.                                         For Each row In resultsReader
  50.                                                 For i = 0 To row.Values.Length - 1
  51.                                                         Dim fieldMatrix As Dictionary(Of Int32, String) = GetFieldMatrix(resultsReader)
  52.  
  53.                                                         'Work out the column associated with the colun ordinal
  54.                                                         Console.Write("{0}={1} ", fieldMatrix(i), row.Values(i))
  55.                                                 Next i
  56.                                                 Console.WriteLine()
  57.                                         Next
  58.                                 End Using
  59.                         End Using
  60.                 End Using
  61.         End Sub
  62.         Function GetFieldMatrix(ByVal Results As Reader) As Dictionary(Of Int32, String)
  63.                 Dim dict As New Dictionary(Of Int32, String)
  64.                 Dim i As Integer, fld As FieldPair
  65.                 For i = 0 To Results.Fields.Count - 1
  66.                         fld = Results.Fields(i)
  67.                         If fld.OrdinalInResults1 = fld.OrdinalInResults2 Then dict.Add(fld.OrdinalInResults1, fld.Field1.Name)
  68.                         If fld.OrdinalInResults1 <> fld.OrdinalInResults2 Then
  69.                                 dict.Add(fld.OrdinalInResults1, fld.Field1.Name)
  70.                                 dict.Add(fld.OrdinalInResults2, fld.Field2.Name)
  71.                         End If
  72.                 Next
  73.                 GetFieldMatrix = dict
  74.         End Function
  75. End Class
Personal tools