Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Getting HTML Fields Data into your TFS Warehouse

, 30 May 2013
Rate this:
Please Sign up or sign in to vote.
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

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. 

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.

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)

Share

About the Author

Gordon W Beeming
Software Developer Derivco
South Africa South Africa
Gordon Beeming is a Software Developer at Derivco in the sunny city of Durban, South Africa. He spends most his time hacking away at the keyboard in Visual Studio or with his family relaxing. He is a Visual Studio ALM Rangers, Visual Studio ALM MVP and Friend of Red Gate. His blog is at 31og.com and you can follow him on Twitter at twitter.com/gordonbeeming
 
http://31og.com
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralMy vote of 1 PinmemberClifford Nelson29-May-13 11:13 
GeneralRe: My vote of 1 PinprofessionalGordon W Beeming29-May-13 12:34 
AnswerDownload link PinmemberClifford Nelson29-May-13 14:13 
GeneralRe: Download link PinprofessionalGordon W Beeming29-May-13 18:29 
AnswerRe: Download link PinmemberClifford Nelson30-May-13 7:01 
GeneralRe: Download link PinprofessionalGordon W Beeming30-May-13 8:17 
AnswerRe: Download link PinmemberClifford Nelson30-May-13 11:28 
GeneralRe: Download link PinprofessionalGordon W Beeming30-May-13 18:31 
QuestionLinks broken PinmemberSnowmanMac14-May-13 2:35 
AnswerRe: Links broken PinmemberB1n4ry D1g1t14-May-13 3:09 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 30 May 2013
Article Copyright 2013 by Gordon W Beeming
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid