Sync data using project file setting

kkchan

Hi,
I am using Data Compare to configure tables mappings, table to be sync, options, PK and etc. All of these settings are saved in Data compare project file.

How could I use SQL toolkit to sync database which will follow my settings saved in project file?

Thank you

richardjm

You can use the new ReplayUserActions(ref SchemaMappings) call on the Project object in SQL Toolkit 6.0 after you've loaded the project and created the default schema mappings to use all the settings as if you were using the UI.

kkchan

Hi,
What do you meant by default schema? How to create it?
Since I already have set what tables to be sync, can I just use those tables? How to do this?

Thank you

richardjm

Just using Project.LoadFromDisk and then SchemaMappings CreateMappings after registering the databases. The SchemaMappings after that can be passed into the ReplayUserActions function and then Robert's your fathers brother.

kkchan

Hi,
What does ReplayUserActions method do?
After call ReplayUserActions(), do I need to add code to create comparisonsession, comparedatabase, execute block and etc?

Thank you

richardjm

Yes, you need all the boilerplate comparison code after the call. What it does is apply the settings from the project file to the schemamappings. e.g. Exclude Table, Set Custom Comparison Key etc. All these are stored as a 'script' in the project file to be run after the initial schemamappings have been created.

Make a few changes in the UI save the project and open it in an XML editor and you'll see what I mean.

kkchan

Hi,
I have code as below. Is it correct?

Database dbSource = new Database();
Database dbDest = new Database();

//ConnectionProperties loConnPropSource = new ConnectionProperties(toConnInfoSource.ServerName,
// toConnInfoSource.DatabaseName, toConnInfoSource.UserName, toConnInfoSource.Password);

//ConnectionProperties loConnPropDest = new ConnectionProperties(toConnInfoDest.ServerName,
// toConnInfoDest.DatabaseName, toConnInfoDest.UserName, toConnInfoDest.Password);

//dbSource.RegisterForDataCompare(loConnPropSource, Options.Default);
//dbDest.RegisterForDataCompare(loConnPropDest, Options.Default);

Project loProject = Project.LoadFromDisk(@C:\Documents and Settings\chankk\Local Settings\Application Data\Red Gate\SQL Data Compare 6\Branch2HQ_Master.sdc);

LiveDatabaseSource loDSSource = loProject.DataSource1 as LiveDatabaseSource;
dbSource.RegisterForDataCompare(loDSSource.ToConnectionProperties());

LiveDatabaseSource loDSDest = loProject.DataSource2 as LiveDatabaseSource;
ConnectionProperties loConnPropDest = loDSDest.ToConnectionProperties();
dbDest.RegisterForDataCompare(loConnPropDest);

SchemaMappings loSchemaMappings = new SchemaMappings();
loSchemaMappings.CreateMappings(dbSource, dbDest);

List<UserActionWarning> loUserActionWarnings = loProject.ReplayUserActions(ref loSchemaMappings);

TableMappings loMappings = new TableMappings();
loMappings.CreateMappings(dbSource.Tables, dbDest.Tables);

using (ComparisonSession loSession = new ComparisonSession())
{
try
{
SessionSettings loSessionSettings = SessionSettings.IncludeDifferentRecords |
SessionSettings.IncludeRecordsInOne;
loSession.CompareDatabases(dbSource, dbDest, loMappings, loSessionSettings);
}
catch (OperationCancelledException ex)
{

}
catch (OperationAbortedException ex)
{

}

SqlProvider loSQLProvider = new SqlProvider();
ExecutionBlock loBlock = loSQLProvider.GetMigrationSQL(loSession, false);
BlockSaver loBlockSaver = new BlockSaver(@c:\abc.sql, EncodingType.Unicode, loBlock);
loBlockSaver.SaveToFile();


BlockExecutor loExecutor = new BlockExecutor();
loExecutor.ExecuteBlock(loBlock, loConnPropDest.ServerName, loConnPropDest.DatabaseName,
loConnPropDest.IntegratedSecurity, loConnPropDest.UserName, loConnPropDest.Password);

loBlock.Dispose();
}

dbSource.Dispose();
dbDest.Dispose();

richardjm

Very close. You've got the superflououous (never got stop spelling that word) lines...

TableMappings loMappings = new TableMappings();
loMappings.CreateMappings(dbSource.Tables, dbDest.Tables);

After you created your SchemaMappings and ran ReplayUserActions you were done. CompareDatabases has multiple overrides and will take the SchemaMappings directly. What you've done is effectively created a replacement table mappings without all the project settings applied and used that instead.

HTH

kkchan

Hi,
Thank you for reply.
What about sync, comparison and mapping behavior? Anyway to "load" from project file?

I have a common method to do sync. I only pass project name to it and it would load project and do the rest. That why I don't have to have seperate code to manage it since already defined inside project file

Please advice.

Thank you

richardjm

I think you're fine what you want to do is.

Pass routine Project File...

Load Project File
Get Databases as described in project file
Set options as in project file
Create SchemaMappings
ApplyUserActions to SchemaMappings from project file
Perform comparison
Perform synchronisation
Job Done

kkchan

Hi

Set options as in project file

How could I know what options set in project file?[/quote]

richardjm

Project.Options perhaps?