SubSet Sync with Full Server

eyekron

Hey all,

I'm evaluating the sql toolkit 5 api with C#, and I've got a subset of data on my local machine in a SQLEXPRESS database. The server has a full set of data and tables that are not in my local database.

I need to sync the structure and data of my local database to the server but only worry about sync of the structure and data that is found in my local database. Ignoring all the additional tables, keys, stored procs, views, etc on the server. If it doesn't live in my local database I don't want to edit it on the server.

How do I go about doing this?

Brian Donahue

Hello,

Yes -- you can ignore all of the objects that don't exist in your reference database.

//Set up a progress bar
						
			Database db1=new Database();
			Database db2=new Database();
			
			db1.LoadFromDisk(DB1Snapshot);
						db2.Register(new ConnectionProperties(DBServer, DBName), Options.Default | Options.IgnorePermissions | Options.IgnoreUsers | Options.IgnoreUserProperties);

			Differences differences=db1.CompareWith(db2, Options.Default | Options.IgnorePermissions | Options.IgnoreUsers | Options.IgnoreUserProperties);

			foreach (Difference difference in differences)
			{
				//Do not select users and roles.
				if (difference.DatabaseObjectType!=ObjectType.User && difference.DatabaseObjectType!=ObjectType.Role) difference.Selected=true;
				else difference.Selected=false;
				//Do not drop things from the target database
				if (difference.ObjectIn2==null) difference.Selected=false;
			}

			Work work=new Work();

			//calculate the work to do using sensible default options
			//the script is to be run on WidgetProduction so the runOnTwo parameter is true
			work.BuildFromDifferences(differences, Options.Default | Options.IgnorePermissions | Options.IgnoreUsers | Options.IgnoreUserProperties, true);

			ExecutionBlock block=work.ExecutionBlock;
			//and run the SQL

			RedGate.SQL.Shared.BlockExecutor exe=new RedGate.SQL.Shared.BlockExecutor();
			
			exe.ExecuteBlock(block, DBServer, DBName);
			
			//dispose of the objects
			block.Dispose();
			db1.Dispose();
			db2.Dispose();

eyekron

My function fails with a NullReferenceException not being handled on line:

rgSQLdce.TableDifference difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];

in the mapping.Obj1. This only occurs when it encounters a table that is not in the local database. How do I correct this?


private void syncTableValuesToServer()
{
//TODO:sync table values from local tables to the remote server for only the tables in the local tables

ssm("[BEGIN] Sync Local Data Values to Server-");

rgSQLce.Database db1 = new rgSQLce.Database();
db1.Status += new StatusEventHandler(StatusCallback);

rgSQLce.Database db2 = new rgSQLce.Database();
db2.Status += new StatusEventHandler(StatusCallback);

rgSQLdce.ComparisonSession session = new rgSQLdce.ComparisonSession();
session.Status += new StatusEventHandler(StatusCallback);

rgSQLdce.SchemaMappings mappings = new rgSQLdce.SchemaMappings();
mappings.Status += new StatusEventHandler(StatusCallback);

try
{
ssm("[*] Reading Local DB Data Details...");
db1.RegisterForDataCompare(new rgSQLce.ConnectionProperties(LocalServer, LocalDatabase), rgSQLce.Options.Default);
ssm("[*] Reading Server DB Data Details...");
db2.RegisterForDataCompare(new rgSQLce.ConnectionProperties(SeekServer, SeekDatabase, SeekUser, SeekPassword), rgSQLce.Options.Default);

// Create the mappings between the two databases
ssm("[*] Creating Table Mappings...");
mappings.CreateMappings(db1, db2);

ssm("[*] Comparing Table Data Differences...");
session.CompareDatabases(db1, db2, mappings);

foreach (rgSQLdce.TableMapping mapping in mappings.TableMappings)
{

rgSQLdce.TableDifference difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];
//loop through all the rows

foreach (rgSQLdce.ResultsStore.Row row in difference.ResultsStore)
{
//go through the non same records
if (row.Type != rgSQLdce.ResultsStore.Row.RowType.Same)
{
ssm(String.Format("{0} Row {1} type {2}", mapping.Obj1.FullyQualifiedName, row.Index, row.Type.ToString()));
int i = 0;
foreach (rgSQLdce.FieldPair field in difference.ResultsStore.Fields)
{
int field1 = field.OrdinalInResults1;
int field2 = field.OrdinalInResults2;

if (field1 != field2)
{
// Get the values
object value1 = row.Values[field1];
object value2 = row.Values[field2];
if (value1 == null)
value1 = "NULL";
if (value2 == null)
value2 = "NULL";
ssm(String.Format("{0}\t{1}\t{2}\t{3}", field.Field(false).Name, value1.ToString(), row.FieldDifferent(i) ? "<>" : "==", value2.ToString()));
}
else
{
// This is part of the unique index we are comparing on
object value = row.Values[field1];
ssm(String.Format("*{0}\t{1}", field.Field(false).Name, value.ToString()));
}
i++;
}// End of foreach field pair
}
}// End of foreach row
}// End of foreach mappings
}
finally
{
session.Dispose();
db1.Dispose();
db2.Dispose();
}

ssm("[END] Sync Local Data Values to Server-");
}

Brian Donahue

Hi,

I've obviously not thought this all the way through. You'll want to add a condition to check if mapping.Obj1==null. For missing tables, there is a handy option in the Data Compare Engine that will script all of the data for a table that is missing in the second database:

mappings.Options.MappingOptions=MappingOptions.Default | MappingOptions.MissingFrom2AsInclude; 
            mappings.CreateMappings(db1, db2);

I think in your case, you'd need to rearrange the database object registrations so that db2 is the one with the missing table.

eyekron

Ok, I've added:


ssm("[*] Creating Table Mappings...");
mappings.Options.MappingOptions = rgSQLdce.MappingOptions.Default | rgSQLdce.MappingOptions.MissingFrom2AsInclude;
mappings.CreateMappings(db1, db2);

ssm("[*] Comparing Table Data Differences...");
session.CompareDatabases(db1, db2, mappings);

foreach (rgSQLdce.TableMapping mapping in mappings.TableMappings)
{

if (mapping.Obj1 != null)
{

rgSQLdce.TableDifference difference = session.TableDifferences[mapping.Obj1.FullyQualifiedName];
//loop through all the rows

if (difference != null)
{



To the function above and the function now lists the differences, however, it doesn't commit the changes to the server what's missing?

Brian Donahue

Hi,

I think that all you need to do is get the SQL and run it on the server like so:

SqlProvider provider=new SqlProvider();
provider.Options = session.Options;
ExecutionBlock block= provider.GetMigrationSQL(session, true);
//You need a reference to RedGate.SQL.Shared to get BlockExecutor
BlockExecutor executor = new BlockExecutor();  
executor.ExecuteBlock(block, db2.ConnectionProperties.ServerName, db2.ConnectionProperties.DatabaseName);