Excluding all columns with specified name

From RedGateWiki

This example shows how data in a column with a specified name can be excluded from all tables in the database. After completing the mapping process, the ExcludeColumns method is applied to each table mapping to remove the field mapping for each column with a certain name, in this case, the Description column.

C#

using System;
using RedGate.SQLCompare.Engine;
using RedGate.SQLDataCompare.Engine;
using RedGate.SQLDataCompare.Engine.ResultsStore;
using RedGate.Shared.SQL;
using RedGate.Shared.Utils;
namespace SQLDataCompareCodeSnippets
{
public class SDC_Atomic_Column_Exclusion
{
static void Main()
{
Database db1 = new Database();
Database db2 = new Database();
ComparisonSession session = new ComparisonSession();
SchemaMappings mappings = new SchemaMappings();
try
{
db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
// Create the mappings between the two databases
mappings.TableMappings.CreateMappings(db1.Tables, db2.Tables);
//Exclude the Description column from all tables (Widgets, WidgetDescriptions)
ExcludeColumns(mappings.TableMappings,"Description");
//Compare the databases
session.CompareDatabases(db1, db2, mappings);
foreach (TableMapping mapping in mappings.TableMappings)
{
TableDifference difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];
//Any tables that couldn't be compared we don't output the results
if (difference == null)
continue;
//loop through all the rows
foreach (Row row in difference.ResultsStore)
{
//go through the non same records
if (row.Type != Row.RowType.Same)
{
Console.WriteLine("{0} Row {1} type {2}", mapping.Obj1.FullyQualifiedName, 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 unique index we are comparing on
object value = row.Values[field1];
Console.WriteLine("*{0}\t{1}", field.Field(false).Name, value.ToString());
}
i++;
}// End of foreach field pair
}
}// End of foreach row
}// End of foreach field mappings
}
finally
{
session.Dispose();
db1.Dispose();
db2.Dispose();
}
}
private static void ExcludeColumns(TableMappings mappings, string ColumnName)
{
foreach (TableMapping mapping in mappings)
{
foreach (FieldMapping m in mapping.FieldMappings)
{
if (m == mapping.FieldMappings[ColumnName])
m.Include = false;
}
}
}
}
}

VB

Imports System
Imports RedGate.SQLCompare.Engine
Imports RedGate.SQLDataCompare.Engine
Imports RedGate.SQLDataCompare.Engine.ResultsStore
Imports RedGate.Shared.SQL
Imports RedGate.Shared.Utils
Module Module1
Sub Main()
Dim db1 As New Database()
Dim db2 As New Database()
Dim session As New ComparisonSession()
Dim mappings As New SchemaMappings()
Try
db1.RegisterForDataCompare(New ConnectionProperties(".", "WidgetDev"), Options.[Default])
db2.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.[Default])
' Create the mappings between the two databases
mappings.TableMappings.CreateMappings(db1.Tables, db2.Tables)
'Exclude the Description column from all tables (Widgets, WidgetDescriptions)
ExcludeColumns(mappings.TableMappings, "Description")
'Compare the databases
session.CompareDatabases(db1, db2, mappings)
For Each mapping As TableMapping In mappings.TableMappings
Dim difference As TableDifference = session.TableDifferences(mapping.Obj1.FullyQualifiedName)
'Any tables that couldn't be compared we don't output the results
If difference Is Nothing Then
Continue For
End If
'loop through all the rows
For Each row__1 As Row In difference.ResultsStore
'go through the non same records
If row__1.Type <> Row.RowType.Same Then
Console.WriteLine("{0} Row {1} type {2}", mapping.Obj1.FullyQualifiedName, row__1.Index, row__1.Type.ToString())
Dim i As Integer = 0
For Each field As FieldPair In difference.ResultsStore.Fields
Dim field1 As Integer = field.OrdinalInResults1
Dim field2 As Integer = field.OrdinalInResults2
If field1 <> field2 Then
' Get the values
Dim value1 As Object = row__1.Values(field1)
Dim value2 As Object = row__1.Values(field2)
If value1 Is Nothing Then
value1 = "NULL"
End If
If value2 Is Nothing Then
value2 = "NULL"
End If
Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & "{2}" & vbTab & "{3}", field.Field(False).Name, value1.ToString(), If(row__1.FieldDifferent(i), "<>", "=="), value2.ToString())
Else
' This is part of the unique index we are comparing on
Dim value As Object = row__1.Values(field1)
Console.WriteLine("*{0}" & vbTab & "{1}", field.Field(False).Name, value.ToString())
End If
i += 1
' End of foreach field pair
Next
End If
' End of foreach row
Next
' End of foreach field mappings
Next
Finally
session.Dispose()
db1.Dispose()
db2.Dispose()
End Try
End Sub
Private Sub ExcludeColumns(ByVal mappings As TableMappings, ByVal ColumnName As String)
For Each mapping As TableMapping In mappings
For Each m As FieldMapping In mapping.FieldMappings
If m.Equals(mapping.FieldMappings(ColumnName)) Then
m.Include = False
End If
Next
Next
End Sub
End Module

Excluding all columns with specified name

C#

VB

Views

Personal tools

Navigation

Search

Toolbox