SQL Compare SDK, with 2 Script Folders

Recently I ran into a problem where I had 2 independant script folders and needed every object in script folder 1, but only needed a selection of objects from script folder 2.

I thought that I can get to the selection information in the current SQL Compare SDK, because that information is definitely stored in the .SCP files, but it isn't. You can read more here.

Anyway, I needed this to work for the our build task, so I decided to use what I have from the current SQL Compare SDK.

The first step is to store which tables are going to be included for the synchronization from the second script folder. Our solution already has a xml file inside a database script folder which has destination database information. I extended this file to also include Source information:

<config>
 <ScriptSource>
  <Source Type="tfs" TFSServer="TFSServer" Path="$/DatabaseScriptFolder">
   <Include>dbo.Table</Include>
  </Source>
 </ScriptSource>
 <DestinationDatabase>
  <ServerName>.</ServerName>
  <DatabaseName>Database</DatabaseName>
 </DestinationDatabase>
</config>

At the Source tag, you can specify where to get the database script folder. In my case, it is getting the latest from TFS and then using the local path. Within the Source tag, there are Include tags. This is where I specify which additional objects I want to synchronize to the database.

I am also assuming where you have the config file, it is the master script folder, so script folder 1 is where this config is stored.

In C# code, I loop through all the Source tags found within ScriptSource in the xml.

Once the file is read and processed, eventually you will end up at the place where you compare the two databases.

foreach (ScriptSource scriptSource in ScriptSources) {

   using (Database script = new Database())
using (Database database = new Database()) {
  //register script
  //register database

  Differences differences = script.CompareWith(database, Options.Default);

  foreach (Difference difference in differences) {
      if (scriptSource.IsLocal) {
          difference.Selected = true;
          foreach (ScriptSource scriptSourceCheck in ScriptSources) {
              if (scriptSourceCheck.IncludedObjects.Count > 0) {
                  if (difference.ObjectIn2 != null) {
                      if (scriptSourceCheck.IncludedObjects.Contains(string.Format("{0}.{1}", difference.ObjectIn2.Owner, difference.ObjectIn2.Name))) {
                          difference.Selected = false;
                      }
                  }
              }
          }
      } else {
          difference.Selected = false;
          if (difference.ObjectIn1 != null) {
              if (scriptSource.IncludedObjects.Contains(string.Format("{0}.{1}", difference.ObjectIn1.Owner, difference.ObjectIn1.Name))) {
                  difference.Selected = true;
              }
          }
      }
  }
}
}

This bit of code loops through all the ScriptSource and does a sync on the database. I also made sure if the database already has the object from script folder 2 and there were no differences, it would not get deleted. Also if it was only a difference it would only be altered.

This does what I had hoped it to do, and now my database sync library is in use in a Visual Studio Addin and a TFS Build Task.