How to avoid Deletes from Target Database

Automate and integrate using the SQL comparison API

Moderator: Chris Auckland

How to avoid Deletes from Target Database

Postby danielstony » Mon Sep 08, 2008 4:03 pm

Hi,

I have C# code that successfully does a comparison of two databases. Due to database integrity, I want to prevent the comparison from deleting records from the target database even if they dont exist in the source database. Ideally, I would just want to tell the comparion to perform INSERTS and UPDATES only.

This is a snippet of current code

//get the two databases
Database db1 = new Database();
Database db2 = new Database();
SchemaMappings mappings = new SchemaMappings();

//Should check if this is true
LiveDatabaseSource liveDb = project.DataSource1 as LiveDatabaseSource;
liveDb.ServerName = _server;

if (_username != "")
{
liveDb.UserName = _username;
iveDb.Password = _password;
}
db1.RegisterForDataCompare(liveDb.ToConnectionProperties(), Options.Default);

//Should check if this is true
liveDb = project.DataSource2 as LiveDatabaseSource;
liveDb.ServerName = _server;
if (_username != "")
{
liveDb.UserName = _username;
liveDb.Password = _password;
}

db2.RegisterForDataCompare(liveDb.ToConnectionProperties(), Options.Default);

mappings.Options = project.Options;
mappings.CreateMappings(db1, db2);

//Disable any mappings here that you may want....
ComparisonSession session = new ComparisonSession();
session.Options = project.Options;
session.CompareDatabases(db1, db2, mappings);

SqlProvider provider=new SqlProvider();
provider.Options = session.Options;

ExecutionBlock block = provider.GetMigrationSQL(session, true);



Thanks
Tony
danielstony
 
Posts: 4
Joined: Thu Aug 14, 2008 9:19 am

Postby Chris Auckland » Wed Sep 10, 2008 6:21 pm

Thanks for your post.

We have an open feature request to add an option to exclude certain comparison groups from the sync. Effectively giving the user options to stop things being Added, altered, or dropped from the target schema. The feature has been approved for a future version but an exact release version is yet to be assigned. For your reference the feature tracking number is SC-3478.
Chris
Chris Auckland
 
Posts: 757
Joined: Tue Oct 24, 2006 2:12 pm
Location: Red Gate Software Ltd.

Solution

Postby danielstony » Thu Sep 11, 2008 8:59 am

Hi,

thanks for the response, not very helpful I'm afraid!I have managed to work out the solution which others may find useful.

It is possible to use a delegate function to achieve this. When you call the GetMigrationSQL() function, you need to pass in the name of the delegate function, i.e.

ExecutionBlock block = provider.GetMigrationSQL(session, new SelectionDelegate(SyncRecord), true);

where SyncRecord is the name of the function. The SyncRecord function is as follows

private static bool SyncRecord(SynchronizationRecord syncRecordObject)
{
Reader resultsReader = m_TableDifferences[syncRecordObject.TableName].ResultsStore.GetReader(Row.RowType.All);

// return true if the current record is to be included in the script
Row row = resultsReader.GetRow(syncRecordObject.Bookmark);

// data not in db2 but in db1 (depending on the comparison order, means either delete or insert)
if (row.Type == Row.RowType.In2)
{
return false;
}

return true;
}

This function returns true if you want the changes, otherwise it returns False to ignore it.

There are several options available to Row.RowType, in this case In2 indicates the record exists in the destination database but not in the source (i.e. a delete).
In the scenario above, this ieffectively ignore all difference in the destination database (Updates and Deletes), which is fine for what I need because it should be a push from the source to the destination.

Hope this helps someone.[/b]
danielstony
 
Posts: 4
Joined: Thu Aug 14, 2008 9:19 am

Postby Brian Donahue » Thu Sep 11, 2008 9:26 am

Hi,

That's one solution, but I think it's better to set one or more options in the SessionSettings enumeration as part of your ComparisonOptions.

SessionSettings contain parameters for including only the records in database one or database two. For instance, to stop the synchronization from deleting data in database two, you can "unset" SessionSettings.IncludeRecordsInTwo. I haven't done this in version 7 of the API yet, so I'd say if you want to try this out, give it a run on a test database and see if it works for you.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Could someone provide an example of how to do this

Postby brotherned » Wed Feb 18, 2009 12:28 am

Can some elaborate on this further? I've downloaded the code above, but I'm not sure what m_TableDifferences references.

Could someone from RedGate flush out the theory by Brian Donahue and possibly provide an example?

Thank you,
brotherned
 
Posts: 2
Joined: Tue Feb 17, 2009 7:31 pm

Postby simon.jackson » Wed Feb 18, 2009 10:58 am

danielstony was working in the context of the examples. So m_TableDifferences is ComparisonSession.TableDifferences. But you can just get the RowType directly:

protected bool SyncRecord(SynchronizationRecord syncRecordObject)
{
return syncRecordObject.ResultsStoreType != Row.RowType.In2;
}
simon.jackson
 
Posts: 45
Joined: Thu Jan 08, 2009 10:42 am

Postby Brian Donahue » Wed Feb 18, 2009 11:09 am

The option to exclude all records that only exist in the second database (and therefore would cause a DELETE query to be scripted for that record) is specified in the CompareDatabases method. By unsetting IncludeRecordsInTwo, you should get a script to run on the second database that only includes UPDATE and INSERT queries.
Code: Select all
         using (ComparisonSession session=new ComparisonSession())
         {
            session.Options = mappings.Options;
            session.CompareDatabases(db1, db2, mappings,SessionSettings.Default^SessionSettings.IncludeRecordsInTwo);
...

This is a lot less work that implementing a SelectionDelegate and probably performs better as well.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby brotherned » Wed Feb 18, 2009 3:11 pm

Brian,

This is very helpful. I didn't realize you could use "^" to exclude a value. Thank you very much.
brotherned
 
Posts: 2
Joined: Tue Feb 17, 2009 7:31 pm


Return to SQL Comparison SDK Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests