Find Changeset Details for All Work Items in a TFS Query

• Download source code - 4.6 KB

Introduction

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

Background

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 following references from Extensions tab in Reference Manager dialog.

So in program.cs file, the 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" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <appSettings>
    <add key="TfsServer" value="http://tfs.yourServer.com:8080/tfs"/>
    <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"/>
  </appSettings>
</configuration>

So initial requirements are done, let's start code by connecting to TFS server and get the TFS project of our concern using the 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"]);
    break;
}

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

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();

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;

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";
                 break;
             }
             else if (changes.Item.ServerItem.Contains
             (ConfigurationManager.AppSettings["ReleaseBranchName"]))
             {
                 dr["Fix in ReleaseBranch"] = "Yes";
                 break;
             }
        }    
    }

    dt.Rows.Add(dr);
}
//Write datable to excel file using StreamWriter
WriteToExcel(dt);

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("ID");
    dt.Columns.Add("Title");
    dt.Columns.Add("ChangesetId");

    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");
    }
    streamWriter.WriteLine();

    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");
            }
            else
            {
                streamWriter.Write("\t");
            }
        }
        streamWriter.WriteLine();
    }
    streamWriter.Close();
}

Points of Interest

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

History

• 14^th April, 2016: Initial version