Filtering - the new approach

jbaggaley

What is the new approach to filtering (as shown by the example code for selecting tables which have a company id field of 1) because the object model no longer has a Table class (RedGate.SQLDataCompare.Engine.Table)?

/* Get a list of tables with matching primary keys */ 
obCompareTables = RedGate.SQLDataCompare.Engine.Tables.CreateFromIntersection(dbSourceDatabase.Tables, dbTargetDatabase.Tables); 

/*Loop through all of the tables, and only select  the tables that contain the field 'CompanyID' */ 

foreach (RedGate.SQLDataCompare.Engine.Table Table in obCompareTables) 
{ 
	if (Table.Fields["CompanyID"]!=null) obTableComparisonSettings.Add(obTCS = new SQLDataCompare.Engine.TableComparisonSetting(Table.FullyQualifiedName, Table.Fields, Table.PrimaryKey.Fields)); 
}

Thanks

Jon

jbaggaley

Currently experimenting with the following approach because there does not seem to be any other easy way to remove a table from synchronisation...

foreach (TableMapping mapping in mappings.TableMappings)
{    
if (!(mapping.Obj1.Name!="ATableIWant) )
  mappings.TableMappings.Remove(mapping);
}

However this is not working because objects can't be removed from a collection being iterated.
Please can you point me to a new sample for table filtering.

Also, I noticed that there is a new Where clause which is only documented as existing but nothing about how to use it. Can it be used similar to the following?

mapping.Where.Clause(true)="customerid=1";

if so, is it possible to do this before doing a table comparison so that in the case of our databases where we have multiple million row tables, we can restrict the amount of keys that the API has to look through?

Thanks

Jon

jbaggaley

The following procedure seems to do the job of removing unwanted tables. Not sure I like the idea of having to shove the objects in an arraylist before removing them but all improvement suggestions greatfully received... just got to work out how the Where clause works now (found a UI example on http://blogs.red-gate.com/blogs/james/archive/2006/02/27/Data_Compare_5_Beta.aspx) so it must be possible...

Hope this helps someone.

/// <summary>
/// Synchronise the static data tables by comparing the list of available tables
/// with a control list of static tables found in the function IsStaticTable(). 
/// </summary>
/// <returns>A success indicator</returns>
private bool SyncStaticData()
{
	BlockExecutor rbeBlock=new BlockExecutor();
	ComparisonSession rcsSession = new ComparisonSession();
	ExecutionBlock rxbSyncRemoteStaticDataBlock=new ExecutionBlock() ;
	SchemaMappings rsmMappings = new SchemaMappings();
	SqlProvider rspProvider=new SqlProvider();
	ArrayList arlTablesToRemove = new ArrayList();
	bool blnSuccess=true;

	try
	{
		// Create the mappings between the two databases
		rsmMappings.CreateMappings(_rsdCentralDataDB , _rsdRemoteDataDB );

		// Loop through all of the tables, and only select the tables that match our
		// predefined list of static tables found in the function  IsStaticTable()
		foreach (TableMapping mapping in rsmMappings.TableMappings)
		{
			if (!(IsStaticTable(mapping.Obj1.Name)) )
			{
				arlTablesToRemove.Add(mapping );
			}
		}

		// Remove any tables that we have marked as not required.
		// (has to be done outside the foreach loop or an error occurs)
		foreach (TableMapping rtmMapping in arlTablesToRemove )
		{
			rsmMappings.TableMappings.Remove(rtmMapping);
		}

		// Now we have the final list of tables, perform the data comparison
		rcsSession.CompareDatabases(_rsdCentralDataDB, _rsdRemoteDataDB, rsmMappings);

		// Work out the SQL script
		rxbSyncRemoteStaticDataBlock= rspProvider.GetMigrationSQL(rcsSession, true);

		// Execute the changes
		rbeBlock.ExecuteBlock(rxbSyncRemoteStaticDataBlock, "RemoteServerName" , "RemoteDBName");

	}
	catch (Exception ex)
	{
		// TODO Add some sort of message/raise an error which can be sent back to the user
		// possibly along with logging the failure for support reasons
		Debug.Assert(false, "failed to synchronise static data such as lookup tables");
		blnSuccess=false;
	}
	finally
	{
		// Tidy up
		rcsSession.Dispose ();
	}
	return blnSuccess;

}

Brian Donahue

Hi,

Sorry for the delay. We're all coming to grips with the new object model. If you simply want to exclude a table from a data compare, run through the mappings, and set the 'include' property of each mapping to 'false'.

Hope this helps!

jbaggaley

no worries - don't see how I could have missed it... :shock:
updated version then as follows in case anyone needs it...

Now just need to work out the where clause object..

/// <summary>
/// Synchronise the static data tables by comparing the list of available tables 
/// with a control list of static tables found in the function IsStaticTable(). 
/// </summary> 
/// <returns>A success indicator</returns> 
private bool SyncStaticData() 
{ 
   BlockExecutor rbeBlock=new BlockExecutor(); 
   ComparisonSession rcsSession = new ComparisonSession(); 
   ExecutionBlock rxbSyncRemoteStaticDataBlock=new ExecutionBlock() ; 
   SchemaMappings rsmMappings = new SchemaMappings(); 
   SqlProvider rspProvider=new SqlProvider(); 
   bool blnSuccess=true;

   try 
   { 
      // Create the mappings between the two databases 
      rsmMappings.CreateMappings(_rsdCentralDataDB , _rsdRemoteDataDB ); 

      // Loop through all of the tables, and only select the tables that match our 
      // predefined list of static tables found in the function  IsStaticTable() 
      foreach (TableMapping mapping in rsmMappings.TableMappings) 
      { 
         if (!(IsStaticTable(mapping.Obj1.Name)) ) 
         { 
         mapping.Include=false;
         }
      } 

      // Now we have the final list of tables, perform the data comparison 
      rcsSession.CompareDatabases(_rsdCentralDataDB, _rsdRemoteDataDB, rsmMappings); 

      // Work out the SQL script 
      rxbSyncRemoteStaticDataBlock= rspProvider.GetMigrationSQL(rcsSession, true); 

      // Execute the changes
      rbeBlock.ExecuteBlock(rxbSyncRemoteStaticDataBlock, "RemoteServerName" , "RemoteDBName"); 

   } 
   catch (Exception ex) 
   { 
      // TODO Add some sort of message/raise an error which can be sent back to the user 
      // possibly along with logging the failure for support reasons 
      Debug.Assert(false, "failed to synchronise static data such as lookup tables"); 
      blnSuccess=false; 
   } 
   finally 
   { 
      // Tidy up 
      rcsSession.Dispose (); 
   } 
   return blnSuccess; 

}