Excluding all columns with specified name

From RedGateWiki
Jump to: navigation, search

Back to SQL Comparison SDK

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#

  1. using System;
  2. using RedGate.SQLCompare.Engine;
  3. using RedGate.SQLDataCompare.Engine;
  4. using RedGate.SQLDataCompare.Engine.ResultsStore;
  5. using RedGate.Shared.SQL;
  6. using RedGate.Shared.Utils;
  7.  
  8. namespace SQLDataCompareCodeSnippets
  9. {
  10.         public class SDC_Atomic_Column_Exclusion
  11.         {
  12.  
  13.                 static void Main()
  14.                 {
  15.                         Database db1 = new Database();
  16.                         Database db2 = new Database();
  17.                         ComparisonSession session = new ComparisonSession();
  18.                         SchemaMappings mappings = new SchemaMappings();
  19.  
  20.                         try
  21.                         {
  22.                                 db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
  23.                                 db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
  24.  
  25.                                 // Create the mappings between the two databases
  26.                                 mappings.TableMappings.CreateMappings(db1.Tables, db2.Tables);
  27.  
  28.                                 //Exclude the Description column from all tables (Widgets, WidgetDescriptions)
  29.                                 ExcludeColumns(mappings.TableMappings,"Description");
  30.  
  31.                                 //Compare the databases
  32.                                 session.CompareDatabases(db1, db2, mappings);
  33.  
  34.                                 foreach (TableMapping mapping in mappings.TableMappings)
  35.                                 {
  36.                                         TableDifference difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];
  37.  
  38.                                         //Any tables that couldn't be compared we don't output the results
  39.                                         if (difference == null)
  40.                                                 continue;
  41.  
  42.                                         //loop through all the rows
  43.                                         foreach (Row row in difference.ResultsStore)
  44.                                         {
  45.                                                 //go through the non same records
  46.                                                 if (row.Type != Row.RowType.Same)
  47.                                                 {
  48.                                                         Console.WriteLine("{0} Row {1} type {2}", mapping.Obj1.FullyQualifiedName, row.Index, row.Type.ToString());
  49.                                                         int i = 0;
  50.                                                         foreach (FieldPair field in difference.ResultsStore.Fields)
  51.                                                         {
  52.                                                                 int field1 = field.OrdinalInResults1;
  53.                                                                 int field2 = field.OrdinalInResults2;
  54.  
  55.                                                                 if (field1 != field2)
  56.                                                                 {
  57.                                                                         // Get the values
  58.                                                                         object value1 = row.Values[field1];
  59.                                                                         object value2 = row.Values[field2];
  60.                                                                         if (value1 == null)
  61.                                                                                 value1 = "NULL";
  62.                                                                         if (value2 == null)
  63.                                                                                 value2 = "NULL";
  64.                                                                         Console.WriteLine("{0}\t{1}\t{2}\t{3}", field.Field(false).Name, value1.ToString(), row.FieldDifferent(i) ? "<>" : "==", value2.ToString());
  65.                                                                 }
  66.                                                                 else
  67.                                                                 {
  68.                                                                         // This is part of the unique index we are comparing on
  69.                                                                         object value = row.Values[field1];
  70.                                                                         Console.WriteLine("*{0}\t{1}", field.Field(false).Name, value.ToString());
  71.                                                                 }
  72.                                                                 i++;
  73.  
  74.                                                         }// End of foreach field pair
  75.                                                 }
  76.  
  77.                                         }// End of foreach row
  78.                                 }// End of foreach field mappings
  79.                         }
  80.                         finally
  81.                         {
  82.                                 session.Dispose();
  83.                                 db1.Dispose();
  84.                                 db2.Dispose();
  85.                         }
  86.                 }
  87.  
  88.                 private static void ExcludeColumns(TableMappings mappings, string ColumnName)
  89.                 {
  90.                         foreach (TableMapping mapping in mappings)
  91.                         {
  92.                                 foreach (FieldMapping m in mapping.FieldMappings)
  93.                                 {
  94.                                         if (m == mapping.FieldMappings[ColumnName])
  95.                                                 m.Include = false;
  96.  
  97.                                 }
  98.                         }
  99.                 }
  100.         }
  101. }

VB

  1. Imports System
  2. Imports RedGate.SQLCompare.Engine
  3. Imports RedGate.SQLDataCompare.Engine
  4. Imports RedGate.SQLDataCompare.Engine.ResultsStore
  5. Imports RedGate.Shared.SQL
  6. Imports RedGate.Shared.Utils
  7.  
  8. Module Module1
  9.         Sub Main()
  10.                 Dim db1 As New Database()
  11.                 Dim db2 As New Database()
  12.                 Dim session As New ComparisonSession()
  13.                 Dim mappings As New SchemaMappings()
  14.  
  15.                 Try
  16.                         db1.RegisterForDataCompare(New ConnectionProperties(".", "WidgetDev"), Options.[Default])
  17.                         db2.RegisterForDataCompare(New ConnectionProperties(".", "WidgetLive"), Options.[Default])
  18.  
  19.                         ' Create the mappings between the two databases
  20.                         mappings.TableMappings.CreateMappings(db1.Tables, db2.Tables)
  21.  
  22.                         'Exclude the Description column from all tables (Widgets, WidgetDescriptions)
  23.                         ExcludeColumns(mappings.TableMappings, "Description")
  24.  
  25.                         'Compare the databases
  26.                         session.CompareDatabases(db1, db2, mappings)
  27.  
  28.                         For Each mapping As TableMapping In mappings.TableMappings
  29.                                 Dim difference As TableDifference = session.TableDifferences(mapping.Obj1.FullyQualifiedName)
  30.  
  31.                                 'Any tables that couldn't be compared we don't output the results
  32.                                 If difference Is Nothing Then
  33.                                         Continue For
  34.                                 End If
  35.  
  36.                                 'loop through all the rows
  37.                                 For Each row__1 As Row In difference.ResultsStore
  38.                                         'go through the non same records
  39.                                         If row__1.Type <> Row.RowType.Same Then
  40.                                                 Console.WriteLine("{0} Row {1} type {2}", mapping.Obj1.FullyQualifiedName, row__1.Index, row__1.Type.ToString())
  41.                                                 Dim i As Integer = 0
  42.                                                 For Each field As FieldPair In difference.ResultsStore.Fields
  43.                                                         Dim field1 As Integer = field.OrdinalInResults1
  44.                                                         Dim field2 As Integer = field.OrdinalInResults2
  45.  
  46.                                                         If field1 <> field2 Then
  47.                                                                 ' Get the values
  48.                                                                 Dim value1 As Object = row__1.Values(field1)
  49.                                                                 Dim value2 As Object = row__1.Values(field2)
  50.                                                                 If value1 Is Nothing Then
  51.                                                                         value1 = "NULL"
  52.                                                                 End If
  53.                                                                 If value2 Is Nothing Then
  54.                                                                         value2 = "NULL"
  55.                                                                 End If
  56.                                                                 Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & "{2}" & vbTab & "{3}", field.Field(False).Name, value1.ToString(), If(row__1.FieldDifferent(i), "<>", "=="), value2.ToString())
  57.                                                         Else
  58.                                                                 ' This is part of the unique index we are comparing on
  59.                                                                 Dim value As Object = row__1.Values(field1)
  60.                                                                 Console.WriteLine("*{0}" & vbTab & "{1}", field.Field(False).Name, value.ToString())
  61.                                                         End If
  62.  
  63.                                                         i += 1
  64.                                                         ' End of foreach field pair
  65.                                                 Next
  66.  
  67.                                         End If
  68.                                         ' End of foreach row
  69.                                 Next
  70.                                 ' End of foreach field mappings
  71.                         Next
  72.                 Finally
  73.                         session.Dispose()
  74.                         db1.Dispose()
  75.                         db2.Dispose()
  76.                 End Try
  77.         End Sub
  78.  
  79.         Private Sub ExcludeColumns(ByVal mappings As TableMappings, ByVal ColumnName As String)
  80.                 For Each mapping As TableMapping In mappings
  81.                         For Each m As FieldMapping In mapping.FieldMappings
  82.                                 If m.Equals(mapping.FieldMappings(ColumnName)) Then
  83.                                         m.Include = False
  84.  
  85.                                 End If
  86.                         Next
  87.                 Next
  88.         End Sub
  89. End Module
Personal tools