Red Gate forums :: View topic - How to avoid Deletes from Target Database
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

How to avoid Deletes from Target Database

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
danielstony



Joined: 14 Aug 2008
Posts: 4

PostPosted: Mon Sep 08, 2008 4:03 pm    Post subject: How to avoid Deletes from Target Database Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Wed Sep 10, 2008 6:21 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
danielstony



Joined: 14 Aug 2008
Posts: 4

PostPosted: Thu Sep 11, 2008 8:59 am    Post subject: Solution Reply with quote

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]
Back to top
View user's profile Send private message Send e-mail
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Thu Sep 11, 2008 9:26 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
brotherned



Joined: 17 Feb 2009
Posts: 2

PostPosted: Wed Feb 18, 2009 12:28 am    Post subject: Could someone provide an example of how to do this Reply with quote

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,
Back to top
View user's profile Send private message
simon.jackson



Joined: 08 Jan 2009
Posts: 45

PostPosted: Wed Feb 18, 2009 10:58 am    Post subject: Reply with quote

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;
}
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Wed Feb 18, 2009 11:09 am    Post subject: Reply with quote

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:
         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.
Back to top
View user's profile Send private message
brotherned



Joined: 17 Feb 2009
Posts: 2

PostPosted: Wed Feb 18, 2009 3:11 pm    Post subject: Reply with quote

Brian,

This is very helpful. I didn't realize you could use "^" to exclude a value. Thank you very much.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group