How to avoid Deletes from Target Database

danielstony

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

Chris Auckland

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.

danielstony

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]

Brian Donahue

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.

brotherned

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,

simon.jackson

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;
}

Brian Donahue

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.

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.

brotherned

Brian,

This is very helpful. I didn't realize you could use "^" to exclude a value. Thank you very much.