sync and generate scripts for selected rows of a table

jubayer92

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.

Brian Donahue

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.

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();
		}
	}
}