SqlSynchronization Enumeration

jbaggaleyjbaggaley Posts: 38
edited April 24, 2006 6:13AM in SQL Toolkit Previous Versions
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
:-)zz[

Comments

  • 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;
    
    }
    
    :-)zz[
  • 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?
    Richard Mitchell
    Project Manager
    Red Gate Software Ltd
Sign In or Register to comment.