Up to date sample of selecting individual records.

jbaggaleyjbaggaley Posts: 38
edited June 20, 2006 12:57PM in SQL Toolkit Previous Versions
Please can you point me in the direction of an up to data row selection sample because ResultsStoreType in the example below seems obsolete as it has been moved theoretically to RedGate.SQLDataCompare.Engine.SynchronizationRecord. However you need to create an instance of that before you can read ResultsStoreType as a property so I am not sure how it should be used.
I need to compare two tables and then based on a list of required records, choose those to be replicated in the same way that tables can be chosen.

http://www.red-gate.com/messageboard/vi ... horizontal

(btw we have now got several licences so thanks for your help whilst I was initially evaluating)
:-)zz[

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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”);
             } 
          }
    
  • 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
  • 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[
  • 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[
Sign In or Register to comment.