Lou Franco's Software Business Blog

Adam, our QA Engineer, and I decided yesterday that we would get TFS integration going with FogBugz. Unfortunately, for us, this page of FogBugz source control integration instuctions does not include TFS. There is this generic page of source control integration instructions to work from, though.

The basic idea is that we either had to get TFS to ping the Fogbugz webserver on each checkin or somehow fill up the CVS table in FogBugz ourselves. One benefit of the latter (aside from having no idea how to get TFS to do things on checkin) is that we could sync the past. That's actually nice, because as Adam and I were looking at our logs, we could see a lot of checkin comments that looked like:

    FB4567: Fixed a bug ...
    Fixed the blah blah (fb5678)
    Case: 6475 Added a way to ...

(of course, the comments are better than that, though) 

And a few other formats.  A simple regex and we could pick up all of the info in these past comments -- then we just needed to run the tool in a Windows Task Scheduler task to keep everything in synch.

At this point, we hadn't looked at the TFS .NET object model, and just looked at the SQL Server data (easy to do in Visual Studio).  We poked around the tables and could easily see how it related to what we could see in TFS history lists.

This SQL gets you all of the changesets and their comments

    select changesetid, comment from tbl_changeset

And this will get you the files associated with a changeset

    select versionfrom, fullpath from tbl_version where versionfrom=@cs 

For FogBugz, this SQL will insert a record to associate a FB case with a file version:

    insert into cvs (ixbug, sfile, sprev, snew) values (@bugid, @file, @sprev, @snew)

For our purposes, just setting sPrev to sNew-1 is ok (later, TFS will know how to diff based on that)

My full code is attached -- you need to make a config file with your connection strings in it:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="TfsFogbugzSync.Properties.Settings.fogbugzConnectionString"
            connectionString="Data Source=FB_SERVERNAME;Initial Catalog=fogbugz;Integrated Security=True"
            providerName="System.Data.SqlClient" />
        <add name="TfsFogbugzSync.Properties.Settings.TfsVersionControlConnectionString"
            connectionString="Data Source=TFS_SERVERNAME;Initial Catalog=TfsVersionControl;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

You need to change FB_SERVERNAME and TFS_SERVERNAME to the real server names.