Getting HTML Fields Data into your TFS Warehouse

• Download source code

Problem

A while back, I was faced with the situation where I wanted to use an HTML control in a process template Work Item but needed that data from that to be passed on to the Warehouse for reporting. For those of you that know how the process WITs work, you’ll know that you can’t mark an HTML data type field as Reportable. Today, I’m going to show you how I managed to accomplish this.

Before We Get Started

I just want to let you know before you read this that this only allows for a small amount of HTML, it is meant for small mark up changes like bold, Italics and Underline. I haven’t fully analyzed where all the compatibility issues are but I know that when you add images to the HTML, this approach sometimes doesn’t work. This is most likely caused because the string field is a [nvarchar](256) in the TFS Warehouse.

Steps to Success

Install the Right Tools

You don’t need these tools but it will make developing, managing and implementing process template changes easier.

• Microsoft Kanban 1.0 Process template
• Microsoft Visual Studio Team Foundation Server 2012 Update 1 Power Tools

Add the HTML field.

I’m using the Card.xml found in the Microsoft Kanban 1.0 process template provided by the ALM Rangers, this blog post explains how to install it. So using this process template you’d browse to ~\Microsoft Kanban 1.0\WorkItem Tracking\TypeDefinitions and open Card.xml in Visual Studio 2012. You should see a windows like below:

Click New and fill in the form as below:

Click the Rules tab and then click new and select AllowExistingValue as below:

Now add a second field, that is the same as the first one except this time add _Copy to the name and ref name fields as below, after this add the same rule as previously added:

Add the new HTML field to the layout

Click layout above and then add a new control by right clicking on any node that isn’t a control and selected New Control. Fill in the properties Field Name and Type as below, the rest is up to you.

Update the WIT for a Team

Next, we are going to need to update the Card WIT in TFS. To do this, Click on Tools > Process Editor > Work Item Types > Import WIT. Browse to the Card.xml file that you were altering and then select the team you want to import this change for and click OK.

Create the Subscriber Plug in for TFS

This has now updated the windows for the Card windows through TFS for the team selected. Go to the TFS WebAccess Portal and add a new Card and you’ll notice the field is there. If you complete the form and click Save, you’ll notice that everything saves smoothly with no issues. Next, we are going to need to create a new Class Library and add a TFSFunctions.cs class (the source in this class is taking from the GlobalList Updater project in the ALM Planning zip file and modified a bit). Add the code below into TFSFunction.cs:

namespace HtmlFieldsInReports
{
#region

using System;

using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.Framework.Server;
using Microsoft.TeamFoundation.WorkItemTracking.Client;

#endregion

public static class TFSFunctions
{
#region Public Methods and Operators

public static WorkItemCollection ExecuteQuery(WorkItemStore store, 
string query, string teamProjectName, string processStepWorkItemType)
{
query = query.ToLower().Replace("@project", teamProjectName);
query = query.ToLower().Replace("@processstepworkitemtype", processStepWorkItemType);
return store.Query(query);
}

public static Uri GetTFSUri(TeamFoundationRequestContext requestContext)
{
return new Uri(requestContext.GetService&
lt;TeamFoundationLocationService>().GetServerAccessMapping
(requestContext).AccessPoint.Replace("localhost", 
Environment.MachineName) + "/" + requestContext.ServiceHost.Name);
}

public static TfsTeamProjectCollection GetTeamProjectCollection
(string requestContextVirtualDirectory, string workItemChangedEventDisplayUrl)
{
string tpcUrl = GetTeamProjectCollectionUrl
(requestContextVirtualDirectory, workItemChangedEventDisplayUrl);
var collection = new TfsTeamProjectCollection(new Uri(tpcUrl));

collection.EnsureAuthenticated();
return collection;
}

public static string GetTeamProjectCollectionUrl
(string requestContextVirtualDirectory, string workItemChangedEventDisplayUrl)
{
string[] strArray = workItemChangedEventDisplayUrl.Split('/');
return string.Format("{0}//{1}{2}", 
strArray[0], strArray[2], requestContextVirtualDirectory);
}

public static WorkItemStore GetWorkItemStore(TfsTeamProjectCollection collection)
{
return (WorkItemStore)collection.GetService(typeof(WorkItemStore));
}

#endregion
}
}

And also, add an HtmlFieldSyncSubscriber.cs file with the code below in it:

namespace HtmlFieldsInReports
{
#region

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Reflection;

using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.Common;
using Microsoft.TeamFoundation.Framework.Server;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Server;

#endregion

public class HtmlFieldSyncSubscriber : ISubscriber
{
// ISubscriber - requested event types

// ISubscriber - name of the plugin
#region Public Properties

public string Name
{
get
{
return "BinaryDigit.Subscribers.HtmlFieldsInReports";
}
}

public SubscriberPriority Priority
{
get
{
return SubscriberPriority.Low;
}
}

#endregion

// ISubscriber - event handler
#region Public Methods and Operators

public EventNotificationStatus ProcessEvent(
TeamFoundationRequestContext requestContext, NotificationType notificationType, 
object notificationEventArgs, out int statusCode, out string statusMessage, 
out ExceptionPropertyCollection properties)
{
this.WriteInfo("In the EventNotificationStatus 
ProcessEvent method now", EventLogEntryType.Information);
string strDump = string.Empty;
statusCode = 0;
properties = null;
statusMessage = string.Empty;

try
{
if (notificationType == NotificationType.Notification &&
amp;amp; notificationEventArgs is WorkItemChangedEvent)
{
// change this object to be a type we can easily get into
var workItemEvent = notificationEventArgs as WorkItemChangedEvent;
string currentField = string.Empty;
IntegerField id = null;
foreach (IntegerField item in workItemEvent.CoreFields.IntegerFields)
{
if (string.Compare(item.Name, "ID", true) == 0)
{
id = item;
break;
}
}

if (id != null)
{
try
{
Uri uri = TFSFunctions.GetTFSUri(requestContext);

if (workItemEvent.TextFields != null)
{
var affectedWorkItems = new List<WorkItem>();
using (TeamFoundationServer tfs = TeamFoundationServerFactory.GetServer(uri))
{
var wit = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));
foreach (TextField item in workItemEvent.TextFields)
{
if (item.Name.ToLower().EndsWith("_copy") &
& item.ReferenceName.ToLower().EndsWith("_copy"))
{
currentField = item.Name;

WorkItem wi = this.SetFieldValue(wit, id, item, uri);
if (wi != null)
{
affectedWorkItems.Add(wi);
}
}
}

if (affectedWorkItems.Count > 0)
{
wit.BatchSave(affectedWorkItems.ToArray());
}
}
}
else
{
return EventNotificationStatus.ActionPermitted;
}
}
catch (Exception ex)
{
this.WriteInfo("Failed to update field '" + 
currentField + "'.nn" + ex, EventLogEntryType.Error);
if (!string.IsNullOrEmpty(currentField))
{
statusMessage = "Failed to update field '" + currentField + "'.";
return EventNotificationStatus.ActionDenied;
}
}
}
}
}
catch (Exception ex)
{
// diagnostics
strDump = string.Format
("There was a unhandled exception: {0} n {1}", ex, ex.StackTrace);
this.WriteInfo(strDump, EventLogEntryType.Error);
}

return EventNotificationStatus.ActionPermitted;
}

public Type[] SubscribedTypes()
{
return new[] { typeof(WorkItemChangedEvent) };
}

// helper - dumping diagnostics
public void WriteInfo(string strDump, EventLogEntryType entryType)
{
var log = new EventLog();
log.Source = "TFS Services";
if (!EventLog.SourceExists(log.Source))
{
EventLog.CreateEventSource(log.Source, "Application");
}

string strMessage = string.Format("The TFS server plugin {0} 
provides the following logging information:nn{1}", 
Assembly.GetCallingAssembly().GetName().Name, strDump);
log.WriteEntry(strMessage, entryType);
}

#endregion

#region Methods

private WorkItem SetFieldValue(WorkItemStore wit, 
IntegerField id, TextField currentHtmlField, Uri uri)
{
WorkItem affectedWorkItem = null;

WorkItemCollection result = wit.Query
("SELECT [System.Id] FROM WorkItems WHERE [System.Id] = " + id.NewValue);
foreach (WorkItem wi in result)
{
string fieldLookingFor = currentHtmlField.Name.Remove
(currentHtmlField.Name.ToLower().IndexOf("_copy")).Trim();
foreach (Field item in wi.Fields)
{
if (string.Compare(item.Name, fieldLookingFor, true) == 0)
{
if (item.Value.ToString() != currentHtmlField.Value)
{
wi.Open();
item.Value = currentHtmlField.Value;
affectedWorkItem = wi;
}

break;
}
}
}

return affectedWorkItem;
}

#endregion
}
}

You need to add the below references to your project and then it will build:

Add the Plugin to TFS

Once this is compiled, take the outputted assemblies and copy them to C:Program FilesMicrosoft Team Foundation Server 11.0Application TierWeb ServicesbinPlugins on the TFS server. When you alter a Card now in any of the Card Work Item windows, this plugin will now copy the HTML from your _Copy field into the other field and when the data is moved to the warehouse the HTML (string field) will now be moved as well.

Verify the Data

After the data is synced, you can use the SQL query below to verify that the plugin worked correctly.

SELECT *
FROM [dbo].[DimWorkItem] with (nolock)
WHERE [Fields_MyHtmlField] IS NOT NULL
order by [WorkItemSK] desc

Use the HTML Field Data in a Report

Now you can write reports and when displaying the field, mark it as an HTML field as below:

Hope this helps you. Feel free to request more information on this topic if you need.