Red Gate forums :: View topic - Know the name of modified columns in the ResultStore
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

Know the name of modified columns in the ResultStore

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
Samuel



Joined: 28 Apr 2010
Posts: 1

PostPosted: Wed Apr 28, 2010 2:43 am    Post subject: Know the name of modified columns in the ResultStore Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6641

PostPosted: Thu Apr 29, 2010 1:23 pm    Post subject: Reply with quote

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.
Code:
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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group