Up to date sample of selecting individual records.

Comments

• Brian Donahue Posts: 6,590 Bronze 1

  June 20, 2006 12:44PM edited June 22, 2006 4:27AM

  Hello,
  
  The new version of the Data Compare library has some new features that may make the old selectiondelegate code obsolete. What you can do in the API of version 5 is use a where clause on all of your tables:

  rsmMappings.CreateMappings(_rsdCentralDataDB , _rsdRemoteDataDB ); 
  
        foreach (TableMapping mapping in rsmMappings.TableMappings) 
        { 
           if (mapping.Obj1.Name==”TableULike”) 
           { 
           mapping.Include=true;
  
  mapping.Where = new WhereClause(“ID<100”);
           } 
        }
  

  0 · Share on Twitter
• david connell Redgate › Posts: 172 Silver 1

  June 20, 2006 12:46PM

  Hi there,
  here is a modification of one of the code snippits.

  	public class SqlProviderExample
  	{
  		TableDifferences m_TableDifferences;
  
  	protected bool SyncRecord(SynchronizationRecord syncRecordObject)
  		{
  			Reader resultsReader = m_TableDifferences[syncRecordObject.TableName].ResultsStore.GetReader(Row.RowType.All);
  
  			Row myRow = resultsReader.GetRow(syncRecordObject.Bookmark);
  
  			if (syncRecordObject.TableName == "[dbo].[Widgets]")
  			{				
  				if ((Int64) myRow.Values[0] > 3)
  				{
  					return true;
  				}
  			}
  			//Console.WriteLine("Skipping Table {0} - {1}", syncRecordObject.TableName, myRow.Values[0].ToString());			
  			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);
      
  			using (ComparisonSession session=new ComparisonSession())
  			{
  				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();            
  				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());
      
  					// we can access the SQL in a memory efficient manner by accessing the underlying stream
  					// FileStream stream=block.GetFileStream();
      
  					// run the SQL ( commented out by default )
  					// BlockExecutor executor = new BlockExecutor();
  					// executor.ExecuteBlock(block, ".", "WidgetLive");
  				}
  				finally
  				{
  					block = provider.Block;
  					if (block != null)
  					{
  						block.Dispose();    // dispose of the objects to delete temporary files
  					}
  				}
  			}
  			db1.Dispose();
  			db2.Dispose();
  		}
  	}
  

  
  Hope that helps
  David

  0 · Share on Twitter
• jbaggaley Posts: 38

  June 20, 2006 12:49PM

  Thanks for replying. I was intending to filter in two stages, a big broadbrush filter to give me a smaller subset of records and then create a decision about individual records based on their content which would be too large for a where statement and would take ages just looping through the entire set of changes.
  
  I also need to query based on multiple column primary key fields and would love to know of any examples you have for doing that in v5 too.
  
  
  Thanks
  
  Jon

  :-)zz[

  0 · Share on Twitter
• jbaggaley Posts: 38

  June 20, 2006 12:57PM

  That was quick.. Looks just what I need! I needed to add the following to make it compile though...
  
  using RedGate.SQLDataCompare.Engine.ResultsStore;
  
  
  thanks
  
  Jon

  :-)zz[

  0 · Share on Twitter