Removing a RowType from only one table

RawdenRawden Posts: 34
edited February 20, 2008 4:20AM in SQL Toolkit Previous Versions
Hi,

I have a project which is using the Toolkit to automate DataCompare. When in the GUI of DataCompare, you can choose to exclude a certain direction on any table. Like this:

redgate.jpg

Basically, I am looking to do this via code. I know I can do it for all the tables i.e.
dbSession.CompareDatabases(dbSource, dbDest, Mappings, SessionSettings.IncludeRecordsInOne Or SessionSettings.IncludeDifferentRecords)

but I need to do it for only one table.

Hopefully an easy one to answer :lol:

Regards,

Rawden.

Comments

  • chris.buckinghamchris.buckingham Posts: 59
    edited February 19, 2008 3:20PM
    This code snippet might give an idea how to retain the contents of the second table. Suffix 'In2' below refers to where the data exists in 'Widgets' in the second database only.

    This has been adapted from the example code FilterSQLExample.cs in SQLDataCompareSnippets.



    using System;
    using RedGate.SQL.Shared;
    using RedGate.SQLCompare.Engine;
    using RedGate.SQLDataCompare.Engine;
    using RedGate.SQLDataCompare.Engine.ResultsStore;

    namespace SQLDataCompareCodeSnippets
    {
    public class FilterSQLExample
    {
    TableDifferences m_TableDifferences;

    protected bool SyncRecord(SynchronizationRecord syncRecordObject)
    {
    if (syncRecordObject.TableName == "[dbo].[Widgets]")
    {
    if (syncRecordObject.ResultsStoreType == Row.RowType.In2)
    {
    return false;
    }
    else
    {
    return true;
    }
    }
    return true;
    }

    public void RunExample()
    {
    Database db1=new Database();
    Database db2=new Database();

    db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
    db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);

    // Create the mappings between the two databases
    TableMappings mappings = new TableMappings();
    mappings.CreateMappings(db1.Tables, db2.Tables);

    mappings.Options = new EngineDataCompareOptions(
    MappingOptions.Default,
    ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
    SqlOptions.Default);

    using (ComparisonSession session=new ComparisonSession())
    {
    session.Options = mappings.Options;
    session.CompareDatabases(db1, db2, mappings);

    m_TableDifferences = session.TableDifferences;

    // now get the ExecutionBlock containing the SQL
    // we want to run this on WidgetLive so we pass on true as the second parameter
    SqlProvider provider=new SqlProvider();
    provider.Options = session.Options;
    ExecutionBlock block;
    try
    {
    block = provider.GetMigrationSQL(session, new SelectionDelegate(this.SyncRecord), true);

    Console.WriteLine("The synchronization SQL contains {0} lines in {1} batches", block.LineCount, block.BatchCount);

    // if the ExecutionBlock was very large this could cause memory problems
    Console.WriteLine("The SQL to be run is:");
    Console.WriteLine(block.GetString());

    }
    finally
    {
    block = provider.Block;
    if (block != null)
    {
    block.Dispose(); // dispose of the objects to delete temporary files
    }
    }
    }
    db1.Dispose();
    db2.Dispose();
    }
    }
    }


    In VB
    If (syncRecordObject.TableName = "[dbo].[Widgets]") Then
    If (syncRecordObject.ResultsStoreType = Row.RowType.In2) Then
    SyncRecord = False
    Else
    SyncRecord = True
    End If
    Else
    SyncRecord = True
    End If
    Chris Buckingham
    Red-Gate support
  • Excellent. Thanks a lot.

    Sorry I forgot to mention I was using VB.NET, but I managed to work it out I think. What's the difference between Row.RowType.Different and Row.RowType.TotalDifferent though?
  • Row.RowType.Different = Data exists in both databases and is different.

    Row.RowType.TotalDifferent = The rows that differ, (Different, In1, and In2). Not a true row type

    Please check the link
    http://help.red-gate.com/help/SQLDataCo ... owType.htm

    The full API documentation is at...
    http://help.red-gate.com/help/SQLDataCo ... /index.htm

    for a full explanation.
    Chris Buckingham
    Red-Gate support
  • Ok. Thanks Chris. :lol:
Sign In or Register to comment.