SqlSynchronization Enumeration

jbaggaley

I know I am going blind but I cannot see which object this fits into so I can set it to not delete records that don't appear on one of my databases...

the Namespace is according to the manual:- RedGate.SQLDataCompare.Engine.

I have the following code:

public string SyncVariableData()
{
	ComparisonSession rcsRemoteToCentralSession = new ComparisonSession();
	SchemaMappings rsmMappings = new SchemaMappings();
	SqlProvider rspProvider=new SqlProvider();
	ExecutionBlock rxbSyncRemoteDynamicDataBlock=new ExecutionBlock() ;
	ExecutionBlock rxbSyncCentralDynamicBlock=new ExecutionBlock() ;
	BlockExecutor rbxBlock=new BlockExecutor();
	string strReturnItem=string.Empty ;

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

		// Loop through all of the tables, and only select the tables that match our
		// predefined list of dynamic tables
		foreach (TableMapping mapping in rsmMappings.TableMappings)
		{
			if (!(IsValidTableToUpdate(mapping.Obj1.Name,eDataUpdateType.eDU_DynamicData )) )
			{
				mapping.Include =false;						
			}
			else
				// if the table is in our include list then sort out any where clauses
				// which will speed up synchronisation
				mapping.Where =new WhereClause( GetTableWhereFilter( mapping.Obj1.Name));
		}

		_rsdCentralDB. 
		// Now we have the final list of tables, perform the data comparison
		rcsRemoteToCentralSession.CompareDatabases(_rsdCentralDB, _rsdRemoteDB, rsmMappings,SessionSettings.IncludeDifferentRecords | SessionSettings.IncludeRecordsInOne | SessionSettings.IncludeRecordsInTwo );

		// Work out the SQL script for central to remote synchronisation
		rxbSyncRemoteDynamicDataBlock= rspProvider.GetMigrationSQL(rcsRemoteToCentralSession, true);


		// Run the script for the central server 				Trace.WriteLine (rxbSyncRemoteDynamicDataBlock.GetString());
		

		rbxBlock.ExecuteBlock(rxbSyncCentralDynamicBlock, _ssCentralServer.ServerName , _ssCentralServer.DBName);					
		
	}
	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");
		strReturnItem=false.ToString() ;
	}
	finally
	{
		// Tidy up
		//rcsCentralToRemoteSession.Dispose ();
		rcsRemoteToCentralSession.Dispose ();

		_dtsTableList=null;

	}
	return strReturnItem;

}

Which of my objects should call the settings and if I am missing an object, where does it go?
I only found SessionSettings by accident - turned out to be a parameter of CompareDatabases.
For the help file updates, would be really great if there was a one or two line sample of how every object is used when an object is called as a parameter of another object. Not a full sample but just enough to see it in context...

Thanks

Jon

jbaggaley

rewritten to only make the changes work with update and insert only... All code/object usage improvement comments gratefully received!

public bool SyncVariableData()
{
	ComparisonSession rcsRemoteToCentralSession = new ComparisonSession();
	SchemaMappings rsmMappings = new SchemaMappings();
	SqlProvider rspProvider=new SqlProvider();
	ExecutionBlock rxbSyncRemoteDynamicDataBlock=new ExecutionBlock() ;
	ExecutionBlock rxbSyncCentralDynamicBlock=new ExecutionBlock() ;
	BlockExecutor rbxBlock=new BlockExecutor();
	bool blnSuccess=false;
	SqlSynchronization  rsqSynchronizationOptions=new SqlSynchronization();			

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

		// Set the synchronisation options
		rsqSynchronizationOptions=SqlSynchronization.AddSql | SqlSynchronization.UpdateSql  ; 

		// Loop through all of the tables, and only select the tables that match our
		// predefined list of dynamic tables
		foreach (TableMapping mapping in rsmMappings.TableMappings)
		{
			if (!(IsValidTableToUpdate(mapping.Obj1.Name,eDataUpdateType.eDU_DynamicData )) )
			{
				mapping.Include =false;						
			}
			else
				// if the table is in our include list then sort out any where clauses
				// which will speed up synchronisation
				mapping.Where =new WhereClause( GetTableWhereFilter( mapping.Obj1.Name));
		}

		// Now we have the final list of tables, perform the data comparison
		rcsRemoteToCentralSession.CompareDatabases(_rsdCentralDB, _rsdRemoteDB, rsmMappings,SessionSettings.IncludeDifferentRecords | SessionSettings.IncludeRecordsInOne | SessionSettings.IncludeRecordsInTwo );

		// Now we have the data comparison done, set the tables to only add and update
		foreach (TableMapping mapping in rsmMappings.TableMappings)              
		{           
			if (mapping.Include)
			{
				TableDifference difference=rcsRemoteToCentralSession.TableDifferences[mapping.Obj1.FullyQualifiedName];
				//display the different records                     
				difference.SqlSynchronization =rsqSynchronizationOptions;
			}			
		}

		// Work out the SQL script for central to remote synchronisation
		rxbSyncRemoteDynamicDataBlock= rspProvider.GetMigrationSQL(rcsRemoteToCentralSession, true);
		rxbSyncCentralDynamicBlock= rspProvider.GetMigrationSQL(rcsRemoteToCentralSession, false);

		// Run the script 
		rbxBlock.ExecuteBlock(rxbSyncCentralDynamicBlock, _ssCentralServer.ServerName , _ssCentralServer.DBName);		rbxBlock.ExecuteBlock(rxbSyncRemoteDynamicDataBlock, _ssRemoteServer.ServerName , _ssRemoteServer.DBName);
				
			// managed to execute the update
				strReturnItem=true;
		}

	}
	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");
		strReturnItem=false;
	}
	finally
	{
		// Tidy up
		rcsRemoteToCentralSession.Dispose ();
	}
	return strReturnItem;

}

richardjm

Seems OK after having a quick read through. Not sure that you're disposing of all of the IDisposable objects there though - we make extensive use of temporary files so you really need to be careful to do that.

Nice to see somebody using the new API - how are you finding it?