Know the name of modified columns in the ResultStore

Samuel

Hi,

I have successfully created with your sample a merge algorithm for two of my databases. The problem is that when I call resultsReader.GetRow(syncRecordObject.Bookmark).Values, I can see the list of all values that have changed but I can't understand how I can see to what column theses values is associated.

Here is a sample of my code:

Private Function SyncRecord(ByVal syncRecordObject As SynchronizationRecord) As Boolean

Dim resultsReader As Reader

Dim result As Boolean

If syncRecordObject.ResultsStoreType = Row.RowType.Different Then

resultsReader = _tableDifferences(syncRecordObject.TableName()).ResultsStore.GetReader(Row.RowType.All)

SyncRecord = False

If (syncRecordObject.TableName <> String.Empty) Then

Dim myRow As Row = resultsReader.GetRow(syncRecordObject.Bookmark)


Here, I want to know to which columns the values in myRow.Values is associated;


result = True

End If
Else
result = True
End If

Return result
End Function

Thank you very much.

P.S. Sorry for the poor formatting but the very small textbox for enter the texte is not very helpful.

Brian Donahue

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