STK 3 - Implement horizontal filter using SQLDataCompare

Brian Donahue

• Date: 28 JUN 2005
• Versions affected: 3.x

It is possible to filter rows horizontally using SQL Toolkit by implementing a SelectionDelegate and passing it to the GetMigrationSQL method.

This allows you to selectively choose the rows that will be synchronized in code. In the following example, the tables in two databases will be synchronized for all tables that have a column called 'CompanyID', but only if the value of CompanyID is equal to 1.

using System;
using System.IO;
using System.Collections;
using RedGate.SQL.Shared;
using RedGate.SQLDataCompare.Engine;

namespace RedGate.ToolkitChooseRecords
{
	/// <summary>
	/// Summary description for Class1.
	/// </summary>
	/// 
	class ChooseRecords
	{
		/// <summary>
		/// This program will synchronize all tables in a database, but only if they contain a column called CompanyID
		/// and that CompanyID=1.
		/// </summary>
		//This is our global array of SyncInfo objects that tell us if a record is to be synchronized.
		static ArrayList syncInfoArray = new ArrayList();
		[STAThread]
		static int Main(string[] args)
		{
			//Some initial declarations for arguments.
			string sDBSource = "", sDBSourceServer = "", sDBSourceUser = "", sDBSourcePassword = "";
			string sDBTarget = "", sDBTargetServer = "", sDBTargetUser = "", sDBTargetPassword = "";
			bool bSourceIntegrated = true, bTargetIntegrated = true;
			//Tell Data compare to synchronize different, missing, and additional records.
			SessionSettings enDataCompareOptions = SessionSettings.Default;
			int iCount = 0;
			if (args.Length > 0) 
			{
				/* argument parsing routine */
				foreach (string argument in args)
				{
					string arg=argument.ToUpper();
					if (arg.StartsWith("/DB1:")) sDBSource = arg.Substring(5);
					if (arg.StartsWith("/DB2:")) sDBTarget = arg.Substring(5);
					if (arg.StartsWith("/SERVER1:")) sDBSourceServer = arg.Substring(9);
					if (arg.StartsWith("/SERVER2:")) sDBTargetServer = arg.Substring(9);
					if (arg.StartsWith("/USER1:")) sDBSourceUser = arg.Substring(7);
					if (arg.StartsWith("/USER2:")) sDBTargetUser = arg.Substring(7);
					if (arg.StartsWith("/PASSWORD1:")) sDBSourcePassword = arg.Substring(11);
					if (arg.StartsWith("/PASSWORD2:")) sDBTargetPassword = arg.Substring(11);
				}
			}
			else 
			{
				/* if user enters no args, show some help */
				Console.WriteLine("USAGE: FilterDemo /server1:<Source server>\n\t\t/db1:<Source Database>\n\t\t/user1:<Source Database User>\n\t\t/password1:<Source Database User's Password>\n\t\t"
					+"/server2:<Traget server>\n\t\t/db2:<Target Database>\n\t\t/user1:<Target Database User>\n\t\t/password1:<Target Database User's Password>\n" 
					+"If no user or password is specified, Integrated Security will be used!\n");
				return 20;
			}

			/* Check for improper args */
			if (sDBSourceServer.Length == 0) sDBSourceServer = ".";
			if (sDBTargetServer.Length == 0) sDBTargetServer = ".";
			if (sDBSource.Length == 0 || sDBTarget.Length == 0) 
			{
				Console.WriteLine("Must specify a source and a destination database!");
				return 20;}
			if (sDBSourceUser.Length > 0) bSourceIntegrated = false;
			if (sDBTargetUser.Length > 0) bTargetIntegrated = false;

			if (sDBSourceUser.Length > 0 && sDBSourcePassword.Length == 0) 
			{
				Console.WriteLine("You must specify a password for the source database user name!\n");
				return 20;}
			if (sDBTargetUser.Length > 0 && sDBTargetPassword.Length == 0) 
			{
				Console.WriteLine("You must specify a password for the target database user name!\n");
				return 20;}
			
			
			string sDataMigration = GenerateDataSyncScript(sDBSourceServer, sDBSource, bSourceIntegrated, sDBSourceUser, sDBSourcePassword, sDBTargetServer, sDBTarget, bTargetIntegrated, sDBTargetUser, sDBTargetPassword, enDataCompareOptions);
		
			Console.WriteLine(sDataMigration);
			Console.WriteLine("\r\n\t\t\t Press Enter.");
			Console.ReadLine();
			return 0;
		}
		
		/*This method generates the synchronization script.
		It adds any tables that have a columns called CompanyID
		and produces a script only if the row's CompanyID=1 */
		static string GenerateDataSyncScript(string sDBSourceServer, string sDBSourceName, bool bSourceIntegrated, string sDBSourceUser, string sDBSourcePassword, string sDBTargetServer, string sDBTargetName, bool bTargetIntegrated, string sDBTargetUser, string sDBTargetPassword,  SessionSettings enDataCompareOptions) 
		{
			/* Our objects needed to run the comparison */
			SqlProvider obSqlProvider1 = new SqlProvider();
			RedGate.SQLDataCompare.Engine.Database dbSourceDatabase;
			RedGate.SQLDataCompare.Engine.Database dbTargetDatabase;
			RedGate.SQLDataCompare.Engine.Tables obCompareTables;
			RedGate.SQLDataCompare.Engine.TableComparisonSettings obTableComparisonSettings = new RedGate.SQLDataCompare.Engine.TableComparisonSettings();
			RedGate.SQLDataCompare.Engine.ComparisonSession obComparisonSession = new RedGate.SQLDataCompare.Engine.ComparisonSession();
			RedGate.SQLDataCompare.Engine.SqlConnectionProperties obConnectionProperties;
			SQLDataCompare.Engine.TableComparisonSetting obTCS;

			/* Register the database using GetDatabase() */
			try 
			{
				if (bSourceIntegrated) dbSourceDatabase = obSqlProvider1.GetDatabase(obConnectionProperties = new RedGate.SQLDataCompare.Engine.SqlConnectionProperties(sDBSourceServer, sDBSourceName));
				else dbSourceDatabase = obSqlProvider1.GetDatabase(obConnectionProperties = new RedGate.SQLDataCompare.Engine.SqlConnectionProperties(sDBSourceServer, sDBSourceName, sDBSourceUser, sDBSourcePassword));
			}
			catch (System.Exception e) 
			{
				Console.WriteLine("Data Compare GetDatabase() failed on source database: " +e.Message);
				return "";
			}
			
			try 
			{
				if (bTargetIntegrated) dbTargetDatabase = obSqlProvider1.GetDatabase(obConnectionProperties = new RedGate.SQLDataCompare.Engine.SqlConnectionProperties(sDBTargetServer, sDBTargetName));
				else dbTargetDatabase = obSqlProvider1.GetDatabase(obConnectionProperties = new RedGate.SQLDataCompare.Engine.SqlConnectionProperties(sDBTargetServer, sDBTargetName, sDBTargetUser, sDBTargetPassword));
			}
			catch (System.Exception e) 
			{
				Console.WriteLine("Data Compare GetDatabase() failed on target database: " +e.Message);
				return "";
			}
			/* Get a list of tables with matching primary keys */
			obCompareTables = RedGate.SQLDataCompare.Engine.Tables.CreateFromIntersection(dbSourceDatabase.Tables, dbTargetDatabase.Tables);

			 /*Loop through all of the tables, and only select
            the tables that contain the field 'CompanyID' */
			foreach (RedGate.SQLDataCompare.Engine.Table Table in obCompareTables) 
			{
				if (Table.Fields["CompanyID"]!=null) obTableComparisonSettings.Add(obTCS = new SQLDataCompare.Engine.TableComparisonSetting(Table.FullyQualifiedName, Table.Fields, Table.PrimaryKey.Fields));
			}
			
			/* Now we are ready for DataCompare to run the actual comparison. */
			obComparisonSession.CompareDatabases(dbSourceDatabase, dbTargetDatabase, obTableComparisonSettings);

			/* Now we go through all of the result stores for the SyncByCompanyID table */
			ResultsStore rs=null;
			ResultsStoreType storetype = ResultsStoreType.In1;
			foreach (TableComparisonSetting tcs in obTableComparisonSettings) 
			{
				// Loop through each type of result store.
				for (int iStore=0; iStore<3; iStore++) 
				{
				
					switch (iStore) 
					{
						case 0: rs = obComparisonSession.TableDifferences[tcs.TableFullyQualifiedNameIn1].In1Records; 
							storetype=ResultsStoreType.In1;
							break;
						case 1: rs = obComparisonSession.TableDifferences[tcs.TableFullyQualifiedNameIn1].In2Records;
							storetype=ResultsStoreType.In2;
							break;
						case 2: rs = obComparisonSession.TableDifferences[tcs.TableFullyQualifiedNameIn1].DifferentRecords;
							storetype=ResultsStoreType.Different;
							break;
					}
			
					for (int i=0; i<rs.RowCount; i++)
					{
						//Get the row values.
						Row aRow = rs.GetRow(i);
						//Instantiate a new SyncInfo object to store information about this row.
						SyncInfo si = new SyncInfo();				
						si.TableName=tcs.TableFullyQualifiedNameIn1;
                si.Synchronize=false;
						//Choose CompanyID=1 for synchronization
if (storetype==ResultsStoreType.Different) 
						{
							if ((int) aRow.Values[tcs.Fields["CompanyID"].OrdinalInResults1]==1 || (int) aRow.Values[tcs.Fields["CompanyID"].OrdinalInResults2]==1) si.Synchronize=true;
						}
						else 
						{
							if ((int) aRow.Values[tcs.Fields["CompanyID"].OrdinalInResults]==1) si.Synchronize=true;
						}						//Store the bookmark to cross-reference with in the delegate.
						si.Bookmark=aRow.Bookmark;
						si.StoreType=storetype;
						//Add the bookmark to the valueArray Hashtable
						//with a true/false value of whether the datestamp in
						//the source database is newer than in the target
						syncInfoArray.Add(si);
					}//End for each record in this particular store
					//Free up this particular results store
					rs.Close();
				} // End do for each result store
			} //End do for each table
            /*Instantiate a new SelectionDelegate and map the ChooseRow
			method to the object. When IncludeRowDelegatefires, it will
			run ChooseRow*/
			SelectionDelegate IncludeRowDelegate = new SelectionDelegate(ChooseRow);

			/* Now, generate synchronization script, passing IncludeRowDelegate in */
			RedGate.SQL.Shared.ExecutionBlock obExeBlock = obSqlProvider1.GetMigrationSQL(obComparisonSession, IncludeRowDelegate, false);
	
			return obExeBlock.ToString();
		}
		
		/*This is our function that will determine whether or not to include
		a row in the synchronization script.
		If this function returns True, the row will appear in the script.
		If it returns false, the row will be ommitted.
		The function accepts a SynchronizationRecord that is sent by
		the GetMigrationSQL method. */
		static public bool ChooseRow(SynchronizationRecord sRec) 
		{
			foreach (SyncInfo si in syncInfoArray) 
			{
				if (si.Bookmark==sRec.Bookmark && sRec.ResultsStoreType==si.StoreType && !si.Synchronize && si.TableName==sRec.TableName) return false;
			}
			return true;
		}
		
	}

	/* This class holds information about the synchronization */
	public class SyncInfo 
	{
		private long bookmark;
		private bool synchronize;
		private ResultsStoreType storetype;
		private string tablename;

		public long Bookmark 
		{
			get {return bookmark;}
			set {bookmark=value;}
		}
		public bool Synchronize 
		{
			get {return synchronize;}
			set {synchronize=value;}
		}
		public ResultsStoreType StoreType 
		{
			get {return storetype;}
			set {storetype=value;}
		}
		public string TableName 
		{
			get {return tablename;}
			set {tablename=value;}
		}
	
	}
}