How to Programmatically Bulk Update TFS Query Columns and Sort Order to Match Another Query Using the TFS SDK

2012-01-17

I just created 10 queries with all the same columns and sort order. I then realized I missed a column and wanted to add another sort column to all those queries. I made the changes to one of the other 10 queries and thought there must be a way to do this without having to click a thousand times using the TFS UI.

The TFS SDK lets you update queries, so I just grab the select columns and the sort columns from the source query (the first one I modified) and then replaced those fields the destination queries. It’s pretty simple, but worth a quick post and will hopefully save you thousands of clicks as well.

You probably need to be a project admin for this to work. Ask your TFS admin to give you those perms.

1. Open a new console app.
2. Reference Microsoft.TeamFoundation.Client and Microsoft.TeamFoundation.WorkItemTracking.Client

3. Add the using statements:

using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;

4. Add this code to your main method

var tfs = TfsTeamProjectCollectionFactory.GetTeamProjectCollection(new Uri("[url to your tfs server]"));
var store = tfs.GetService();
var project = store.Projects["[your project name"];
  var sourceGuid = new Guid("14c1bcab-0bcb-40ee-80c1-e23d1dc41a15"); List destinationGuids = new List &#123;
   new Guid("183dba3a-06fd-4b3f-bf60-9dec3fe8cfcd"), new Guid("c0a612b9-9e61-4f78-98ab-07c70702b9d6"), new Guid("3925276a-389c-4052-b1c9-6efedc2ee3a0"), new Guid("599f08f7-a3c0-4b88-a7fe-66f1b9e88e54"), new Guid("80d10f84-b14d-429c-82df-c6c0113fd043"), new Guid("5cc37d79-70c2-426d-bfe4-8544c805ae21"), new Guid("d147d553-0aac-4ebb-8ee7-d281cb5fc7c0"), new Guid("01e8daea-585a-4d3e-af0d-c2c11e742f8c")
  &#125;; // the QueryText is a lot like SQL... SELECT COLUMNS FROM TABLE ORDER BY COLUMNS // we want to get the selected COLUMNS and the ORDER BY columns, then copy those to the destination queries var sourceQuery = project.StoredQueries[sourceGuid];
  // get the columns
  var sourceColumns = sourceQuery.QueryText.Substring(0, sourceQuery.QueryText.IndexOf(" from "));
  // get the order by
  var sourceSort = sourceQuery.QueryText.Substring(sourceQuery.QueryText.IndexOf(" order by ")); foreach(Guid destinationGuid in destinationGuids) &#123; // get the destination query filters, the stuff in between the columns and the order by        StoredQuery destinationQuery = project.StoredQueries[destinationGuid];        int fromIndex = destinationQuery.QueryText.IndexOf(" from ");        int orderByIndex = destinationQuery.QueryText.IndexOf(" order by ");
   string queryFilters = destinationQuery.QueryText.Substring(fromIndex, orderByIndex - fromIndex);
   // combine the source columns and sort with the destination filters
   destinationQuery.QueryText = string.Concat(sourceColumns, queryFilters, sourceSort);
   destinationQuery.Update();
  &#125;

5. Replace [url to your tfs server] with the url to your tfs server, you can get this from your TFS admin.
6. Replace [your project name] with the name of your project.
7. Find the Guid of the source query. You can select the query in Team Explorer and hit F4. Copy the Guid in the Url field to the sourceGuid property in your code.

8. Find the Guid for all the queries you want to change and put those Guids in the destinationGuids collection.

9. Run the app.

HTH,

Jon

TechTutorials