sync and generate scripts for selected rows of a table

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

sync and generate scripts for selected rows of a table

Postby jubayer92 » Wed Jun 18, 2008 6:43 pm

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.
jubayer92
 
Posts: 2
Joined: Wed Jun 18, 2008 6:29 pm

Postby Brian Donahue » Mon Jun 23, 2008 6:27 pm

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: Select all
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
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests