Removing a RowType from only one table

Rawden

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:



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.

chris.buckingham

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

Rawden

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?

chris.buckingham

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.

Rawden

Ok. Thanks Chris. :lol: