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.

Microsoft Technology Rant!

As I try to build more and more complex applications, I am starting to find incompatibilites between various microsoft technologies.

First one being ADO.NET DataServices & Silverlight:

Since I am building a Silverlight application that uses RESTful services to get data, I wanted to make sure the packets that I am sending are small. Comparing JSON and XML packets, JSON packets in general contact less crap, and hence the reason I choose to use JSON string instead of XML. At the sametime I wanted to be flexible, so I designed my webservices to be able to return JSON and XML based on the ACCEPT header (which is exactly how ADO.NET Data Services swtiches between the two formats).

I am now discovering because of a restriction on WebHeaderCollection, I can't switch between the data, and probably will now be forced to use XML!

Another restriction I discovered is that the silverlight plugin is restricted to POST and GET request methods. The decision is for this restriction is because the plugin is designed for the lowerest denominator of where the plugin might be used. I can see how this makes sense, but I want control over this not microsoft.

When I develop software, I know what kind of computers and software I am targetting. I know the limitations, so I want the flexibility. To get around this, I needed to implement POST Tunneling into my webservice so that I can do a normal POST and an extra Header called "X-HTTP-Method-Override" with the real request method I want.

AARGH!!

DTS (Database & Team Foundation Sidekick)

I decided to make this tool to help with the database management in Team Foundation Server. There is a massive gap between TFS and SQL Server Management Studio. SSMS can communicate with TFS but the SSMS environment is so limited.

Current features of this tool:
- Generate Scripts from SQL Server 2000/2005 in SQL 2000 format.
i.e. If exists then Drop then Create.
- Save output to file.
- Save output to TFS
- Find changes from TFS in a defined folder.

Future features:
- Compare 2 different databases and script the changes into TFS
- Compare lookup data of 2 different databases and script the changes.

You can download it from here.

You will need the Visual Studio SDK 2005 installed.