Red Gate forums :: View topic - Removing a RowType from only one table
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Toolkit Previous Versions
SQL Toolkit Previous Versions forum

Removing a RowType from only one table

Search in SQL Toolkit Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
Rawden



Joined: 07 Nov 2006
Posts: 27

PostPosted: Fri Feb 15, 2008 11:29 am    Post subject: Removing a RowType from only one table Reply with quote

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.
Code:
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 Laughing

Regards,

Rawden.
Back to top
View user's profile Send private message Send e-mail
chris.buckingham



Joined: 30 Jul 2007
Posts: 56

PostPosted: Tue Feb 19, 2008 1:54 pm    Post subject: Reply with quote

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


Last edited by chris.buckingham on Tue Feb 19, 2008 9:20 pm; edited 1 time in total
Back to top
View user's profile Send private message Send e-mail
Rawden



Joined: 07 Nov 2006
Posts: 27

PostPosted: Tue Feb 19, 2008 5:45 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message Send e-mail
chris.buckingham



Joined: 30 Jul 2007
Posts: 56

PostPosted: Tue Feb 19, 2008 9:18 pm    Post subject: Reply with quote

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/SQLDataCompareAPIv6/1/en/html/T_RedGate_SQLDataCompare_Engine_ResultsStore_Row_RowType.htm

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

for a full explanation.
_________________
Chris Buckingham
Red-Gate support
Back to top
View user's profile Send private message Send e-mail
Rawden



Joined: 07 Nov 2006
Posts: 27

PostPosted: Wed Feb 20, 2008 10:20 am    Post subject: Reply with quote

Ok. Thanks Chris. Laughing
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group