| Author |
Message |
jubayer92
Joined: 18 Jun 2008
Posts: 2
|
 Posted: Wed Jun 18, 2008 6:43 pm Post subject: sync and generate scripts for selected rows of a table |
 |
|
I was trying to mimic what SQL Comparer does - sync and generate scripts for selected rows for a table.
In my UI, I am displaying all rows for a compared tables and would like to sync and generate scripts for selected rows.
How I can accomplish this? Any help is appreciated.
thanks. |
|
| Back to top |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6341
Location: Red Gate Software
|
| Posted: Mon Jun 23, 2008 6:27 pm Post subject: |
|
|
Hi,
SQL Toolkit supports this via a delegate function called SelectionDelegate. To hook this function into Data Compare Engine, you can specify the delegate function as a parameter to the SqlProvider.GetMigrationSQL method, and you can write the logic that decides whether or not an individual update, insert, or delete query will be included in the synchronization script. If your delegate function returns TRUE, the individual record will be included in the script.
| Code: |
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)
{
Reader resultsReader = m_TableDifferences[syncRecordObject.TableName].ResultsStore.GetReader(Row.RowType.All);
if (syncRecordObject.TableName == "[dbo].[Widgets]")
{
Row myRow = resultsReader.GetRow(syncRecordObject.Bookmark);
if ((Int64) myRow.Values[0] > 3)
{
return true;
}
}
return false;
}
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();
}
}
} |
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
|
|
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
