Click here to Skip to main content
15,867,834 members
Articles / DevOps / TFS

Getting HTML Fields Data into your TFS Warehouse

Rate me:
Please Sign up or sign in to vote.
3.75/5 (5 votes)
30 May 2013CPOL3 min read 32K   96   3   11
A small trick on how to get HTML data into your warehouse.

Problem  

A while back I was faced with the situation where I wanted to use a 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 a HTML data type field as Reportable. Today I’m going to show you how I managed to accomplish this. 

Before we get start

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.

Add the html field 

I’m using the Card.xml found in the Microsoft Kanban 1.0 process template provided by the ALM Rangersthis 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

Image0

Click New and fill in the form as below:

Image1

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

Image2

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

Image3

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.

Image4

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 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

C#
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<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 a HtmlFieldSyncSubscriber.cs file with the code below in it. 

C#
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 && 
                       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 + "'.\n\n" + 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:\n\n{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 you project and then it will build

Image5

Add the plugin to TFS

Once this is compiled, take the outputted assemblies and copy them to C:\Program Files\Microsoft Team Foundation Server 11.0\Application Tier\Web Services\bin\Plugins 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 the the plugin worked correctly.

SQL
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.

Image6

Hope this helps you, feel free to request more info on this topic if you need.

License

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


Written By
Architect SSW
South Africa South Africa

Comments and Discussions

 
QuestionDoes this work around the 255 character limit for the String type? Pin
Anthony Tsaukpaetra28-Oct-14 12:35
Anthony Tsaukpaetra28-Oct-14 12:35 
GeneralMy vote of 1 Pin
Clifford Nelson29-May-13 11:13
Clifford Nelson29-May-13 11:13 
GeneralRe: My vote of 1 Pin
Gordon Beeming29-May-13 12:34
professionalGordon Beeming29-May-13 12:34 
AnswerDownload link Pin
Clifford Nelson29-May-13 14:13
Clifford Nelson29-May-13 14:13 
GeneralRe: Download link Pin
Gordon Beeming29-May-13 18:29
professionalGordon Beeming29-May-13 18:29 
AnswerRe: Download link Pin
Clifford Nelson30-May-13 7:01
Clifford Nelson30-May-13 7:01 
GeneralRe: Download link Pin
Gordon Beeming30-May-13 8:17
professionalGordon Beeming30-May-13 8:17 
AnswerRe: Download link Pin
Clifford Nelson30-May-13 11:28
Clifford Nelson30-May-13 11:28 
GeneralRe: Download link Pin
Gordon Beeming30-May-13 18:31
professionalGordon Beeming30-May-13 18:31 
QuestionLinks broken Pin
SnowmanMac14-May-13 2:35
SnowmanMac14-May-13 2:35 
AnswerRe: Links broken Pin
Gordon Beeming14-May-13 3:09
professionalGordon Beeming14-May-13 3:09 

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.