Public Class TableMappings _
	Inherits Mappings

public class TableMappings : Mappings

public ref class TableMappings : public Mappings

            using System;
            using RedGate.SQLCompare.Engine;
            using RedGate.SQLDataCompare.Engine;
            using RedGate.SQLDataCompare.Engine.ResultsStore;
             
            namespace RedGate.SQLDataCompare.ExampleTests
            {
                public class TableMappingExample
                {
                    public void RunExample()
                    {
                        Database db1=new Database();
                        Database db2=new Database();
                        ComparisonSession session=new ComparisonSession();
                        TableMappings mappings = new TableMappings();        
                       
                        try
                        {
                            db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"));
                            db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"));                                    
                            
                            // Create the mappings between a certain table
                            TableMapping tableMapping = (TableMapping)mappings.Join(db1.Tables["[dbo].[WidgetPrices]"], db2.Tables["[dbo].[WidgetPrices]"]);
             
                            // Set the custom comparison key for the table
                            tableMapping.MatchingMappings.Clear();
                            tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["WidgetID"]);
                            tableMapping.RefreshMappingStatus();
             
                            // Set the where clause for the comparison
                            tableMapping.Where = new WhereClause("Active = 'Y'");
             
                            // Peform the comparison
                            session.CompareDatabases(db1, db2, mappings);
                            
                            TableDifference difference=session.TableDifferences["[dbo].[WidgetPrices]"];
             
                            // Loop through all the rows
                            foreach(Row row in difference.ResultsStore)
                            {
                                Console.WriteLine("Row {0} type {1}", row.Index, row.Type.ToString());
                                int i=0;
                                foreach (FieldPair field in difference.ResultsStore.Fields)
                                {
                                    int field1=field.OrdinalInResults1;
                                    int field2=field.OrdinalInResults2;
                            
                                    if (field1 != field2)
                                    {
                                        //get the values
                                        object value1=row.Values[field1];
                                        object value2=row.Values[field2];
                                        if (value1 == null)
                                            value1="NULL";
                                        if (value2 == null)
                                            value2="NULL";
                                        Console.WriteLine("{0}\t{1}\t{2}\t{3}",field.Field(false).Name, value1.ToString(),row.FieldDifferent(i)?"<>":"==",  value2.ToString());
                                    }
                                    else
                                    {
                                        //this is part of the custom index we are comparing on
                                        object value=row.Values[field1];    
                                        Console.WriteLine("*{0}\t{1}",field.Field(false).Name, value.ToString());
                                    }
                                    i++;
                                }
                            }
                        }
                        finally
                        {
                            session.Dispose();
                            db1.Dispose();
                            db2.Dispose();
                        }
                    }
                }
            }
                

            Option Explicit On 
             
            Imports RedGate.SQLCompare.Engine
            Imports RedGate.SQLDataCompare.Engine
            Imports RedGate.SQLDataCompare.Engine.ResultsStore
             
            Public Class TableMappingExample
                Sub RunExample()
                    Dim session As New ComparisonSession
             
                    'register the databases for comparison
                    Dim db1 As New Database
                    Dim db2 As New Database
             
                    db1.RegisterForDataCompare(New ConnectionProperties(".", "WidgetDev"))
                    db2.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"))
             
                    Dim mappings As New TableMappings
             
                    Dim tableMapping As tableMapping = mappings.Join(db1.Tables("[dbo].[WidgetPrices]"), db2.Tables("[dbo].[WidgetPrices]"))
             
                    ' Set the custom comparison key for the table
                    tableMapping.MatchingMappings.Clear()
                    tableMapping.MatchingMappings.Add(tableMapping.FieldMappings("WidgetID"))
                    tableMapping.RefreshMappingStatus()
             
                    ' Set the where clause for the comparison
                    tableMapping.Where = New WhereClause("Active = 'Y'")
             
                    'compare the databases
                    session.CompareDatabases(db1, db2, mappings)
             
                    Dim mapping As TableMapping
             
             
                    Dim difference As TableDifference = session.TableDifferences("[dbo].[WidgetPrices]")
                    Dim row As row
                    For Each row In difference.ResultsStore  'loop through all the rows
                        Dim field As FieldPair
                        Dim i As Int32 = 0
                        Console.WriteLine("Row {0} type {1}", row.Index, row.Type.ToString())
                        For Each field In difference.ResultsStore.Fields
                            'work out where about in the results the field data is stored
                            'if we were comparing identical records, or records present in one
                            'database but not the other then we would not need to 
                            'use the OrdinalInResults1 and OrdinalInResults2 properties
                            'but just OrdinalInResults
                            Dim field1 As Int32 = field.OrdinalInResults1
                            Dim field2 As Int32 = field.OrdinalInResults2
             
                            If (field1 <> field2) Then
                                'get the values
                                Dim value1 As Object = row.Values(field1)
                                Dim value2 As Object = row.Values(field2)
                                If (value1 Is Nothing) Then
                                    value1 = "NULL"
                                End If
                                If (value2 Is Nothing) Then
                                    value2 = "NULL"
                                End If
                                If row.FieldDifferent(i) Then
                                    Console.WriteLine("{0}:{1} <> {2}", field.Field(False).Name, value1.ToString(), value2.ToString())
                                Else
                                    Console.WriteLine("{0}:{1} == {2}", field.Field(False).Name, value1.ToString(), value2.ToString())
                                End If
                            Else
                                'this is part of the unique index we are comparing on
                                Dim value As Object = row.Values(field1)
                                Console.WriteLine("*{0}:{1}", field.Field(False).Name, value.ToString())
                            End If
                            i += 1
                        Next
                    Next
                    'dispose of the objects
                    session.Dispose()
                    db1.Dispose()
                    db2.Dispose()
                End Sub
            End Class