Know the name of modified columns in the ResultStore

SamuelSamuel Posts: 2
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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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
    
    ''' &lt;summary&gt;
    ''' Finding differences in a named table.
    ''' &lt;/summary&gt;
    Public Class StoreExample
        Sub RunExample&#40;&#41;
            Using db1 As New Database
                Using db2 As New Database
    
                    Dim sourceConnectionProperties As New ConnectionProperties&#40;Program.DevServerName, Program.DevDatabaseName&#41;
                    Dim targetConnectionProperties As New ConnectionProperties&#40;Program.LiveServerName, Program.LiveDatabaseName&#41;
                    Try
                        Console.WriteLine&#40;"Registering database " + sourceConnectionProperties.DatabaseName&#41;
                        db1.RegisterForDataCompare&#40;sourceConnectionProperties, Options.Default&#41;
                    Catch e As SqlException
                        Console.WriteLine&#40;e.Message&#41;
                        Console.WriteLine&#40;vbCrLf & _
                          "Cannot connect to database '&#123;0&#125;' on server '&#123;1&#125;'. 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&#41;
                        Return
                    End Try
                    Try
                        Console.WriteLine&#40;"Registering database " + targetConnectionProperties.DatabaseName&#41;
                        db2.RegisterForDataCompare&#40;targetConnectionProperties, Options.Default&#41;
                    Catch e As SqlException
                        Console.WriteLine&#40;e.Message&#41;
                        Console.WriteLine&#40;vbCrLf & _
                          "Cannot connect to database '&#123;0&#125;' on server '&#123;1&#125;'. 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&#41;
                        Return
                    End Try
    
                    Dim mappings As New TableMappings
                    mappings.CreateMappings&#40;db1.Tables, db2.Tables&#41;
    
                    Using session As New ComparisonSession
    
                        session.CompareDatabases&#40;db1, db2, mappings&#41;
    
                        Dim resultsReader As Reader = session.TableDifferences&#40;"&#91;dbo&#93;.&#91;Widgets&#93;"&#41;.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&#40;Of Int32, String&#41; = GetFieldMatrix&#40;resultsReader&#41;
    
                                'Work out the column associated with the colun ordinal
                                Console.Write&#40;"&#123;0&#125;=&#123;1&#125; ", fieldMatrix&#40;i&#41;, row.Values&#40;i&#41;&#41;
                            Next i
                            Console.WriteLine&#40;&#41;
                        Next
                    End Using
                End Using
            End Using
        End Sub
        Function GetFieldMatrix&#40;ByVal Results As Reader&#41; As Dictionary&#40;Of Int32, String&#41;
            Dim dict As New Dictionary&#40;Of Int32, String&#41;
            Dim i As Integer, fld As FieldPair
            For i = 0 To Results.Fields.Count - 1
                fld = Results.Fields&#40;i&#41;
                If fld.OrdinalInResults1 = fld.OrdinalInResults2 Then dict.Add&#40;fld.OrdinalInResults1, fld.Field1.Name&#41;
                If fld.OrdinalInResults1 &lt;&gt; fld.OrdinalInResults2 Then
                    dict.Add&#40;fld.OrdinalInResults1, fld.Field1.Name&#41;
                    dict.Add&#40;fld.OrdinalInResults2, fld.Field2.Name&#41;
                End If
            Next
            GetFieldMatrix = dict
        End Function
    End Class
    
Sign In or Register to comment.