Programatically find TFS changes since last good build

Question

I have several branches in TFS (dev, test, stage) and when I merge changes into the test branch I want the automated build and deploy script to find all the updated SQL files and deploy them to the test database.

I thought I could do this by finding all the changesets associated with the build since the last good build, finding all the sql files in the changesets and deploying them. However I don't seem to be having the changeset associated with the build for some reason so my question is twofold:

1) How do I ensure that a changeset is associated with a particular build?

2) How can I get a list of files that have changed in the branch since the last good build? I have the last successfully built build but I'm unsure how to get the files without checking the changesets (which as mentioned above are not associated with the build!)

Answer 1

Thanks Scott,

After a while I found a nice way to manage this.

Basically I created a task which gets the current changesets associated with the build (point 1 of my question is not an issue) and then loop through them looking for .sql files. Once I have a list of those I can create a change script or execute them against the target database.

The code looks something like this:

TeamFoundationServer tfs = new TeamFoundationServer(TfsServerUrl);
VersionControlServer vcs = (VersionControlServer)tfs.GetService(typeof(VersionControlServer));

var buildServer = (IBuildServer)tfs.GetService(typeof(IBuildServer));


IBuildDetail build = buildServer.GetBuild(
    new Uri(BuildUri)
    , null
    , QueryOptions.All
);

build.RefreshAllDetails();

var changesets = InformationNodeConverters.GetAssociatedChangesets(build);

foreach (var changesetSummary in changesets)
{
    Changeset changeSet = vcs.GetChangeset(changesetSummary.ChangesetId);

    sqlFilePaths.AddRange(
        ProcessChangeSet(changeSet)
    );

}

and the code inside ProcessChangeSet looks like

List<string> sqlFilePaths = new List<string>();
foreach (Change change in changeSet.Changes)
{

    if ((change.Item.ItemType == ItemType.File)
        && (change.Item.ServerItem.EndsWith(".sql", StringComparison.OrdinalIgnoreCase))
        )
    {
        sqlFilePaths.Add(
            sqlPath
        );

    }
}
return sqlFilePathes;

But if anyone wants I'm happy to give them the complete code. Makes making sure stored procedures are in sync across the system. This only leaves schema changes to manually manage within my database which I'm happy to do.