Sync data using project file setting

Forum for users of SQL Toolkit 3,4,and 5

Sync data using project file setting

Postby kkchan » Sat Jan 05, 2008 2:36 pm

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
kkchan
 
Posts: 23
Joined: Sat Dec 01, 2007 10:50 am

Postby richardjm » Mon Jan 07, 2008 10:57 am

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.
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby kkchan » Mon Jan 07, 2008 4:19 pm

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
kkchan
 
Posts: 23
Joined: Sat Dec 01, 2007 10:50 am

Postby richardjm » Mon Jan 07, 2008 4:37 pm

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.
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby kkchan » Tue Jan 08, 2008 4:06 pm

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
kkchan
 
Posts: 23
Joined: Sat Dec 01, 2007 10:50 am

Postby richardjm » Tue Jan 08, 2008 4:10 pm

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.
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby kkchan » Thu Jan 10, 2008 5:06 am

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();
kkchan
 
Posts: 23
Joined: Sat Dec 01, 2007 10:50 am

Postby richardjm » Thu Jan 10, 2008 9:27 am

Very close. You've got the superflououous (never got stop spelling that word) lines...
Code: Select all
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
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby kkchan » Sat Jan 12, 2008 4:16 am

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
kkchan
 
Posts: 23
Joined: Sat Dec 01, 2007 10:50 am

Postby richardjm » Mon Jan 14, 2008 9:16 am

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
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd

Postby kkchan » Mon Jan 14, 2008 3:44 pm

Hi

Set options as in project file


How could I know what options set in project file?[/quote]
kkchan
 
Posts: 23
Joined: Sat Dec 01, 2007 10:50 am

Postby richardjm » Mon Jan 14, 2008 4:20 pm

Project.Options perhaps? :)
Richard Mitchell
Project Manager
Red Gate Software Ltd
richardjm
 
Posts: 395
Joined: Fri May 20, 2005 9:44 am
Location: Red Gate Software Ltd


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests