Removing a RowType from only one table

Forum for users of SQL Toolkit 3,4,and 5

Removing a RowType from only one table

Postby Rawden » Fri Feb 15, 2008 11:29 am

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:

Image

Basically, I am looking to do this via code. I know I can do it for all the tables i.e.
Code: Select all
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.
Rawden
 
Posts: 27
Joined: Tue Nov 07, 2006 2:07 pm

Postby chris.buckingham » Tue Feb 19, 2008 1:54 pm

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
Last edited by chris.buckingham on Tue Feb 19, 2008 9:20 pm, edited 1 time in total.
Chris Buckingham
Red-Gate support
chris.buckingham
 
Posts: 56
Joined: Mon Jul 30, 2007 1:39 pm

Postby Rawden » Tue Feb 19, 2008 5:45 pm

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?
Rawden
 
Posts: 27
Joined: Tue Nov 07, 2006 2:07 pm

Postby chris.buckingham » Tue Feb 19, 2008 9:18 pm

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
chris.buckingham
 
Posts: 56
Joined: Mon Jul 30, 2007 1:39 pm

Postby Rawden » Wed Feb 20, 2008 10:20 am

Ok. Thanks Chris. :lol:
Rawden
 
Posts: 27
Joined: Tue Nov 07, 2006 2:07 pm


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests

cron