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

So I can understand the intuitive appeal of this approach, but I don't think it's the right way to go.

For one thing it's going to be difficult. But the second problem is that TFS doesn't have a good way to record deployment data.

For the first question, I'm not sure what that means. For the second question you can use the build labels and tf history today list of the changed files.

As an alternative, you could reconsider how you want to manage SQL changes. I use a low-tech method of keeping the current pending changes in one directory, and then after deploying moving the files to a different directory. This method can be enhanced by keeping a deployment history table in the database. You may also want to look into vsts DB addition, the current CTP has a lot of new features around managing database changes. I also hear that Red Gate has nice database management tools as well.

Answer 2

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.

Answer 3

Hey, that's great abigblackman, I can really use that. Would you happen to have a VS2005 version of it?

Answer 4

This is great! Would you mind sending me the complete code, abigblackman?

ty.

Answer 5

Can you please send the complete code? Thanks in advance.

Answer 6

hi abigblackman, can i have the complete code too. thanks very much.

Answer 7

Hey abigblackman,

I would like to see the complete code to the auto push of TFS changes.

Dennis