Click here to Skip to main content
14,975,931 members
Articles / DevOps
Posted 14 Apr 2016

Tagged as


2 bookmarked

Find Changeset details for all work items in a TFS Query

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
14 Apr 2016CPOL2 min read
Generate work items report from TFS including their associated changeset details


This is a console program to generate an excel report for showing the changeset details along with each workitem listed by a TFS Query.


I have come across a requirement to generate a report which needs to identify that a bug is fixed in which branch say development branch or release branch. Just by creating a TFS query, I don't get the details of each changeset and details of source control items which are associated with respective changeset.

So, I decided to create a program to generate excel report by using Microsoft.TeamFoundation libraries.

Using the code

Create a console application using Visual studio and add follwing references from Extensions tab in Reference Manager dialog

So in program.cs file following namespaces should be used.

using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.Framework.Client;
using Microsoft.TeamFoundation.Framework.Common;
using Microsoft.TeamFoundation.VersionControl.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;

Add few keys in app.config file which tells about the TFS server, Query Name and branches we are looking for:

<?xml version="1.0" encoding="utf-8" ?>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    <add key="TfsServer" value=""/>
    <add key="TfsProjectName" value="YourTFSProject"/>
    <add key="TfsQueryGroup" value="My Queries"/>
    <add key="TfsQueryName" value="YourQuery"/>
    <add key="ExcelFile" value="C:\TfsReport.xls"/>
    <add key="DevBranchName" value="YourDevelopmentBranch"/>
    <add key="ReleaseBranchName" value="YourReleaseBranch"/>

So initial requirements are done, lets start code by connecting to TFS server and get the TFS project of our concern using below code:

//Initialize TFS Server object
TfsConfigurationServer configurationServer = TfsConfigurationServerFactory.GetConfigurationServer(new Uri(ConfigurationManager.AppSettings["TfsServer"]));

//Get the catalog of team project collections
CatalogNode catalogNode = configurationServer.CatalogNode;
//Get all CatalogNodes which are ProjectCollection
ReadOnlyCollection<catalognode> tpcNodes = catalogNode.QueryChildren(new Guid[] { CatalogResourceTypes.ProjectCollection }, false, CatalogQueryOptions.None);

//Get InstanceId of a ProjectCollection
Guid tpcId = Guid.Empty;
foreach (CatalogNode tpcNode in tpcNodes)
    tpcId = new Guid(tpcNode.Resource.Properties["InstanceId"]);

//Fill list of projects in a local variable
TfsTeamProjectCollection projectCollection = configurationServer.GetTeamProjectCollection(tpcId);

Now, we need to get the workitems from the TFS query associated to selected TFS project

//Get WorkItem Tracking client for workitem collection for selected ProjectCollection
WorkItemStore workItemStore = projectCollection.GetService<workitemstore>();
//Get Project from Tracking client
Project project = workItemStore.Projects[ConfigurationManager.AppSettings["TfsProjectName"]];

QueryFolder teamQueryFolder = project.QueryHierarchy[ConfigurationManager.AppSettings["TfsQueryGroup"]] as QueryFolder;
QueryItem queryItem = teamQueryFolder[ConfigurationManager.AppSettings["TfsQueryName"]];
QueryDefinition queryDefinition = workItemStore.GetQueryDefinition(queryItem.Id);

Dictionary<string, string=""> variables = new Dictionary<string, string=""> { { "project", queryItem.Project.Name } };

WorkItemCollection workItemCollection = workItemStore.Query(queryDefinition.QueryText, variables);DataTable dt = CreateDataTable();</string,></string,></workitemstore>

Code for getting the Version Control artifacts for selected project's selected workitem

//Get Source Control/Version Control repository for selected project collection
VersionControlServer versionControlServer = projectCollection.GetService<versioncontrolserver>();
//Get Details of Version Control using artifact provider
VersionControlArtifactProvider artifactProvider = versionControlServer.ArtifactProvider;</versioncontrolserver>

Once we get the workItemCollection, we need to make the linq query to get all linked changesets and find the required information. Here, I am only looking for the sourcefile paths associated to a changeset.

//Iterate through each item to get its details
foreach (WorkItem workItem in workItemCollection)
    DataRow dr = dt.NewRow();
    dr["ID"] = workItem.Id;
    dr["Title"] = workItem.Title;

    //use linq to get the linked changesets to a workitem
    IEnumerable<changeset> changesets = workItem.Links.OfType<externallink>().Select(link => artifactProvider.GetChangeset(new Uri(link.LinkedArtifactUri)));

    //iterate through changesets' to get each changeset details
    foreach (Changeset changeset in changesets)
        dr["ChangesetId"] = changeset.ChangesetId;
        foreach (Change changes in changeset.Changes)
            //ServerItem is the full path of a source control file associated to changeset
            if (changes.Item.ServerItem.Contains(ConfigurationManager.AppSettings["DevBranchName"]))
                dr["Fix in DevBranch"] = "Yes";
            else if (changes.Item.ServerItem.Contains(ConfigurationManager.AppSettings["ReleaseBranchName"]))
                dr["Fix in ReleaseBranch"] = "Yes";

//Write datable to excel file using StreamWriter

To fill details of WorkItem and Changeset create a data table, you can use any other way also.

public static DataTable CreateDataTable()
    DataTable dt = new DataTable();

    dt.Columns.Add("Fix in DevBranch");
    dt.Columns.Add("Fix in ReleaseBranch");
    return dt;

Below is the function to create the excel file with the tabular data filled in datatable

public static void WriteToExcel(DataTable dataTable)
    StreamWriter streamWriter = new StreamWriter(ConfigurationManager.AppSettings["ExcelFile"], false);
    for (int i = 0; i < dataTable.Columns.Count; i++)
        streamWriter.Write(dataTable.Columns[i].ToString().ToUpper() + "\t");

    for (int i = 0; i < (dataTable.Rows.Count); i++)
        for (int j = 0; j < dataTable.Columns.Count; j++)
            if (dataTable.Rows[i][j] != null)
                streamWriter.Write(Convert.ToString(dataTable.Rows[i][j]) + "\t");


Points of Interest

This code is beneficial to all, who wants to automate their TFS reports. for understanding the basics of TFS library extensions, please read this.



This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Asmita Bhurke
United States United States
No Biography provided

Comments and Discussions

QuestionGreat article, thanks for sharing. Pin
Member 1338784220-Sep-19 7:39
MemberMember 1338784220-Sep-19 7:39 
QuestionWorks with one changeset Pin
Member 109911152-Jun-17 9:06
MemberMember 109911152-Jun-17 9:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.