My manager needed a report on bugs (work items) in TFS (Team Foundation Server). The bugs were in different areas of the project and I had been using different queries in Team Explorer to get and view the data. So now I wanted to automate this so I did not have to run the different queries and then create a report by hand from him. My design goal was to use the queries that are designed in VS Team Explorer and saved to .WIG files. The code should then pull the query from all .WIG files. This will allow queries to be updated and new ones to be added with out the need to change any of the code. With this in mind I downloaded the VS 2005 SDK which has some classes that allow you to programmatically work with TFS. You can download the SDK from the following link:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7e0fdd66-698a-4e6a-b373-bd0642847ab7&DisplayLang=en
While looking through this I found an excellent sample showed how to run TFS queries. The sample is “Work Item Query Sample.Zip” in the version of the SDK I am using it is located in the following folder:
“Visual Studio 2005 SDK\2006.04\VisualStudioTeamSystemIntegration\Work Item Tracking”
Using this as a starting point I created a small app that would use the .wiq files to get the queries to run. After it ran the query I would place the results into a Table which is then stored into a DataSet. This way I can just walk the different tables or bind them to a control to create my report. Here is the code for anyone who is interested. Standard disclaimer: “I don’t have much error checking in this code so use at your own risk”
My using statements:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using System.Diagnostics;
using System.Data;
using System.Xml;
using System.IO;
using System.Collections;
using System.Configuration;
C# Code:
class Program
{
static DataSet BugDataSet;
static void Main(string[] args)
{
// get the path to the querys.
DirectoryInfo myDir;
if (ConfigurationManager.AppSettings["QueryDirPath"].ToString().Length > 0)
{
myDir = new DirectoryInfo(ConfigurationManager.AppSettings["QueryDirPath"].ToString());
}
else
{
//if it is not in the config file we assume they are in the following folder.
myDir = new DirectoryInfo(Environment.CurrentDirectory + "\\TFSquerys");
}
FileInfo[] myfiles = myDir.GetFiles("*.wiq");
int FileCount = myfiles.Length;
BugDataSet = new DataSet();
for (int i = 0; i < FileCount; i++)
{
Console.WriteLine(myfiles[i].FullName);
XmlReader reader = XmlReader.Create(myfiles[i].FullName);
reader.ReadToFollowing("TeamFoundationServer");
string connection = reader.ReadString();
reader.ReadToFollowing("TeamProject");
string project = reader.ReadString();
reader.ReadToFollowing("Wiql");
string DTSQuery = reader.ReadString();
int finddot = myfiles[i].Name.IndexOf(".");
ProcessDTSQuery(connection, project, DTSQuery, myfiles[i].Name.Substring(0,finddot));
}
CreateReport(); // this code is not included!
}
static void ProcessDTSQuery(string connection, string project, string DTSQuery, string BugType)
{
//run the query and save the results into a table
bool idField = false;
TeamFoundationServer tfs = TeamFoundationServerFactory.GetServer(connection);
WorkItemStore store = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));
Hashtable context = new Hashtable();
context.Add("project", project);
WorkItemCollection result = store.Query(DTSQuery, context);
Console.WriteLine(BugType);
DisplayFieldList fieldList = result.Query.DisplayFieldList;
//give the table the same name as the wiq file.
DataTable table = new DataTable(BugType);
DataColumn column;
DataRow row;
int i;
for (i = 0; i < fieldList.Count; i++)
{
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = fieldList[i].Name;
table.Columns.Add(column);
if (fieldList[i].Name == "ID")
idField = true;
}
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "WorkItemUri";
table.Columns.Add(column);
for (int ii = 0; ii < result.Count; ii++)
{
row = table.NewRow();
for(i= 0; i< fieldList.Count; i++)
{
object o = result[ii].Fields[fieldList[i].Name].Value;
string s;
if (o is DateTime)
{
s = ((DateTime)o).ToLocalTime().ToString();
}
else if (o != null)
{
s = o.ToString();
}
else
{
s = "";
}
row[fieldList[i].Name] = s;
}
if(idField) //only add this if we have the workitem ID
// Workitem.aspx allows you to get a web page to view the bug using the workitem ID
row["WorkItemUri"] = connection + "/WorkItemTracking/Workitem.aspx?artifactMoniker=" + result[ii].Fields["ID"].Value;
table.Rows.Add(row);
}
BugDataSet.Tables.Add(table);
}
}
Please note I have not posted the code that walks through the DataSet Tables to create my custom report.
Hope this helps if you have a need to pull work item data from TFS.
Enjoy and have fun.
Brian
