Know the name of modified columns in the ResultStore

Comments

• Options

  Brian Donahue Posts: 6,590 Bronze 1

  April 29, 2010 8:23AM

  Hello,
  
  I could not find any function in the SQL Data Compare SDK that will resolve a Row column to a column name in the ResultsReader, so I wrote my own. The GetFieldMatrix function will return a dictionary keyed on the row's column ordinal and valued on the column name.
  
  This works for me, so I hope it's useful to you.

  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}'. The most common causes of this error are:" & vbCrLf & _
                        "        o The sample databases are not installed" & vbCrLf & _
                        "        o ServerName not set to the location of the target database" & vbCrLf & _
                        "        o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor" & vbCrLf & _
                        "        o Remote connections not enabled", 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}'. The most common causes of this error are:" & vbCrLf & _
                        "        o The sample databases are not installed" & vbCrLf & _
                        "        o ServerName not set to the location of the target database" & vbCrLf & _
                        "        o For sql server authentication, username and password incorrect or not supplied in ConnectionProperties constructor" & vbCrLf & _
                        "        o Remote connections not enabled", 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
  

  0 · Share on Twitter