TFS 2010 custom workflow activity to get a version number from a database

createDBObjects.sql

CREATE TABLE [dbo].[Version](
	[Tag] [varchar](20) NOT NULL,
	[Major] [int] NOT NULL,
	[Minor] [int] NOT NULL,
	[Build] [int] NOT NULL,
	[Revision] [int] NOT NULL,
 CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED 
(
	[Tag] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

go

CREATE PROCEDURE [dbo].[SetVersionNumber]
	@major int,
	@minor int, 
	@build int, 
	@revision int, 
	@tag varchar(200)
AS
BEGIN
	update [Version] set major = @major, minor = @minor,build = @build, revision = @revision where Tag = @tag;
END

go 

CREATE PROCEDURE [dbo].[GetVersionNumber]
	@tag varchar(200)
AS
BEGIN
	select major,minor, build,revision from [Version] where Tag = @tag;
END
GO

GetBuildNumberFromDatabase.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.TeamFoundation.Build.Client;
using System.Activities;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using Microsoft.TeamFoundation.Build.Workflow.Tracking;
using Microsoft.TeamFoundation.Build.Workflow.Activities;

namespace TFS.CustomBuildActivities
{
	[BuildActivity(HostEnvironmentOption.Controller)]
	public sealed class GetBuildNumberFromDatabase : CodeActivity
	{
		[RequiredArgument]
		public InArgument<string> ConnectionString
		{
			get;
			set;
		}

		public OutArgument<string> VersionNumber
		{
			get;
			set;
		}

		public InArgument<string> SearchTag
		{
			get;
			set;
		}

		public InArgument<bool> UpdateMajor
		{
			get;
			set;
		}

		public InArgument<bool> UpdateMinor
		{
			get;
			set;
		}

		public InArgument<bool> UpdateBuild
		{
			get;
			set;
		}

		public InArgument<bool> UpdateRevision
		{
			get;
			set;
		}

		public InArgument<bool> StripRevision
		{
			get;
			set;
		}

		

		protected override void Execute(CodeActivityContext context)
		{
			int major = 0, minor = 0, build = 0, revision = 0;
			try
			{
				GetBuildNumber(context, out major, out minor, out build, out revision);
			}
			catch (SqlException ex)
			{
				context.TrackBuildError("There was an error retrieving the build number: " + ex.Message);
			}

			context.TrackBuildMessage("Beginning Build Number\"" + CreateBuildString(context, major, minor, build, revision) + "\".", BuildMessageImportance.High);

			if (UpdateMajor.Get(context)) { major++; }

			if (UpdateMinor.Get(context)) { minor++; }

			if (UpdateBuild.Get(context)) { build++; }

			if (UpdateRevision.Get(context)) { revision++; }

			VersionNumber.Set(context, CreateBuildString(context,major, minor, build, revision));

			try
			{
				UpdateBuildNumber(context, major, minor, build, revision);
			}
			catch (SqlException ex)
			{
				context.TrackBuildError("There was an error setting the updated build number: " + ex.Message);
				
				return;
			}
			context.TrackBuildMessage("Ending Build Number\"" + VersionNumber.Get(context) + "\".", BuildMessageImportance.High);
		}


		private string CreateBuildString(ActivityContext ac, int major, int minor, int build, int revision)
		{
			string ret = major.ToString() + "." + minor.ToString() + "." + build.ToString();
			if (!StripRevision.Get(ac))
			{
				ret = ret + "." + revision.ToString();
			}
			return ret;
		}

		private void UpdateBuildNumber(ActivityContext ac, int major, int minor, int build, int revision)
		{
			using (SqlConnection connection = new SqlConnection(ConnectionString.Get(ac)))
			{
				SqlCommand cmd = new SqlCommand("SetVersionNumber", connection);
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.AddWithValue("@major", major);
				cmd.Parameters.AddWithValue("@minor", minor);
				cmd.Parameters.AddWithValue("@build", build);
				cmd.Parameters.AddWithValue("@revision", revision);
				cmd.Parameters.AddWithValue("@tag", SearchTag.Get(ac));
				connection.Open();
				cmd.ExecuteNonQuery();
			}
		}

		private void GetBuildNumber(ActivityContext ac, out int major, out int minor, out int build, out int revision)
		{
			major = minor = build = revision = 0;
			using (SqlConnection connection = new SqlConnection(ConnectionString.Get(ac)))
			{
				SqlCommand cmd = new SqlCommand("GetVersionNumber", connection);
				cmd.CommandType = CommandType.StoredProcedure;
				SqlParameter tag = cmd.Parameters.Add("@tag", SqlDbType.VarChar, 20);
				tag.Value = SearchTag.Get(ac);
				connection.Open();
				using (SqlDataReader reader = cmd.ExecuteReader())
				{
					if (reader.Read())
					{
						major = reader.GetInt32(0);
						minor = reader.GetInt32(1);
						build = reader.GetInt32(2);
						revision = reader.GetInt32(3);
					}
					else
					{
						throw new InvalidDataException("No data was returned from the version query");
					}
				}
			}
		}
	}
}