Inspect Object Locking with T-SQL

I recently came across a scenario whereby we wanted to programmatically, via T-SQL, inspect locking behaviour on objects in a particular database when operations were being performed on given data (actually just a SELECT (WITH (NOLOCK) and DELETE statement, on a table). The requirement was to get a direct view on whether the table was having an exclusive lock thrust upon it, or whether shared access was still in play (and get an insight into whether we were in the realms of row-level locking or not).

This turns out to be relatively easy through the use of the inbuilt Dynamic Management Views; specifically the sys.dm_tran_locks view, coupled with a cheeky join to sys.objects for information to identify the object being referenced (this is my own interpretation, from scratch, but you can easily find many similar examples so pick what floats your boat!):

/*
	Interrogate the lock types at database level (can see the lock type on a table when another statement is running)
	-------------------------------------------------------------------------------------------------------------------

	1) For information on the different lock modes in SQL Server visit the following resources:
		a) https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx
		b) http://lockergnome.com/2010/01/26/what-are-the-different-lock-modes-in-sql-server/
		c) https://logicalread.com/sql-server-lock-modes-mc03/ - Haven't actually read this one yet but looked intriguing enough to include as a 'bonus' :o)
	2) An interesting aside into the WITH (NOLOCK) hint: http://sqlsoldier.net/wp/sqlserver/thetruthaboutnolockhints
	3) The difference by IX and X locks: http://database.ittoolbox.com/groups/technical-functional/sql-server-l/difference-between-x-and-ix-lock-151903
*/
SELECT 
tl.resource_type AS [ResourceType]
, CASE 
	WHEN tl.request_mode = 'Sch-S'
	THEN 'Schema Stability (ensure entity cannot be dropped)'
	WHEN tl.request_mode = 'IS'
	THEN 'Intent Shared'
	WHEN tl.request_mode = 'IX'
	THEN 'Intent Exclusive (exclusive lock to subordinate resource in the lock hierarchy - table can still be accessed in part)'
	WHEN tl.request_mode = 'X'
	THEN 'Exclusive (full, exclusive access to the resource)'
	ELSE tl.request_mode
END AS [LockType]
, so.[object_id] AS [ObjectId] 
, so.name AS [ObjectName]
, so.type_desc AS [TypeDesc]
FROM sys.dm_tran_locks tl
	-- JOIN to sys.objects to get identifiable information on the resource marked with a lock
	INNER JOIN sys.objects so ON tl.resource_associated_entity_id = so.object_id
-- Show lock information for the database in scope (this could be parameterised - this could be placed in an stored procedure, for example)
WHERE tl.resource_database_id = DB_ID();

In the comments above the code snippet, I’ve included a couple of links to detail the various lock types (information that can be shown in the request_mode view column) that you may encounter. Just run the query whilst a transaction that you want to inspect (for locking activity) is in progress, and voila, you’ll get an idea as to how the resources are being accessed and what impact this is having locking wise.

As a quick illustration let’s knock up a test database and table, using the following script which incorporates a very trivial setup:

-- Test script for checking out the dm_tran_locks test script

-- 1) Create the test database
CREATE DATABASE [LockTestDatabase];
GO

USE [LockTestDatabase];

-- 2) Create the LockTest table within the LockTestDatabase database (super noddy example for illustration only)
CREATE TABLE [dbo].[LockTest]
(
	Id				INT				PRIMARY		KEY	IDENTITY(1, 1)	NOT NULL
	, SomeInfo		NVARCHAR(20)									NOT NULL
);

-- 3) Create some simple test data
DECLARE
@Counter		INT = 1;

WHILE (@Counter <= 250000)		-- Want a non-trivial amount of data so queries take a couple of seconds to complete
BEGIN

	PRINT (@Counter);

	INSERT INTO [dbo].[LockTest]
	(
		SomeInfo
	)
	VALUES
	(
		'Information' + CONVERT(NVARCHAR(6), @Counter)
	);

	SET 
	@Counter += 1;

END;

The insertion of test records takes a short while so it’s possible to run the dm_tran_locks test script and obtain the following results:

LockTest Table Lock State during Insert.

LockTest Table Lock State during Insert.

After the test insertions are complete, attempt to run a SELECT statement using the WITH (NOLOCK) hint:

SELECT 
lt.Id
, lt.SomeInfo
FROM dbo.LockTest lt WITH (NOLOCK);

You should hopefully observe a shared resource lock, for schema stability only, that essentially just prevents the resource from being dropped whilst information is being requested from the table:

LockTest Table Lock State during SELECT (WITH (NOLOCK)).

LockTest Table Lock State during SELECT (WITH (NOLOCK)).

I’m not going into any kind of detailed explanation with regards to the lock types here, I’m just proving that observing the lock types being applied is easily possible. Lastly, attempt a deletion (wrapped in a rollback, so it can easily be re-run):

BEGIN TRANSACTION

	DELETE
	FROM dbo.LockTest;

ROLLBACK TRANSACTION

A lock is applied that indicates exclusive access to the resource (due to the nature of the delete here, as all rows are being removed), aka table:

LockTest Table Lock State during Deletion.

LockTest Table Lock State during Deletion.

This gives you an insight into how straightforward it is to get an impression on how your SQL objects are being influenced, locking wise, by your code. If anyone has any suggestions on further reading, more useful snippets, etc. then please add a comment below. Thanks, all.

Where You At…

Hi there wonderful people! I hope everyone is keeping well and remembering to be awesome :o)

The great wave of life has risen up and crashed back down a fair few times in the last handful of months which has, in turn, lead to a serious drought on new content; apologies for this.

Being brutally honest I’ve found myself a little bit zapped of energy and inspiration in general. We had a very long and protracted house move which involved a good number of twists and turns, mixing that in with a busy period at work (and a stretch without good internet access at the new house, in the midst of all this) and I think I did approach (and am just getting out of the other side of) something close to burnout; it happens to all of us at some point or another. It has meant that I’ve fallen into a little rut of ‘imposter syndrome’; a dose of passion is required to climb out! With all of the change going on around me nagging self-doubt as to my abilities has come into the fold and I’ve been scratching my head on how best to deal with it; nothing but the honest truth from me.

Therefore, I’m planning on trying to take some responsibility for getting back into the game and rolling some more content on to the board; reflecting a drive to inject some confidence and passion into the proceedings once again. Thanks for sticking with this, it’s a key thing in the development business to keep reaching for new horizons (and to pick which horizons should be pursued and which should be ignored, as there are so many damn options) so getting the engine kick-started again, and carving out the time to keep it ticking over, is now a priority.

Content is on the way then…happy coding until the next time :o)

Session State Behaviour & Async Headaches

I was battling a little issue today surrounding an action method no longer being called asynchronously; the issue turned out to be related to some recent session-based code being added to our code base. In short, the minute session is detected in the underlying code, the ‘default’ behaviour for session state handling throws a monkey wrench in asynchronicity, regardless of the operation being performed on session data (i.e. writing to the session or just reading from the session). This, for me, turned into a performance headache.

There is an attribute that can be placed at controller level that states ‘I’m reading from session only, please continue to allow asynchronous operations’, which when used looks like this:

[SessionState(System.Web.SessionState.SessionStateBehavior.ReadOnly)]
public class TestController : Controller
{
          ……
}

However, if you want to implement a control mechanism at the action level you need to travel down the custom controller factory/attribute route. This post turned out to be a lifesaver: Session State Behaviour Per Action in ASP.NET MVC

In short, this setup enables you to set session state behaviour handling at the action level by adorning the target method with a custom attribute; bonza!

When inspecting this and underlying, base class, implementations you will most likely discover that it’s not immediately clear how to handle scenarios where overridden methods exist (where methods match by name but differ by signature). This, for me, caused several crunches into the dreaded AmbigiousMatchException.

The implementation below shows my modified override of the DefaultControllerFactory GetControllerSessionBehavior method that is designed to a) avoid exceptions and b) only try to ‘discover’ the attribute and apply custom session state behaviour handling where a single method is ‘matched’ (based on the supplied RequestContext). If the custom attribute is not found, or more than one method is found matching by name (or another error occurs) base logic kicks in and takes precedence:

        /// <summary>
        /// Public overridden method that looks at the controller/action method being called and attempts
        /// to see if a custom ActionSessionStateAttribute (determining how session state behaviour should work) is in play.
        /// If it is, return the custom attributes SessionStateBehaviour value via the Behaviour property, in all other instances
        /// refer to the base class for obtaining a SessionStateBehavior value (via base.GetControllerSessionBehavior).
        /// </summary>
        /// <param name="requestContext">The request context object (to get information about the action called).</param>
        /// <param name="controllerType">The controller type linked to this request (used in a reflection operation to access a MethodInfo object).</param>
        /// <returns>A SessionStateBehavior enumeration value (either dictacted by us based on ActionSessionStateAttribute usage or the base implementation).</returns>
        protected override SessionStateBehavior GetControllerSessionBehavior(RequestContext requestContext, Type controllerType)
        {
            try
            {
                // At the time of writing base.GetControllerSessionBehavior just returns SessionStateBehaviour.Default but to make this robust we should just call
                // base.GetControllerSessionBehavior if the controllerType is null so any changes to the base behaviour in future are adhered to
                if (controllerType != null)
                {
                    // Defensive code to check the state of RouteData before proceeding
                    if (requestContext.RouteData != null
                        && requestContext.RouteData.Values != null
                        && requestContext.RouteData.Values["action"] != null)
                    {
                        // Attempt to find the MethodInfo type behind the action method requested. There is a limitation here (just because of what we are provided with) that
                        // this piece of custom attribute handling (for ActionSessionStateAttribute) can only be accurately determined if we find just one matching method
                        string actionName = requestContext.RouteData.Values["action"].ToString();
                        List<MethodInfo> controllerMatchingActionMethods = controllerType.GetMethods(BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance)
                            .Where(method => method.Name.Equals(actionName, StringComparison.InvariantCultureIgnoreCase)).ToList();

                        // In order to avoid ambiguous match exceptions (plus we don't have enough information about method parameter types to pick the correct method in the case
                        // where more than one match exists) I needed to rig this in such a way that it can only work where one matching method, by name, exists (works for our current use cases) 
                        if (controllerMatchingActionMethods != null && controllerMatchingActionMethods.Count == 1)
                        {
                            MethodInfo matchingActionMethod = controllerMatchingActionMethods.FirstOrDefault();

                            if (matchingActionMethod != null)
                            {
                                // Does the action method requested use the custom ActionSessionStateAttribute. If yes, we can return the SessionStateBehaviour specified by the
                                // developer who used the attribute. Otherwise, just fail over to base logic
                                ActionSessionStateAttribute actionSessionStateAttr =
                                    matchingActionMethod.GetCustomAttributes(typeof(ActionSessionStateAttribute), false)
                                        .OfType<ActionSessionStateAttribute>()
                                            .FirstOrDefault();

                                if (actionSessionStateAttr != null)
                                {
                                    return actionSessionStateAttr.Behaviour;
                                }
                            }                       
                        }
                    }
                }
            }
            catch
            {
                // If any issues occur with our custom SessionStateBehavior inferring handling we're best to just let the base method calculate this instead (best efforts 
                // have been made to avoid exceptions where possible). Could consider logging here in future (but we're in an odd place in the MVC lifecycle, could cause
                // ourselves more issues by attempting this so will only do if absolutely required)
            }

            return base.GetControllerSessionBehavior(requestContext, controllerType);   
        }

This appeared to be a pretty robust solution in my case (and we gained back the asynchronous processing on the targetted methods = big plus), so, hopefully, this comes in handy for others at some point.

Cheers all!

Zoning out with Moment Timezone

I’ve recently been heavily embedded in implementing time zone sensitivity into a web application and I thought I’d share my first experiences on handling this from the perspective of the browser.

A great little library for handling this kind of tricky number can be found in the form of Moment Timezone, which sits proudly beside Moment.js, but as a full date parsing solution incorporating time zones.

The part of the library that really caught my attention was the time zone inferring abilities of the library; the superbly named ‘guess‘ function (loving the name!). The function, despite the name, is actually pretty sophisticated, so let’s take a look at a working example and how the documentation defines the ‘guts’ of its time zone guessing powers.

Moment Timezone can be installed and used in a number of different ways, as described here, but I went with the good old classic method of adding a NuGet package via Visual Studio:

Adding Moment Timezone via NuGet.

Adding Moment Timezone via NuGet.

Or, if you want to use the Package Manager Console then use this nugget instead:

Install-Package Moment.Timezone.js

Once the package is installed, alive and kicking we need to (as you would expect) reference the supporting Moment JavaScript library followed by the Moment Timezone based library, as follows:

<script src="~/Scripts/moment.min.js" type="text/javascript"></script>
<script src="~/Scripts/moment-timezone-with-data.min.js" type="text/javascript"></script>

You are then ready to utilise the guess function in a stupendous one-liner, just like this (wrapped in a jQuery document ready function, in this example):

<script type="text/javascript">
    // On page load grab a value denoting the time zone of the browser
    $(function () {
        // Log to the console the result of the call to moment.tz.guess()
        console.log(moment.tz.guess());
    });
</script>

The screenshots listed here show just a few examples of how the guess function works (by providing a tz database, or IANA database, value denoting which time zone Moment Timezone has inferred the client is in).

Moment Guess Usage London.

Moment Guess Usage London.

Moment Guess Usage Cairo.

Moment Guess Usage Cairo.

Moment Guess Usage Havana.

Moment Guess Usage Havana.

For newer, supporting browsers, Moment Timezone can utilise the Internationalization API (Intl.DateTimeFormat().resolvedOptions().timeZone) to obtain time zone information from the browser. For other browsers, Moment Timezone will gather data for a handful of moments from around the current year, using Date#getTimezoneOffset and Date#toString, to intelligently infer as much about the user’s environment as possible. From this information, a comparison is made against entries in the time zone database and the best match is returned. The most interesting part of this process is what happens in the case of a tied match; in this instance, a cities population becomes a deciding factor (the time zone linking to a city with the largest population is returned).

A full listing of tz database values can be found using the link below, showing the range of options available including historical time zones. It’s worth noting that the tz database also forms the backbone of the very popular Joda-Time and Noda Time date/time and timezone handling libraries (Java and C#, respectively; from the legendary Mr Skeet!).

List of tz database zones

For the project I was involved with, I ended up using Noda Time to actually perform conversions server side, utilising Moment Timezone to provide a ‘best stab’ at a user’s timezone on first access of the system. I’d like to give this the attention it deserves in a follow-up post.

Have a great week everyone, until the next time!

Classes and instances…what gives!

My brother, who is a DevOps and integrations whizz, got around to quizzing me, after hearing chatter amongst the nearby developer folk in his building, about the wonderful world of classes and instances, as they pertain to C#.

I reeled off the best explanation I could as I sipped on the best damn gin ever (actually, voted the UK’s best, check this out) and scoffed down some superb steak and chips. I didn’t think my musings were all that bad, but I got to thinking that formalising and solidify my thoughts on the matter wouldn’t hurt. Last aside, if you’re in Norfolk and fancy a good meal this is worth hitting up:

The Boars Spooner Row

What is a steak…I mean, class!?

Food on the brain! Ok, in layman’s terms, a class simply defines a template or blueprint for anything being represented in a given computer program. This blueprint contains (but doesn’t have to and is not limited to), on a basic level, properties that describe the thing being templated and methods that represent actions or functions (that may or may not receive external stimuli, or variables) the, for want of a better term, thing can perform. The class, in and of itself, does nothing up until the point it is brought into life…meaning when an instance is created (ignoring static classes, for the purposes of this explanation).

So, what is an instance?

Instances, typically, are brought to life for actual use, in C#, using the new keyword and all we are doing here is bringing an occurrence (to try and avoid typing instance, again) of a given blueprint into being so the descriptive values of the object can be accessed and the functionality triggered.

I would normally use the tried and tested example of vehicles to show how this actually works, with a little dip into inheritance to boot, but I’m going off piste with the first thing that came into my head…different types of homes is what I’m going with.

Let’s start with a blueprint (or class) for a Home. I don’t want this to be too complicated but going too trivial may not get the key points across, so hopefully this middle ground will make sense:

/// <summary>
/// The blueprint, in our application, for a place
/// to live.
/// </summary>
public class Home
{
	#region Private Readonly Data Fields

	/// <summary>
	/// Every home is expected to have rooms. This value, as it's marked
	/// as readonly, can only be set with a value here as part of the declaration or
	/// as part of a 'constructor' (that is involved in building an instance of a home) - in this 
	/// first iteration the number of rooms in a home isn't going to change (we'll come back to this!).
	/// </summary>
	private readonly int numberOfRooms;

	#endregion Private Readonly Data Fields

	#region Private Data Fields

	/// <summary>
	/// A private variable that keeps track of whether the 
	/// door to the home is open or closed. The door to a home
	/// can only be opened/closed by triggering the OpenDoor/CloseDoor
	/// methods on an 'instance' of the type, no direct 
	/// access is allowed = encapsulation.
	/// </summary>
	private bool doorOpen = false;

	#endregion Private Data Fields

	#region Public Properties

	/// <summary>
	/// Allow an object user to get a value representing if a home's
	/// door is open or closed, without allowing them to directly 
	/// change the state of the door.
	/// </summary>
	public bool IsDoorOpen
	{
		get
		{
			return doorOpen;
		}
	}

	/// <summary>
	/// Much like with IsDoorOpen, allow an object user to get a 
	/// readout of the number of rooms in this home without any direct
	/// access to change it at this point (and the underlying variable
	/// is currently readonly anyway, disallowing changes at this time).
	/// </summary>
	public int NumberOfRooms
	{
		get
		{
			return numberOfRooms;
		}
	}

	#endregion Public Properties

	#region Constructor

	/// <summary>
	/// The 'constructor' for a Home that is used to setup object
	/// state for each and every instance of a home.
	/// </summary>
	/// <param name="roomCount">The number of rooms that are in this house (provided by the object user).</param>
	public Home(int roomCount)
	{
		numberOfRooms = roomCount;
	}

	#endregion Constructor

	#region Public Methods

	/// <summary>
	/// Public method that triggers an action on this home, i.e. opens
	/// the door of this home.
	/// </summary>
	public void OpenDoor()
	{
		// Opens the door to the house
		doorOpen = true;

		// Perhaps other things happen as a result of this...
		Console.WriteLine("The door on this home has been opened.");
	}

	/// <summary>
	/// Public method that triggers an action on this home, i.e. closes
	/// the door of this home. 
	/// </summary>
	public void CloseDoor()
	{
		// Closes the door to the house
		doorOpen = false;

		// Perhaps other things happen a result of this...
		Console.WriteLine("The door on this home has been closed.");
	}

	#endregion Public Methods
}

I’ve outlined the starting concept of what I think a ‘Home’ looks and feels like. A home has, from my very barebones view (forgetting about things like walls, ahem!):

  • A number of rooms.
  • A door.
  • A way for the door to be opened and closed.

Obviously, homes are far more complicated than this, but this will get us going. Regardless of the keywords and definitions used this is nothing more than a blueprint, an instance of an object is required to start interacting with a home, as follows:

        /// Create an instance of a home, using the blueprint provided, and open
        /// then close the door (as well as read out the number of rooms).
        /// </summary>
        private static void PlayWithAHome()
        {
            // Use the 'Home' class blueprint to create an 'instance' of a Home so we can actually start reading/triggering facets of it
            // The Home blueprint demands, in this case, that we provide the number or rooms (as part of the constructor)
            Home testHome = new Home(6);

            // Let's use our home...
            Console.WriteLine($"The home has { testHome.NumberOfRooms } rooms.");               // How many rooms does the home have
            Console.WriteLine($"The door is { (testHome.IsDoorOpen ? "open" : "closed") }.");   // Is the door open or closed (should start closed)

            // Let's open the door (we should get a console readout as part of triggering this functionality on a Home)
            testHome.OpenDoor();

            Console.WriteLine($"The door is now { (testHome.IsDoorOpen ? "open" : "closed") }.");   // Is the door open or closed (should now be open)

            // Stop the application so we can read the output
            Console.Read();
        }
Home object being used.

Home object being used.

A simple run through then; a home has a blueprint that defines it will contain a certain number of rooms, a door, a way to read out the number of rooms and whether the door is ajar (private fields and properties) and a mechanism for opening and closing the door (methods). This is the class (or type). To actually get a readout on the number of rooms and start opening and closing the door we need to build the home, end of; this is the instance.

There are a few extra comments in the Home class that discuss ‘readonly’ variables, ‘getter only’ properties (which ties in encapsulation) and the constructor; I’ll leave you to peruse them as I’ve covered the meat of classes and instances at this point.

Sideline question…how does inheritance come into this

Just before my poor Mum looked destined to snooze off at the dinner table, meaning for everyone’s sanity the subject had to be changed, we also skimmed inheritance; so I’ll give one brief example below using our ‘Home’ class from before (modified to make it simpler this time around).

Inheritance, in short, is the idea of building a ‘chain’ of related classes, by building common functionality into a ‘base’ class and then reusing/overriding this functionality in one or more sub-classes; basically, new classes can be created using an existing class as a starting point. The core concept behind classical inheritance is the ‘is a’ relationship between types; below we have a one man tent, bungalow and house; these can all be prefixed with the term ‘is a’ to establish a valid sounding relationship (a house ‘is a’ home, for example).

Firstly, although not required for inheritance, I’ve created an interface, or contract, that outlines the common functionality that any implementing class must define (and subsequently, will be tied to subclasses). This wasn’t mandatory for the example I was putting together but I’ve opted to roll with it.

namespace HomeApplication
{
    /// <summary>
    /// Public interface that defines the properties and behaviours
    /// that all homes should exhibit. The Home class will use this interface
    /// that basically states that the class will implement the described properties/methods - 
    /// This can be thought of as a contract (a promise that the facets will be found on the class).
    /// </summary>
    public interface IHome
    {
        /// <summary>
        /// Homes all have a certain number of floors, or living 'levels'.
        /// </summary>
        int NumberOfFloors { get; }

        /// <summary>
        /// Homes all have a certain number of rooms.
        /// </summary>
        int NumberOfRooms { get; }

        /// <summary>
        /// Homes all have a way to tell if the door is open or closed.
        /// </summary>
        bool IsDoorOpen { get; }

        /// <summary>
        /// Homes (for my example) are expected to have a way to open the door.
        /// </summary>
        void OpenDoor();

        /// <summary>
        /// Homes (for my example) are expected to have a way to close the door.
        /// </summary>
        void CloseDoor();

        /// <summary>
        /// Homes (for my example) are expected to have a way to turn on the heating.
        /// </summary>
        void TurnOnHeating();
    }
}

Using our IHome interface, the Home class outlines common functionality and properties to be shared by all subclasses; we are ultimately just using, as stated before, this class as a starting point to create other classes.

This class has been listed as abstract (which is not a requirement for implementing inheritance), which means that a ‘Home’ is an abstract concept only and I want to disallow users from creating an instance of this type; only instances of subclasses should be created. In as short a description as possible, virtual members provide a default implementation but can be optionally overridden by subclasses, abstract members, however, require subclasses to provide the full implementation (we are simply stating, in this case, that subclasses should implement a particular flavour of functionality). Other than that, I’ve described other pertinent details in the comments within the class definition itself.

using System;

namespace HomeApplication
{
    /// <summary>
    /// The blueprint, in our application, for a place
    /// to live. This is 'abstract', meaning no one can create
    /// a home as an instance to use directly, they can only create
    /// sub-classes of 'Home' for use in an application.
    /// </summary>
    public abstract class Home : IHome      // IHome defines a contract that 'Home' has to conform to (and therefore, that all sub-classes will be locked in to)
    {
        #region Public Properties

        /// <summary>
        /// Allow an object user to read the number of floors
        /// in this home (this value can only be set privately
        /// within this class, not from another class or sub-class directly).
        /// </summary>
        public int NumberOfFloors { get; private set; }

        /// <summary>
        /// Allow an object user to read the number of rooms
        /// in this home (this value can only be set privately
        /// within this class, not from another class or sub-class directly).
        /// </summary>
        public int NumberOfRooms { get; private set; }

        #endregion Public Properties

        #region Public Virtual Properties

        /// <summary>
        /// Allow an object user to obtain a value that represents if
        /// the door is open or closed. This is virtual as I want to allow
        /// derived types to optionally override how this is determined.
        /// </summary>
        public virtual bool IsDoorOpen { get; private set; }

        #endregion Public Virtual Properties

        #region Constructor

        /// <summary>
        /// When an 'instance' of a home is created we expect
        /// to be provided with the number of floors and rooms
        /// available within the home.
        /// </summary>
        /// <param name="floors">The default number of floors on offer.</param>
        /// <param name="rooms">The default number of rooms on offer.</param>
        public Home(int floors, int rooms)
        {
            // Store the provided values in the appropriate properties
            NumberOfFloors = floors;
            NumberOfRooms = rooms;
        }

        #endregion Constructor

        #region Protected Methods

        /// <summary>
        /// Protected members or only accessible from within this type and from direct
        /// descendant types, not from an external class. I want sub-types to possibly alter
        /// how many rooms (by adding a room) can be found in the home.
        /// </summary>
        /// <param name="numberOfRooms">The number of rooms to add.</param>
        protected void AddExtraRooms(int numberOfRooms)
        {
            NumberOfRooms += numberOfRooms;
        }

        #endregion Protected Methods

        #region Public Virtual Methods

        /// <summary>
        /// Public virtual method that closes a home's door - this represents
        /// the 'default' implementation only. This is virtual as I want derived 
        /// classes to be able to optionally override how this process 
        /// happens (see the OneManTent, for example).
        /// </summary>
        public virtual void CloseDoor()
        {
            // Closes the door to the house (enhanced to fully use auto properties)
            IsDoorOpen = false;

            // Perhaps other things happen a result of this...
            Console.WriteLine("The door on this home has been closed.");
        }


        /// <summary>
        /// Public virtual method that opens a home's door - this represents
        /// the 'default' implementation only. This is virtual as I want derived 
        /// classes to be able to optionally override how this process 
        /// happens (see the OneManTent, for example).
        /// </summary>
        public virtual void OpenDoor()
        {
            // Opens the door to the house (enhanced to fully use auto properties)
            IsDoorOpen = true;

            // Perhaps other things happen as a result of this...
            Console.WriteLine("The door on this home has been opened.");
        }

        #endregion Public Virtual Methods

        #region Public Abstract Methods

        /// <summary>
        /// Final method...this is abstract as we are enforcing a situation whereby derived
        /// types of 'Home' have to implement this themselves (every home's method of heating will
        /// vary in my test setup) - there is no default implementation.
        /// </summary>
        public abstract void TurnOnHeating();

        #endregion Public Abstract Methods
    }
}

Our other classes are utilising inheritance directly, using the Home class as a ‘template’ and using ‘overrides’ where applicable to provide their own spin on functionality, as required.

For example, all types support opening and closing of the door; however, tents override this functionality to take the ‘zip getting stuck’ into account. Houses allow for extensions to be built, which ultimately means that further rooms get added to the home. Further in line comments are there for more in-depth explanations as to what is going on.

using System;

namespace HomeApplication
{
    /// <summary>
    /// Blueprint that defines what a house looks like
    /// (this 'is a' home in my example).
    /// </summary>
    public class House : Home       // A house 'is a' home, but has some differences, which this class outlines
    {
        #region Constructor

        /// <summary>
        /// The constructor for a house consumes values that represent
        /// the number of floors and rooms that are available - these are passed
        /// directly to the Home base classes constructor.
        /// </summary>
        /// <param name="floors">The default number of floors on offer.</param>
        /// <param name="rooms">The default number of rooms on offer.</param>
        public House(int floors, int rooms) 
            : base(floors, rooms)
        {

        }

        #endregion Constructor

        #region Public Methods

        /// <summary>
        /// This method is house specific, in my example (could apply to a bungalow, of course, but
        /// I've opted to not allow this for now). A house can have an extension added by calling the protected
        /// (only accessible from the Home class or derived types, like this 'House') AddExtraRooms method. The room
        /// count for this House will therefore be increased by one.
        /// </summary>
        public void AddAnExtension()
        {
            Console.WriteLine("Adding an extension to the house (+1 rooms).");
            AddExtraRooms(1);
        }

        #endregion Public Methods

        #region Public Overridden Methods

        /// <summary>
        /// This represents what happens when the heating is turned on in a house 
        /// (remember, this was marked as abstract on the base class so this class
        /// has no choice but to offer up some kind of implementation). Super toasty
        /// central heating is on offer here!
        /// </summary>
        public override void TurnOnHeating()
        {
            Console.WriteLine("Turning on the central heating in the house.");
        }

        #endregion Public Overriden Methods
    }
}
using System;

namespace HomeApplication
{
    /// <summary>
    /// Blueprint that defines what a bungalow looks like
    /// (this 'is a' home in my example).
    /// </summary>
    public class Bungalow : Home        // A bungalow 'is a' home, but has some differences, which this class outlines
    {
        #region Constructor

        /// <summary>
        /// The constructor for a bungalow consumes a value that represent
        /// the number of rooms that are available - this is passed
        /// directly to the Home base classes constructor. Notice that we are internally
        /// setting the amount of floors to 1 (illustration only, to show how a derived type
        /// can take control of it's own state).
        /// </summary>
        /// <param name="rooms">The default number of rooms on offer.</param>
        public Bungalow(int rooms) 
            : base(1, rooms)            // Bungalows - we only allow a single floor in our example
        {

        }

        #endregion Constructor

        #region Public Overridden Methods

        /// <summary>
        /// This represents what happens when the heating is turned on in a bungalow 
        /// (remember, this was marked as abstract on the base class so this class
        /// has no choice but to offer up some kind of implementation). A Coal fire
        /// have been selected as the weapon of choice in this case.
        /// </summary>
        public override void TurnOnHeating()
        {
            Console.WriteLine("Lighting up the coal fire in the bungalow.");
        }

        #endregion Public Overriden Methods
    }
}
using System;

namespace HomeApplication
{
    /// <summary>
    /// Blueprint that defines what a one man tent looks like
    /// (this 'is a' home in my example).
    /// </summary>
    public class OneManTent : Home      // A one man tent 'is a' home, but has some differences, which this class outlines
    {
        #region Public Properties

        /// <summary>
        /// The door for a tent has an added element to worry about...the bloody zip!
        /// If the zip is broken the door (in my example) is classed as stuck open, might not
        /// be true to reality but serves as illustrative only.
        /// </summary>
        public bool IsZipBroken { get; set; }

        #endregion Public Properties

        #region Public Overridden Properties

        /// <summary>
        /// Overriden functionality from the 'Home' base class. If the zip is broken
        /// the door is classed as open. If the zip isn't broken we simply read if the door
        /// is open or closed from the base class.
        /// </summary>
        public override bool IsDoorOpen
        {
            get
            {
                return IsZipBroken ? true : base.IsDoorOpen;
            }
        }

        #endregion Public Overridden Properties

        #region Constructor

        /// <summary>
        /// The constructor for a one man tent consumes a value that represent
        /// the number of rooms that are available - this is passed
        /// directly to the Home base classes constructor. Notice that we are internally
        /// setting the amount of floors to 1 (illustration only, to show how a derived type
        /// can take control of it's own state).
        /// </summary>
        /// <param name="rooms">The default number of rooms on offer.</param>
        public OneManTent(int rooms) 
            : base(1, rooms)                // Tents - we only allow a single floor in our example
        {

        }

        #endregion Constructor

        #region Public Overridden Methods

        /// <summary>
        /// A tent overrides how a the door is opened. If the zip is broken the tent
        /// door is stuck open. Otherwise, the door opens as normal (via functionality
        /// found on the 'base' class).
        /// </summary>
        public override void OpenDoor()
        {
            if (!IsZipBroken)
            {
                // Zip is not stuck, open the door as normal
                base.OpenDoor();
            }
            else
            {
                // The zip is stuck!!!
                Console.WriteLine("The zip is broken so the tent door is stuck open");
            }
        }

        /// <summary>
        /// A tent overrides how a the door is closed. If the zip is broken the tent
        /// door is stuck open. Otherwise, the door opens as normal (via functionality
        /// found on the 'base' class).
        /// </summary>
        public override void CloseDoor()
        {
            if (!IsZipBroken)
            {
                // Zip is not stuck, close the door as normal
                base.CloseDoor();
            }
            else
            {
                // The zip is stuck!!!
                Console.WriteLine("The zip is broken so the tent door is stuck open");
            }
        }

        /// <summary>
        /// This represents what happens when the heating is turned on in a one man
        /// tent (remember, this was marked as abstract on the base class so this class
        /// has no choice but to offer up some kind of implementation). Hot water bottles
        /// are the only choice here!
        /// </summary>
        public override void TurnOnHeating()
        {
            Console.WriteLine("Urm...using the hotwater bottle for extra heat!");
        }

        #endregion Public Overriden Methods
    }
}
/// <summary>
/// Further fun and games with homes!
/// </summary>
private static void PlayWithHomes()
{
	// A House, Bungalow and OneManTent are 'Homes', therefore share some of the blueprint information (as they are derived classes). Let's use them, and explore the differences

	// Configure instances, with floor and room numbers, as available to us
	House myHouse = new House(2, 8);
	Bungalow myBungalow = new Bungalow(7);
	OneManTent myTent = new OneManTent(2);

	// 1) The House...
	Console.WriteLine("Details about myHouse..." + Environment.NewLine);
	Console.WriteLine($"The house has { myHouse.NumberOfRooms } rooms.");
	Console.WriteLine($"The house has { myHouse.NumberOfFloors } floors.");
	Console.WriteLine($"The house door is { (myHouse.IsDoorOpen ? "open" : "closed") }.");

	// Open the door and check the door state
	myHouse.OpenDoor();
	Console.WriteLine($"The house door is { (myHouse.IsDoorOpen ? "open" : "closed") }.");

	// Turn on the heating in the house
	myHouse.TurnOnHeating();

	// Add an extension (house specific)
	myHouse.AddAnExtension();
	Console.WriteLine($"The house has { myHouse.NumberOfRooms } rooms (after adding an extension)." + Environment.NewLine);

	// ---------------------------------------------------------------------------------------------------

	// 2) The Bungalow...
	Console.WriteLine("Details about myBungalow..." + Environment.NewLine);
	Console.WriteLine($"The bungalow has { myBungalow.NumberOfRooms } rooms.");
	Console.WriteLine($"The bungalow has { myBungalow.NumberOfFloors } floor.");
	Console.WriteLine($"The bungalow door is { (myBungalow.IsDoorOpen ? "open" : "closed") }.");

	// Open the door and check the door state
	myBungalow.OpenDoor();
	Console.WriteLine($"The bungalow door is { (myBungalow.IsDoorOpen ? "open" : "closed") }.");

	// And close it this time, for good measure
	myBungalow.CloseDoor();
	Console.WriteLine($"The bungalow door is { (myBungalow.IsDoorOpen ? "open" : "closed") }.");

	// Turn on the heating in the bungalow
	myBungalow.TurnOnHeating();

	Console.WriteLine();

	// ---------------------------------------------------------------------------------------------------

	// 3) The One Man Tent...
	Console.WriteLine("Details about myTent..." + Environment.NewLine);
	Console.WriteLine($"The tent has { myTent.NumberOfRooms } rooms.");
	Console.WriteLine($"The tent has { myTent.NumberOfFloors } floor.");
	Console.WriteLine($"The tent door is { (myTent.IsDoorOpen ? "open" : "closed") }.");

	// Let's break the zip!
	myTent.IsZipBroken = true;

	// Open the door and check the door state (it should be stuck open)
	myTent.OpenDoor();
	Console.WriteLine($"The tent door is { (myTent.IsDoorOpen ? "open" : "closed") }.");

	// And close it this time, for good measure
	myTent.CloseDoor();
	Console.WriteLine($"The tent door is { (myTent.IsDoorOpen ? "open" : "closed") }.");

	// Fix the zip and try to re-open and close the door
	myTent.IsZipBroken = false;

	myTent.OpenDoor();
	Console.WriteLine($"The tent door is { (myTent.IsDoorOpen ? "open" : "closed") }.");

	myTent.CloseDoor();
	Console.WriteLine($"The tent door is { (myTent.IsDoorOpen ? "open" : "closed") }.");

	// Turn on the heating in the tent
	myTent.TurnOnHeating();

	// Stop the application so we can read the output
	Console.Read();
}

Finally, the following diagram shows that tents, bungalows and houses ‘are’ homes; they share the common facets of a home whilst providing their own functionality and overridden logic, that’s essentially it!

Home class diagram.

Home class diagram.

Home instances output.

Home instances output.

I’ll do a more in depth OOP principle post in the future so watch this space.

Happy Easter!!!

T-SQL Grouping Set Showcase

Greetings all,

In the interim, whilst I juggle my time to get more in-depth posts rolled out (like the one surrounding Alexa skills), I back-tracked once again to look at overlooked features from the distant past of SQL 2008. I’ve chosen the topic of GROUPING SETS as the basis of this post; which basically boils down to a really nice way to return multiple levels of aggregation in a single result set.

A quick run through then – the following example illustrates a sample database with four tables; Staff, Perk, HolidayRequest and SalaryPayment. The HolidayRequest table has a computed column (based on ‘DateFrom’ and ‘DateTo’) that specifies how many days holiday have been requested (per request) and the SalaryPayment table has an ‘Amount’ column (salary per month). These represent our key aggregation fields. I haven’t actually referenced all columns in the samples provided; I just wanted to give the examples a little bit of extra padding (so they feel more complete).

The following T-SQL shows aggregations targeting total holiday days requested grouped by staff name, department and department and job title (see the GetHolidayRequestDaysSumInfo stored procedure in the full listing for further details):

-- Holiday request days (summed), grouped by staff name (two daves are cobbled together here)
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY s.FirstName + ' ' + s.LastName;

-- Holiday request days (summed), grouped by department
SELECT 
s.Department
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY Department;

-- Holiday request days (summed), grouped by department and job title
SELECT
s.Department
, s.JobTitle
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY s.Department, s.JobTitle;

The results look like this:

Holiday Request Group Queries.

Holiday Request Group Queries.

With traditional GROUP BY statements you need to split aggregations, so you can only perform one per result set. This is where GROUPING SETS come in handy; here is a single statement that returns the results from the statements above, but in a single result set (again, see the GetHolidayRequestDaysSumInfoUsingGroupingSets for more information, within the full listing):

-- Holiday request days (summed), group sets included for staff name, department and department and job title
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, s.Department
, s.JobTitle
, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
FROM dbo.Staff s
	INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
GROUP BY GROUPING SETS
(
	(s.FirstName + ' ' + s.LastName)
	, (s.Department)
	, (s.Department, s.JobTitle)
);

Again, here are the super fabulous results from this particular query:

Holiday Request Grouping Set Queries.

Holiday Request Grouping Set Queries.

Pretty neat! Just to nail the point home here are a few extra examples focusing on the summing of salary payments, grouped by various facets (individual statements, for starters, as shown in the GetSalarySumInfo procedure):

-- Salary sum info grouped by staff name (two daves are, of course, grouped by this)
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.FirstName + ' ' + s.LastName;

-- Salary sum info grouped by job title
SELECT 
s.JobTitle
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.JobTitle;

-- Salary sum info grouped by department
SELECT 
s.Department
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.Department

-- Salary sum info grouped by perk name
SELECT 
p.PerkName
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.Perk p ON s.Perk = p.Id
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY p.PerkName;

-- Salary sum info grouped by department and job title
SELECT
s.Department
, s.JobTitle
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY s.Department, s.JobTitle;

Results from our individual statements look a little like this:

Salary Group Queries.

Salary Group Queries.

Lastly, here’s the equivalent, single, query using GROUPING SETS (see GetSalarySumInfoUsingGroupingSets in the full listing):

-- Salary sum info grouped by staff name, department, job title, perk name and department/job title, in a single result set
SELECT 
s.FirstName + ' ' + s.LastName AS [StaffName]
, s.Department
, s.JobTitle
, p.PerkName
, SUM(sp.Amount) AS [TotalPay]
FROM dbo.Staff s
	INNER JOIN dbo.Perk p ON s.Perk = p.Id
	INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
GROUP BY GROUPING SETS
(
	(s.FirstName + ' ' + s.LastName)
	, (s.JobTitle)
	, (s.Department)
	, (p.PerkName)
	, (s.Department, s.JobTitle)
);
Salary Grouping Set Queries.

Salary Grouping Set Queries.

A great little language feature that I have largely overlooked in the past. I do have one or two reservations over performance, but a first inspection of the query plan for the ‘salary’ based example suggests that some marginal gains are on the table, using the stock setup I have (note the table spool, also):

Grouping Sets Performance.

Grouping Sets Performance.

Feels to me like something that deserves further investigation.

Here is the complete listing, if you wish to create the tables with the sample data referenced in this post (procedures, and all, bundled in). Thanks for tuning in!

/* 

	Lewis Grint - 18/03/2017

	GROUPING SETS WORKING SAMPLE 
	-------------------------------------------------
	1) This example uses four mock database tables (created internally by the script, no work required :op). Run this on a test database of your choice.
		- Staff.
		- Perk.
		- HolidayRequest.
		- SalaryPayment.
	2) Utility, mock data is also inserted by this script (table drop statements, if required, can be found just below this header).
	3) The sample data is then aggregated using GROUP BY via two traditionally structured stored procedures (GetHolidayRequestDaysSumInfo and GetSalarySumInfo). Data is returned (for each grouping) using a separate result set.
	4) Stored procedure variants are then presented that using GROUPING SETS - All GROUP BY variations are returned in a single result set (for illustration purposes only, not saying my grouping selections are actually that helpful in the real world!).

*/

/*
-- Drop table statements, as and when required
DROP TABLE dbo.HolidayRequest;
DROP TABLE dbo.SalaryPayment;
DROP TABLE dbo.Staff;
DROP TABLE dbo.Perk;

*/

-- Habitual setting of preferred QUOTED_IDENTIFIER/ANSI_NULL configuration (even if it's not important for this example)
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

-- Utility variables for gathering up the correct StaffId, when required (and payment dates)
DECLARE
@StaffId							INT
, @FebPayDate						DATE = '2017-02-28'
, @JanPayDate						DATE = '2017-01-31'
, @DecPayDate						DATE = '2016-12-31';

-- 1) Create sample tables to illustrate how GROUPING SETS work

-- Perk table added for nothing more than extra kicks. A staff member can select a company perk (referenced from the Staff table)
IF (OBJECT_ID('dbo.Perk', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.Perk
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, PerkName					NVARCHAR(30)									NOT NULL
		, INDEX IX_Perk_PerkName	NONCLUSTERED (PerkName)					
	);

END;

-- NOTE: I won't be including more indexes from this point forward, to try and keep the example light(er)

-- Staff table, which for the sake of our example contains first name, last name, job title, department and a fk to 'perks'
IF (OBJECT_ID('dbo.Staff', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.Staff
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, FirstName					NVARCHAR(50)									NOT NULL
		, LastName					NVARCHAR(50)									NOT NULL
		, JobTitle					NVARCHAR(50)									NOT NULL
		, Department				NVARCHAR(50)									NOT NULL
		, Perk						INT												NOT NULL
		, CONSTRAINT FK_Staff_Perk	FOREIGN KEY (Perk) REFERENCES dbo.Perk (Id)
	);

END;

-- SalaryPayment table, which contains multiple rows for each staff member (one for each salary payment)
IF (OBJECT_ID('dbo.SalaryPayment', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.SalaryPayment
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, StaffId					INT												NOT NULL
		, PaymentDate				DATE											NOT NULL
		, Amount					MONEY
		, CONSTRAINT FK_SalaryPayment_Staff			FOREIGN KEY (StaffId) REFERENCES dbo.Staff (Id)
	);

END;

-- HolidayRequest table, which contains multiple rows for each staff member (one for each holiday request)
IF (OBJECT_ID('dbo.HolidayRequest', 'U') IS NULL)
BEGIN

	CREATE TABLE dbo.HolidayRequest
	(
		Id							INT				PRIMARY KEY		IDENTITY(1, 1)	NOT NULL
		, StaffId					INT												NOT NULL
		, DateFrom					DATE											NOT NULL
		, DateTo					DATE											NOT NULL
		-- Final column is computed based on the difference between the start and end date (requested), in days. Simple, but fits for the purposes of the example (obviously, doesn't take into account weekends, bank holiday, etc.)
		, NumberOfDaysRequested		AS (DATEDIFF(DAY, DateFrom, DateTo) + 1)
		, CONSTRAINT FK_HolidayRequest_Staff		FOREIGN KEY (StaffId) REFERENCES dbo.Staff (Id)
	);

END;

--2) Insert test data into each table

-- Perk table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.Perk p
)
BEGIN

	INSERT INTO dbo.Perk
	(
		PerkName
	)
	VALUES 
	(
		'Free ice cream'
	)
	,
	(
		'Free parking'
	)
	,
	(
		'Beer on arrival'
	)
	,
	(
		'Pat on the back'
	);

END;

-- Staff table test data (including perk info)
IF NOT EXISTS
(
	SELECT *
	FROM dbo.Staff s
)
BEGIN

	INSERT INTO dbo.Staff
	(
		FirstName
		, LastName
		, JobTitle
		, Department
		, Perk	
	)
	VALUES
	(
		'Steve'
		, 'Stevenson'
		, 'Head Honcho'
		, 'Ivory Tower'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free ice cream'
		)
	)
	,
	(
		'Marie'
		, 'Pritchard'
		, 'Team Manager'
		, 'Ivory Tower'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free parking'
		)
	)
	,
	(
		'Judy'
		, 'Dench'
		, 'Team Manager'
		, 'Island Retreat'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Free ice cream'
		)
	)
	,
	(
		'Dave'
		, 'Dodger'
		, 'Chief Work Dodger'
		, 'Store Cupboard'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Beer on arrival'
		)
	)
	-- There's another Dave Dodger at the company, but he is actually a pretty useful chap
	,
	(
		'Dave'
		, 'Dodger'
		, 'Hard Worker'
		, 'Store Cupboard'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	)
	,
	(
		'Bob'
		, 'Boots'
		, 'Handle Cranker'
		, 'Main Office'
		,
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	)
	,
	(
		'Janet'
		, 'Timms'
		, 'Handle Cranker'
		, 'Main Office'
		, 
		(
			SELECT 
			Id 
			FROM dbo.Perk p 
			WHERE p.PerkName = 'Pat on the back'
		)
	);

END;

-- SalaryPayment table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.SalaryPayment sp
)
BEGIN
	
	-- Steve Stevenson | Head Honcho
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Steve'
		AND s.LastName = 'Stevenson'
		AND s.JobTitle = 'Head Honcho'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 5580.50
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 5240.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 5580.50
	);

	-- Marie Pritchard | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Marie'
		AND s.LastName = 'Pritchard'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2500.75
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2425.15
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2425.15
	);

	-- Judy Dench | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Judy'
		AND s.LastName = 'Dench'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2495.75
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2400.15
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2400.15
	);

	-- Dave Dodger | Chief Work Dodger
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Chief Work Dodger'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2122.90
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2105.20
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2105.20
	);

	-- Dave Dodger | Hard Worker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Hard Worker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2115.50
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2100.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2100.50
	);

	-- Bob Boots | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Bob'
		AND s.LastName = 'Boots'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2100.00
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2039.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2039.50
	);

	-- Janet Timms | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Janet'
		AND s.LastName = 'Timms'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.SalaryPayment
	(
		StaffId
		, PaymentDate
		, Amount
	)
	VALUES
	(
		@StaffId
		, @DecPayDate
		, 2100.00
	)
	,
	(
	
		@StaffId
		, @JanPayDate
		, 2039.50
	)
	,
	(
	
		@StaffId
		, @FebPayDate
		, 2039.50
	);
	
END;

-- HolidayRequest table test data
IF NOT EXISTS
(
	SELECT *
	FROM dbo.HolidayRequest hr
)
BEGIN

	-- For ease, everyone has three holiday requests
	
	-- Steve Stevenson | Head Honcho
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Steve'
		AND s.LastName = 'Stevenson'
		AND s.JobTitle = 'Head Honcho'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-01-30'
		, '2017-02-03'
	)
	,
	(
		@StaffId
		, '2017-05-22'
		, '2017-05-26'
	)
	,
	(
		@StaffId
		, '2017-07-24'
		, '2017-07-28'
	);

	-- Marie Pritchard | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Marie'
		AND s.LastName = 'Pritchard'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-23'
		, '2017-03-24'
	)
	,
	(
		@StaffId
		, '2017-06-12'
		, '2017-06-16'
	)
	,
	(
		@StaffId
		, '2017-08-14'
		, '2017-08-14'
	);

	-- Judy Dench | Team Manager
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Judy'
		AND s.LastName = 'Dench'
		AND s.JobTitle = 'Team Manager'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-24'
		, '2017-03-25'
	)
	,
	(
		@StaffId
		, '2017-06-12'
		, '2017-06-15'
	)
	,
	(
		@StaffId
		, '2017-08-15'
		, '2017-08-16'
	);

	-- Dave Dodger | Chief Work Dodger
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Chief Work Dodger'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-20'
		, '2017-03-24'
	)
	,
	(
		@StaffId
		, '2017-03-27'
		, '2017-03-31'
	)
	,
	(
		@StaffId
		, '2017-04-03'
		, '2017-04-07'
	);

	-- Dave Dodger | Hard Worker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Dave'
		AND s.LastName = 'Dodger'
		AND s.JobTitle = 'Hard Worker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-03-31'
		, '2017-03-31'
	)
	,
	(
		@StaffId
		, '2017-07-31'
		, '2017-07-31'
	)
	,
	(
		@StaffId
		, '2017-09-25'
		, '2017-09-25'
	);

	-- Bob Boots | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Bob'
		AND s.LastName = 'Boots'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-05-15'
		, '2017-05-19'
	)
	,
	(
		@StaffId
		, '2017-07-10'
		, '2017-07-12'
	)
	,
	(
		@StaffId
		, '2017-10-11'
		, '2017-10-13'
	);

	-- Janet Timms | Handle Cranker
	SELECT
	@StaffId = 
	s.Id
	FROM dbo.Staff s
	WHERE 
	(
		s.FirstName = 'Janet'
		AND s.LastName = 'Timms'
		AND s.JobTitle = 'Handle Cranker'
	);

	INSERT INTO dbo.HolidayRequest
	(
		StaffId
		, DateFrom
		, DateTo
	)
	VALUES
	(
		@StaffId
		, '2017-04-10'
		, '2017-04-11'
	)
	,
	(
		@StaffId
		, '2017-06-21'
		, '2017-06-23'
	)
	,
	(
		@StaffId
		, '2017-11-13'
		, '2017-11-17'
	);

END;
GO

-- Create utility stored procedures, just because :op

-- Basic example using the HolidayRequest table
IF (OBJECT_ID('dbo.GetHolidayRequestDaysSumInfo', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetHolidayRequestDaysSumInfo;

END;
GO

CREATE PROCEDURE dbo.GetHolidayRequestDaysSumInfo
AS
BEGIN

	-- Holiday request days (summed), grouped by staff name (two daves are cobbled together here)
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY s.FirstName + ' ' + s.LastName;

	-- Holiday request days (summed), grouped by department
	SELECT 
	s.Department
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY Department;

	-- Holiday request days (summed), grouped by department and job title
	SELECT
	s.Department
	, s.JobTitle
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY s.Department, s.JobTitle;

END;
GO

IF (OBJECT_ID('dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets;

END;
GO

CREATE PROCEDURE dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets
AS
BEGIN

	-- Holiday request days (summed), group sets included for staff name, department and department and job title
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, s.Department
	, s.JobTitle
	, SUM(hr.NumberOfDaysRequested) AS [TotalDaysRequested]
	FROM dbo.Staff s
		INNER JOIN dbo.HolidayRequest hr ON s.Id = hr.StaffId
	GROUP BY GROUPING SETS
	(
		(s.FirstName + ' ' + s.LastName)
		, (s.Department)
		, (s.Department, s.JobTitle)
	);

END;
GO

-- A couple more stored prodecures, focusing on the SalaryPayment table this time
IF (OBJECT_ID('dbo.GetSalarySumInfo', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetSalarySumInfo;

END;
GO

CREATE PROCEDURE dbo.GetSalarySumInfo
AS
BEGIN

	-- Salary sum info grouped by staff name (two daves are, of course, grouped by this)
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.FirstName + ' ' + s.LastName;

	-- Salary sum info grouped by job title
	SELECT 
	s.JobTitle
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.JobTitle;

	-- Salary sum info grouped by department
	SELECT 
	s.Department
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.Department

	-- Salary sum info grouped by perk name
	SELECT 
	p.PerkName
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.Perk p ON s.Perk = p.Id
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY p.PerkName;

	-- Salary sum info grouped by department and job title
	SELECT
	s.Department
	, s.JobTitle
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY s.Department, s.JobTitle;

END;
GO

IF (OBJECT_ID('dbo.GetSalarySumInfoUsingGroupingSets', 'P') IS NOT NULL)
BEGIN

	DROP PROCEDURE dbo.GetSalarySumInfoUsingGroupingSets;

END;
GO

CREATE PROCEDURE dbo.GetSalarySumInfoUsingGroupingSets
AS
BEGIN

	-- Salary sum info grouped by staff name, department, job title, perk name and department/job title, in a single result set
	SELECT 
	s.FirstName + ' ' + s.LastName AS [StaffName]
	, s.Department
	, s.JobTitle
	, p.PerkName
	, SUM(sp.Amount) AS [TotalPay]
	FROM dbo.Staff s
		INNER JOIN dbo.Perk p ON s.Perk = p.Id
		INNER JOIN dbo.SalaryPayment sp ON s.Id = sp.StaffId
	GROUP BY GROUPING SETS
	(
		(s.FirstName + ' ' + s.LastName)
		, (s.JobTitle)
		, (s.Department)
		, (p.PerkName)
		, (s.Department, s.JobTitle)
	);

END;
GO

-- Execute stored procedures to inspect the results

-- Holiday aggregation results (grouping sets stored procedure variant returns all data in a single result set)
EXEC dbo.GetHolidayRequestDaysSumInfo;
EXEC dbo.GetHolidayRequestDaysSumInfoUsingGroupingSets;

-- Salary aggregation results (grouping sets stored procedure variant returns all data in a single result set)
EXEC dbo.GetSalarySumInfo;
EXEC dbo.GetSalarySumInfoUsingGroupingSets;

OpenCover UI – Unit Test Code Coverage

A little sideline post to tide everyone over (as I’m still working on the Alexa piece, which I want to do proper justice to when it’s released). I’ve been messing around with a few rough and ready projects and wanted to get an idea of how to dig into code coverage, in respect of Unit Tests.

I’m currently using Visual Studio 2015 Community Edition and from what I gather no built-in support exists for non-enterprise editions, at the moment. The first hit I found was for the OpenCover UI extension; so I thought I’d take it for a spin to see what it’s made of:

Stack Overflow OpenCover UI Mention

Just so that you can get a feel for where I am at, here is an image outlining a home-brew project that shows some Unit Tests in play:

Unit Test Structure.

Unit Test Structure.

Nothing too miraculous here, I’m just using the standard Unit Testing framework and a little Moq for kicks. To follow this up, I then grabbed hold of the OpenCover UI (.vsix extension) from here and installed it:

OpenCover UI VS Marketplace Link

Let’s roll on from here with some ‘off the cuff’ observations, rather than in-depth review of features, etc. This serves as simply my first impressions and, ultimately, an insight into if we can get the coverage metrics I am after quickly and easily. For starters, you’ll notice a new context menu for ‘OpenCover’ when Visual Studio boots up:

OpenCover Menu.

OpenCover Menu.

I have quickly shoved the inbuilt Test Explorer window next to the OpenCover variant; they appear to offer a similar ‘look and feel’, in addition to functional grouping options (the default Test Explorer windows appears to have a few more options, in fact). The OpenCover Test Explorer oddly doesn’t have a ‘Run’ or ‘Run All’ tests buttons, on the face of it anyway (or debugging options). Right clicking a test gives a ‘Cover with OpenCover’ context menu option…guess I’ll see what that does now!

Cover with OpenCover Context Menu Option.

Cover with OpenCover Context Menu Option.

At this point I hit the following, immediate, explosion:

Open Cover EXE Error.

OpenCover EXE Error.

You then get prompted to hunt down the relevant .exe file. As I was fishing around for this I decided to go back to the trusty Stack Overflow, to see what wisdom could be uncovered. This was the first hit, which outlined that a configuration file, with set content, needed to be stuffed in with the solution content:

Further Stack Overflow Wisdom

Further comment sniffing did highlight, under Tools > Options, that additional configuration should be performed (i.e. the .exe path should be specified):

Open Cover VS Options.

Open Cover VS Options.

I decided that hunting on NuGet might be the best way to expose an .exe file here (i.e. getting something dropped into a packages directory, which I can easily pick up). So, I followed the hunch by adding this package (just to the ‘tests’ project, for starters, as I wasn’t sure which projects needed targeting):

OpenCover Nuget Package.

OpenCover Nuget Package.

I don’t feel as if we’ve fallen into a rabbit hole just yet, but at this point, I’ve started to wonder if ‘storms’ are on the horizon! Hopefully, we won’t have to tread too much further to get this machine churning. Installing the NuGet package had the desired effect, I have now got the .exe I was looking for lingering in a ‘Tools’ directory, under the OpenCover folder within packages, which I’ve setup in the Visual Studio Options section:

OpenCover EXE Path Configured.

OpenCover EXE Path Configured.

This shouldn’t be marked down as ‘ideal’ configuration, of course; we’re more leaning towards a ‘just get it working’ stance.

The moment of truth…..right clicking and selecting ‘Cover with OpenCover’ now….success! Well, good things appear to have happened anyway, let’s have a quick review to see if we can make sense of it (code with incomplete XML comments is about to be on show, so apologies about that!). I only ran the one test by the way:

Unit Test Code Coverage.

Unit Test Code Coverage.

First observation; it did seem to take a good few seconds before all of the lines covered (green dots) and not covered (red dots) seemed to be highlighted correctly, nothing too catastrophic in this, however. As far as the unit testing specific code goes, here you can clearly see which tests I ran in this instance, the UI pointers are very self-explanatory. One additional observation, it looks like it could be a touch tricky to pick out breakpoints amongst the code coverage markers, but I don’t see this as a big issue at the moment (I’ll have to see how I feel after extended use). In fact, the OpenCover Results window has an option for enabling/disabling these markers, so we’re all good.

You’ll notice that the unit test method denoted here is placing the ‘AddItemCleansingMappingElementToConfiguration’ method under test, so I am keen to see what lines we hit (or ‘covered’) within the targeted method:

Code Actually Under Test Covered.

Code Actually Under Test Covered.

The idea here is that the XML configuration passed to this method is, in fact, malformed so the statement where the ‘addSuccessful’ variable is set to true is not hit (an exception is triggered, and caught, by the preceding line of code); which mirrors the indicator provided by OpenCover, nice! I call this a success!

I’m now going to run this across the board and see what floats to the surface.

Ah, look at this! For starters, OpenCover has highlighted a problem with one of my unit tests in a very solid, visual way (I was wondering why no lines of this test were covered, until I realised I omitted the ‘Test Method’ attribute!):

Missing Test Method Attribute.

Missing Test Method Attribute.

A couple of quirks that deserve to be noted; firstly, I did have to run the ‘Cover with OpenCover’ command twice to register coverage on all tests (some seemed to be omitted from the process, but then included on the second run through). Also, tests that are geared to expect exceptions to be thrown are always marked with their closing brace as ‘not covered’ (I’m assuming that an exception being thrown legitimately causes the final line to never be hit, therefore not covered, which in my head is expected behaviour – it would be good if there was a way to disregard these instances):

Unit Test Missing Coverage.

Unit Test Missing Coverage.

So what about the actual code ‘under test’ and the metrics provided to show how much of it has been covered? In instances whereby code had been highlighted as not covered (spot checks only, of course), I have to say it appears accurate and has been useful in flagging areas I should really have tested.

As for the actual report metrics, it is exactly what I was after when I started on my way down this road. You get to see the percentage of code coverage at project, class and member level (along with ‘Sequence Points’ visited against the total count of possible points):

OpenCover Metrics Report.

OpenCover Metrics Report.

Sequence points don’t tie directly to ‘lines’, as outlined here. You’ll notice that this is a link detailing a ‘Report Generator’, which uses XML extracted using OpenCover directly. To finish up, I’ll follow the steps outlined on Stack Overflow again (got to love it, especially if you need information on the double!):

Using the Report Generator

The Report Generator can be downloaded by using NuGet again:

OpenCover Report Generator on NuGet.

OpenCover Report Generator on NuGet.

The Report Generator source code itself can be downloaded from this link.

It looks like you can create a custom report via C#, by implementing an interface, etc. For now, I’m going to do a simple run through using the command line interface. This is the command (after a bit of trial and error) that got me the XML report, for starters:

"C:\Source\Utility Applications\DesktopManager\packages\OpenCover.4.6.519\tools\OpenCover.Console.exe" -register:user -target:"C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\mstest.exe" -targetargs:"/noisolation /testcontainer:\"C:\Source\Utility Applications\DesktopManager\DesktopManager.Tests\bin\Debug\DesktopManager.Tests.dll\" /resultsfile:C:\Reports\MSTest\.trx" -mergebyhash -output:C:\Reports\MSTest\projectCoverageReport.xml

This was just a case of specifying locations for the OpenCover.console.exe, the mstest.exe and the location of the ‘Tests’ dll for my specific application. XML file in hand, I trigerred this command to generate the final report resources:

"C:\Source\Utility Applications\DesktopManager\packages\ReportGenerator.2.5.2\tools\ReportGenerator.exe" -reports:"C:\Reports\MSTest\projectCoverageReport.xml" -targetdir:"C:\Reports\CodeCoverage"

In a few quick steps you’ll have a set of HTML ‘reports’, as you can see here:

HTML Reports Generated.

HTML Reports Generated.

Let’s finish up with a couple of examples illustrating the outputs:

Index Report.

Index Report.

Configuration Helper Report Details.

Configuration Helper Report Details.

Configuration Helper Report Details.

Configuration Helper Report Line Coverage.

I think that brings us to a close. This seems like pretty powerful stuff; but, I think I’ll need more time to go through some of the outputs and try this with a larger project. I hope this has been fun and/or useful.

Thanks all!