Red Gate forums :: View topic - sync and generate scripts for selected rows of a 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

sync and generate scripts for selected rows of a table

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



Joined: 18 Jun 2008
Posts: 2

PostPosted: Wed Jun 18, 2008 6:43 pm    Post subject: sync and generate scripts for selected rows of a table Reply with quote

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
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6649

PostPosted: Mon Jun 23, 2008 6:27 pm    Post subject: Reply with quote

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();
      }
   }
}
Back to top
View user's profile Send private message
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