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!

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;

Groovy JavaScript Regex Name Capitalisation Handling

Greetings!

A tidbit found by a friend of mine online, forming the basis for a small piece of work I’ve done this week surrounding name capitalisation. This was pulled from a stack overflow article so credit where credit is due for starters:

js-regex-for-human-names

This is fairly robust, covering Mc, Mac, O’s and double-barrelled, hyphenated names. It does capitalise the first character directly after an apostrophe (regardless of placement) which may or may not be a problem. As for usage, I went with the following setup (with the relevant JavaScript and jQuery hooks being properly abstracted in the production code of course).

Firstly, the example HTML structure:

<div id="container">
	<!--An example form illustrating the fixNameCasing function being called on a test forename, middle names and surname field (when focus is lost)-->
	<form action="/" method="post">
		<div>
			<label id="forename-txt-label">Forename:</label>
		</div>
		<div>
			<input id="forename-text" name="forename-text" class="control-top-margin fix-name-casing" type="text" />
		</div>
		<div>
			<label id="middlename-text-label">Middle names:</label>
		</div>
		<div>
			<input id="middlename-text" name="middlename-text" class="control-top-margin fix-name-casing" type="text" />
		</div>
		<div>
			<label id="surname-text-label">Surname:</label>
		</div>
		<div>
			<input id="surname-text" name="surname-text" class="control-top-margin fix-name-casing" type="text" />
		</div>
		<div>
			<button id="submit-button" type="submit" class="control-top-margin">Submit</button>
		</div>
	</form>
</div>

Then, our jQuery/JavaScript juicy bits:

<!--Bring jQuery into scope so we can hook up a function to relevant elements on 'blur' event (lost focus)-->
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.2/jquery.min.js"></script>
<script type="text/javascript">
	
	// The name casing fix function utilising regex
	function fixNameCasing(name) {
		var replacer = function (whole, prefix, word) {
			ret = [];
			
			if (prefix) {
				ret.push(prefix.charAt(0).toUpperCase());
				ret.push(prefix.substr(1).toLowerCase());
			}
			
			ret.push(word.charAt(0).toUpperCase());
			ret.push(word.substr(1).toLowerCase());
			return ret.join('');
		}
		var pattern = /\b(ma?c)?([a-z]+)/ig;
		return name.replace(pattern, replacer);
	}
	
	// On document ready rig up of relevant controls (based upon using the 'fix-name-casing' class) 'blur' event. When focus is lost, in a given control, we take the controls input and format it based on a return value from fixNameCasing
	$(function() {
		$(".fix-name-casing").blur(function() {
			$(this).val(fixNameCasing($(this).val()));
		});
	});

</script>

The results! Each field in the following screenshot received fully lowercase or uppercase input before being tabbed out of (i.e. lost focus):

Image showing name capitalisation of three example name fields.

Name Capitalisation Test Output.

Lastly, here’s the entire code snippet:

<!DOCTYPE html>
<html>
<head>
	<title>Name Capitalisation Test</title>
	<style type="text/css">
		
		.control-top-margin {
			margin-top: 5px;
		}
	
	</style>
	<!--Bring jQuery into scope so we can hook up a function to relevant elements on 'blur' event (lost focus)-->
	<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.2/jquery.min.js"></script>
	<script type="text/javascript">
		
		// The name casing fix function utilising regex
		function fixNameCasing(name) {
			var replacer = function (whole, prefix, word) {
				ret = [];
				
				if (prefix) {
					ret.push(prefix.charAt(0).toUpperCase());
					ret.push(prefix.substr(1).toLowerCase());
				}
				
				ret.push(word.charAt(0).toUpperCase());
				ret.push(word.substr(1).toLowerCase());
				return ret.join('');
			}
			var pattern = /\b(ma?c)?([a-z]+)/ig;
			return name.replace(pattern, replacer);
		}
		
		// On document ready rig up of relevant controls (based upon using the 'fix-name-casing' class) 'blur' event. When focus is lost, in a given control, we take the controls input and format it based on a return value from fixNameCasing
		$(function() {
			$(".fix-name-casing").blur(function() {
				$(this).val(fixNameCasing($(this).val()));
			});
		});

	</script>
</head>
<body>
	<div id="container">
		<!--An example form illustrating the fixNameCasing function being called on a test forename, middle names and surname field (when focus is lost)-->
		<form action="/" method="post">
			<div>
				<label id="forename-txt-label">Forename:</label>
			</div>
			<div>
				<input id="forename-text" name="forename-text" class="control-top-margin fix-name-casing" type="text" />
			</div>
			<div>
				<label id="middlename-text-label">Middle names:</label>
			</div>
			<div>
				<input id="middlename-text" name="middlename-text" class="control-top-margin fix-name-casing" type="text" />
			</div>
			<div>
				<label id="surname-text-label">Surname:</label>
			</div>
			<div>
				<input id="surname-text" name="surname-text" class="control-top-margin fix-name-casing" type="text" />
			</div>
			<div>
				<button id="submit-button" type="submit" class="control-top-margin">Submit</button>
			</div>
		</form>
	</div>
</body>
</html>

The likelihood is that I’ll be using this just as a basis for my current requirements and adjusting as needed.

I hope this proves useful and kudos to my friend who found this and the original stackoverflow contributor. If anyone has any other examples of code that tackles this problem, that they would like to contribute, just let me know by commenting below.

Cheers!

T-SQL MERGE…Completely overlooked!

Good evening all,

Umm…how an earth did I miss this language feature? Where have I been since the release of SQL Server 2008? This really did just pass me by. I’m in the position whereby I don’t often run into this situation; where a T-SQL MERGE statement is directly applicable (i.e. table synchronisation), but still, I was a little embarrassed. Perhaps I have seen this at some point and it’s just been pushed out of my noodle at the expense of some other piece of information. So, red-faced (at least I am), let’s jump right in.

What is it?

In simple terms, this provides a compound way to perform an INSERT, UPDATE or DELETE on a target table, based on a source table. It’s as easy as that; we’ve negated the need for existence checks and other wrapping code/constructs, including the multiple INSERT, UPDATE and DELETE clauses of course. Performance is mentioned on the appropriate MSDN page. However, in my limited testing, I have not yet seen any major issues; just keep this thought on your mental radar anyway.

More information can be found here, so dive in and have a read (which details performance tips, so keep this in mind): T-SQL MERGE

MERGE Example

The following script rigs up some test data, in two example ‘target’ and ‘source’ tables, in an effort to demonstrate the synchronisations capabilities of the MERGE statement (apologies for the colour coding issues, I am using the correct language type but it seems to be taking exception to some of the content!).

/* 
	Very basic MERGE example
	-----------------------------

	Imagine these tables exist across databases, etc. and require synchronisation (or, as per my recent usage, I built up a derived table using CTE's to provide a source data table to compare against the target table to drive modifications)
	NOTE: Tables created for illustration only; not representative of how to structure this in practice of course
*/

-- TARGET TABLE
IF OBJECT_ID('dbo.TARGET_PERSON_TABLE') IS NULL
BEGIN

	CREATE TABLE dbo.TARGET_PERSON_TABLE
	(
		TARGET_PERSON_ID				INT		PRIMARY KEY
		, TARGET_FORENAME				NVARCHAR(255)
		, TARGET_SURNAME				NVARCHAR(255)
		, TARGET_AGE					INT			
	);

END;

-- SOURCE TABLE
IF OBJECT_ID('dbo.SOURCE_PERSON_TABLE') IS NULL
BEGIN

	CREATE TABLE dbo.SOURCE_PERSON_TABLE
	(
		SOURCE_PERSON_ID				INT		PRIMARY KEY
		, SOURCE_FORENAME				NVARCHAR(255)
		, SOURCE_SURNAME				NVARCHAR(255)
		, SOURCE_AGE					INT			
	);

END;

--Arrange some test data into each table (target and source, for illustration)
INSERT INTO dbo.TARGET_PERSON_TABLE
(
	TARGET_PERSON_ID
	, TARGET_FORENAME
	, TARGET_SURNAME
	, TARGET_AGE
)
VALUES
(
	1
	, 'Dave'
	, 'Jones'
	, 32
)
,
(
	2
	, 'Moira'
	, 'Stevens'
	, 27
)
,
(
	3
	, 'Larry'
	, 'Bodsworth'
	, 48
);

INSERT INTO dbo.SOURCE_PERSON_TABLE
(
	SOURCE_PERSON_ID
	, SOURCE_FORENAME
	, SOURCE_SURNAME
	, SOURCE_AGE
)
VALUES
(
	2
	, 'Mandy'
	, 'Stevens'
	, 32
)
,
(
	3
	, 'Larry'
	, 'Rodsworth'
	, 50
)
,
(
	4
	, 'Sandy'
	, 'Ennis'
	, 29
)
,
(
	5
	, 'Wendy'
	, 'Wainwright'
	, 40
);

-- Inspect the target/source table data prior to the MERGE operation
SELECT 
tpt.TARGET_PERSON_ID
, tpt.TARGET_FORENAME
, tpt.TARGET_SURNAME
, tpt.TARGET_AGE
FROM dbo.TARGET_PERSON_TABLE tpt;

SELECT
spt.SOURCE_PERSON_ID
, spt.SOURCE_FORENAME
, spt.SOURCE_SURNAME
, spt.SOURCE_AGE
FROM dbo.SOURCE_PERSON_TABLE spt;

-- Synchronise the target table with the source table, performing matching and INSERT, UPDATE and DELETE operations as required
MERGE dbo.TARGET_PERSON_TABLE tpt
USING SOURCE_PERSON_TABLE spt ON tpt.TARGET_PERSON_ID = spt.SOURCE_PERSON_ID 
	-- If a row is 'matched' (based on the above 'ON' statement) then simply update the target Person
	WHEN MATCHED 
	THEN UPDATE 
		SET 
		tpt.TARGET_FORENAME = spt.SOURCE_FORENAME
		, tpt.TARGET_SURNAME = spt.SOURCE_SURNAME
		, tpt.TARGET_AGE = spt.SOURCE_AGE
	-- If a row is 'not matched' (based on the above 'ON' statement) then do an insert of a new Person into the target table, based on the source table
	WHEN NOT MATCHED
		THEN INSERT
		(
			TARGET_PERSON_ID
			, TARGET_FORENAME
			, TARGET_SURNAME
			, TARGET_AGE
		)
		VALUES
		(
			SOURCE_PERSON_ID
			, SOURCE_FORENAME
			, SOURCE_SURNAME
			, SOURCE_AGE
		)
	-- Lastly, if a the target table contains a row not matched by the source table then remove the target table row entirely
	WHEN NOT MATCHED BY SOURCE 
		THEN DELETE;

/*
	Inspect the target/source table data post MERGE operation (values should be synchronised between correctly between the two tables)

	Expected Results
	----------------------
	1) Person ID 1, Dave Jones, should be removed from the target table (DELETE)
	2) Person ID 2, Forename of 'Moira' should be updated to 'Mandy' and Age should be updated from 27 to 32 (UPDATE)
	3) Person ID 3, Surname of 'Bodsworth' should be updated to 'Rodsworth' and Age should be updated from 48 to 50 (UPDATE)
	4) Person ID 4, Sandy Ennis, should be added to the target table (INSERT)
	5) Person ID 5, Wendy Wainwright, should be added to the target table (INSERT)

*/

SELECT 
tpt.TARGET_PERSON_ID
, tpt.TARGET_FORENAME
, tpt.TARGET_SURNAME
, tpt.TARGET_AGE
FROM dbo.TARGET_PERSON_TABLE tpt;

SELECT
spt.SOURCE_PERSON_ID
, spt.SOURCE_FORENAME
, spt.SOURCE_SURNAME
, spt.SOURCE_AGE
FROM dbo.SOURCE_PERSON_TABLE spt;

-- Clear down the tables post operation, for ease of re-running and re-testing
TRUNCATE TABLE dbo.TARGET_PERSON_TABLE;
TRUNCATE TABLE dbo.SOURCE_PERSON_TABLE;

Here is the state of the target and source tables prior to the MERGE statement being executed:

Illustrates the target and source tables state after the merge process

Target and Source Tables Post Merge.

After execution, you can see the effects of the synchronisation between the target and source tables:

Illustrates the target and source tables state before the merge process

Target and Source Tables Prior to Merging.

If this was useful to you then that’s superb…my guess is that a fair few of you will be shaking your heads wondering which hole in the sand I’ve had my head planted in for the last few years. Well, I call it how I see it and I’m pretty sure I missed the memo, so for you others out there that may have missed this, enjoy!

I’m still playing around with a F#/FitBit related piece at the moment, and I really want it to be a good quality post, so hang on in there as it’s coming soon.

Cheery bye!

A Little FOR XML PATH Nugget

A very small post this one, covering a little nugget that I’d almost forgot until it came up trumps again this week; TSQL FOR XML PATH can be a nice solution for concatenation of strings across rows (in a given column).

It’s fairly common to have the need to concatenate column based data, as the following example illustrates:

--Standard concatenation of column values (comma separated, produces multiple rows)
SELECT 
FORENAME 
+ ', ' 
+ SURNAME AS [FULL_NAME]
FROM dbo.tblTEST t
WHERE t.ID < 4;

However, don’t forget that working through and concatenating row based data, in a particular column, can be achieved simply using the FOR XML PATH construct, just like this:

--Concatenation of row values for a particular column (imagine we wanted comma separated forenames for example) - Provides a single column as structured
SELECT
CONVERT                                     --Conversion required to an NVARCHAR(VALUE) (MAX depending on string size) - The result will be XML when using FOR XML PATH initially
(
    NVARCHAR(100)
    ,
    (
        SELECT
        t.FORENAME + 
        CASE
            WHEN t.ID < 3                    --Don't add a comma after the last value (just for illustration purposes)
            THEN ', '
            ELSE ''
        END
        FROM dbo.tblTEST t
        WHERE t.ID < 4
        FOR XML PATH('')					 --Specify FOR XML PATH using an empty string (we don't want a wrapping element when concatenating strings)
    )
) AS [COMMA_SEPARATED_FORENAMES];

Interesting use of FOR XML PATH that’s well worth keeping in mind, it can come in dead handy. Apologies for the Short and sweet post; it’s the order of the day! I’ve managed to pick up the dreaded lurgy so I’m dosed up on medication and drinking a tonne of coffee! Here’s hoping that this post makes sense when I read it later on.

Until the next time, bye for now!

Chart-tastic F# Goodness

Happy New Year everyone!

I hope the holiday season was full of beer, wine, grub and every other kind of delightful merriment imaginable. The bear is back and absolutely raring to go! So, in an attempt to be a little more diligent and focused, I’ll crack on with the list of goodies I wanted to cover; starting with a small post on F# charting capabilities.

If you read my previous posts on Future Decoded 2015 and F# you’ll know that I’ve built up a little bit of a soft spot for F# (again, just to reiterate, the Don Syme Future Decoded F# Breakout Session was excellent and is a must watch). To continue on my journey a little bit I wanted to move beyond syntax, basic data aggregation/manipulation and type providers to touch on data presentation; namely the F# charting assemblies specifically.

Here’s a quick guide on how to get started in Visual Studio:

Project Setup

To get started from Visual Studio you have a couple of choices; you can create a full blown F# Library Project (for a more in depth/permanent piece of code) or, within Visual Studio 2015 at least, you can simply create an F# script to be run in F# Interactive without the overhead of a Project. As I’ll be bringing the F# Charting Library into scope, using NuGet, a project makes more sense in this instance, but here are the steps on both should you feel nosey:

Create an F# Script

Go to the File > New menu within Visual Studio 2015 and select the File option (without a Solution or Project currently open). You can then simply select the F# Script File option to get rocking and rolling:

New F# Script File.

New F# Script File.

Create an F# Project

As per any other project you can simply navigate to File > New > Project and select the Visual F# templates. You’ve got options for creating Console Applications, Portable Libraries, Unit Test Applications, etc. From what I’ve seen, the Tutorial project is actually well worth a look, this can be used in conjunction with the tryfsharp website to grease the wheels if needed! For this project, the normal F# Class Library will suffice:

New F# Library.

New F# Library.

Once your project is created you can access and begin scripting using the .fsx file included in the project; no sweat so far!

Script.fsx File.

Script.fsx File.

As an aside, the F# Interactive window (where output is logged for an F# script file) can be accessed from the View > Other Windows > F# Interactive (Ctrl + Alt + F) option.

For those who want to hoover up more background knowledge, further information surrounding writing F# within Visual Studio can be found here:

Using Visual Studio to Write F# Programs

F# Charting Assemblies

We next have to ‘NuGet it up’ and bring some extra resources into scope. This can be done using the Tools > NuGet Package Manager > Manage NuGet Packages for Solution option and then searching for FSharp.Charting, followed by clicking install (see image below), or more simply via the Package Manager Console found under Tools > NuGet Package Manager > Package Manager Console and use the command ‘Install-Package FSharp.Charting’:

Nuget Package Manager F# Charting Search.

Nuget Package Manager F# Charting Search.

Install via Package Manager Console.

Install via Package Manager Console.

Further gubbins on how to install the FSharp Charting Library on other environments (and a link to the NuGet package we’ve utilised here for reference) can be found here:

F# Charting: Library for Data Visualisation
F# Charting (NuGet)

Once you’ve installed the package using either method we’re nearly ready to get charting.

At this point, to run the examples coming up, you’ll also need to:

  • Install the FSharp.Data NuGet package (using exactly the same process as detailed above). Command: ‘Install-Package FSharp.Data’.
  • Install the FSharp.Data.TypeProviders package (again, using the same process detailed above). Command: ‘Install-Package FSharp.Data.TypeProviders’.
  • Reference System.Drawing within your F# Project (using References > Right Click > Add Reference > Assemblies > Framework).

More information here:

FSharp.Data NuGet Package
FSharp.Data.TypeProviders NuGet Package

We’re fully set and ready to go, so on to the charting!

Line Chart

The plan here is to briefly demonstrate each type of chart in action. This first example will be a basic evolutionary piece showing an expansion from a bare bones chart into a more full example utilising some more complex constructor options and styling tweaks (nothing too meaty however!).

Within your .fsx script file you’ll need to begin with the following (highlight the lines and use Alt + Enter to run the code within F# Interactive. If you’re using ReSharper be aware that this keyboard shortcut may be mapped, so you might have to do some jigging!):

//Give F# Interactive awareness of the packages folder (it will allow easier loading/references of resources below)
#I "..\packages"

//Read, compile and run the FSharpt.Charting.fsx file (to access the functionality of FSharp.Charting.dll)
#load "FSharp.Charting.0.90.13\FSharp.Charting.fsx"

//Read in the FSharp.Data and FSharp.Data.TypeProviders assemblies (so we can make use of the HtmlProvider)
#r @"FSharp.Data.2.2.5\lib\net40\FSharp.Data.dll"
#r @"FSharp.Data.TypeProviders.0.0.1\lib\net40\FSharp.Data.TypeProviders.dll"

//Access namespaces as required by the examples. System for the 'Random' type and use of String.Format, FSharp.Data for the HtmlProvider, FSharp.Charting for what is says on the tin
//and System.Drawing for chart styling elements
open System
open FSharp.Data
open FSharp.Charting
open System.Drawing

//Write code to utilise FSharp.Charting
//...

You will see dialogs matching the following thrown during this process, you’ll need to select ‘Enable’:

Type Provider Enable Dialog.

Type Provider Enable Dialog.

Now, some of these reference assemblies and opened namespaces are required for later examples; we’re just getting F# Interactive primed and ready so we don’t have to do any further work down the road. Here’s a quick rundown on the directives being used here:

  • #I: Specifies a search (hint) path so that F# Interactive can intelligently search for resources you reference.
  • #load: Reads, compiles and runs the specified resource.
  • #r: References an assembly directly.
  • open: Imports an assembly (like a C# ‘using’) ready for use in F# code.

In short, we add a search path using #I so F# Interactive can more easily find resources (in our packages directory) without the need to provide a full, absolute path. The #load directive reads, compiles and runs the FSharp.Charting.fsx file, which neatly references the FSharp.Charting assembly as well as the supporting System.Windows.Forms.DataVisualization assembly. A hop, skip and jump later, we can then reference assemblies (added by NuGet) in the packages directory in a more simple manner using #r (you could just as easily references dlls from the bin folder, I need to do some further reading on this to get a feeling for best practice). Lastly, open (begin ‘using’) namespaces linked to these resources as required, boom!

Here’s a wonderful little list detailing the ins and outs of fsi.exe for those who want to delve further.

What can we do with all of this magic…make a chart of course; starting with charting 101, the line chart (run this in F# Interactive as before by highlighting the code and using Alt + Enter):

//Define an array of resting heart rate values
let restingHeartRateValues = [69; 74; 76; 71; 68; 65; 64; 79; 80; 75; 72; 65; 63; 62; 59; 65; 80; 61; 59]

//Produce a line chart to visualise this data
Chart.Line(restingHeartRateValues)
Basic Resting Heart Rate Line Chart.

Basic Resting Heart Rate Line Chart.

That’s it, first chart down! The resting heart rate values array (I’m still getting used to semi-colon separated values!) is used as the ‘data’ input for a call to Chart.Line. The next few code snippets illustrate some alternative syntax for this operation. You can use the ‘|>’ syntax if you just wish to produce a chart with no bells and whistles. For extra levels of shininess (i.e. styling the chart) you can add additional method calls as required. Let’s go through some examples:

//Inline the array directly into the call to Chart.Line
Chart.Line([69; 74; 76; 71; 68; 65; 64; 79; 80; 75; 72; 65; 63; 62; 59; 65; 80; 61; 59])

//Or, alternatively, calling the basic Chart.Line constructor using |> syntax
[69; 74; 76; 71; 68; 65; 64; 79; 80; 75; 72; 65; 63; 62; 59; 65; 80; 61; 59]
|> Chart.Line
//Define an array of resting heart rate values
let restingHeartRateValues = [69; 74; 76; 71; 68; 65; 64; 79; 80; 75; 72; 65; 63; 62; 59; 65; 80; 61; 59]

//Produce a line chart to visualise this data (with a Name, Title and labels for the X and Y axis)
Chart.Line(restingHeartRateValues, Name = "Resting Heart Rate Chart", Title = "Resting Heart Rate by Day", XTitle = "Days", YTitle = "Heart Rate (BPM)")
Styled Resting Heart Rate Line Chart.

Styled Resting Heart Rate Line Chart.

Here, we called some additional constructor arguments of Chart.Line (by name) to name the chart (this appears in the chart window header), give the chart a title (which appears within the chart window, normally overlaying the chart), in addition to naming the chart axis labels.

//Define an array of resting heart rate values
let restingHeartRateValues = [69; 74; 76; 71; 68; 65; 64; 79; 80; 75; 72; 65; 63; 62; 59; 65; 80; 61; 59]

//Produce a line chart to visualise this data (with a Name, Title and labels for the X and Y axis)
Chart.Line(restingHeartRateValues, Name = "Resting Heart Rate Chart", Title = "Resting Heart Rate by Day", XTitle = "Days", YTitle = "Heart Rate (BPM)"
).WithYAxis(Min=55.0) //X Axis starts at 55
Styled Resting Heart Rate Line Chart (Axis Minimum Applied).

Styled Resting Heart Rate Line Chart (Axis Minimum Applied).

A slight extension to the above example, you can perform fine-tuning of each axis (you can control the label this way also) by calling WithYAxis or WithXAxis. In this case, I’m specifying a minimum value for the given axis to control how the chart is presented, all very fine and dandy. On to the next example we go.

//Define an array of resting heart rate values
let restingHeartRateValues = [69; 74; 76; 71; 68; 65; 64; 79; 80; 75; 72; 65; 63; 62; 59; 65; 80; 61; 59]

//Produce a line chart to visualise this data (with a Name, Title and labels for the X and Y axis)
Chart.Line(restingHeartRateValues, Name = "Resting Heart Rate Chart", Title = "Resting Heart Rate by Day", XTitle = "Days", YTitle = "Heart Rate (BPM)"
).WithYAxis(Min=55.0).With3D() //X Axis starts at 55 and with 3D (no extra 3D parameters specified, just as is)
Resting Heart Rate Line Chart (3D).

Resting Heart Rate Line Chart (3D).

In this final snippet I’ve added a call to With3D, because I couldn’t resist seeing what the chart looked like (what a sucker)!

Column Chart

Following up, a couple of iterations of a column chart can be found below (using a string * int list representing Kindle Charge data). This illustrates how to further style the X and Y axis definitions, as well as apply some styling to the chart in general.

//Produce some test data (representing a drop in Kindle charge by day)
let kindleChargeData = [
    "Monday", 100;
    "Tuesday", 91;
    "Wednesday", 87;
    "Thursday", 76;
    "Friday", 61;
    "Saturday", 55;
    "Sunday", 50;
]

//Produce a column chart (with some basic styling for the chart; covering colours and borders, etc.)
Chart.Column(kindleChargeData, Color=Color.Green, Name = "Kindle Charge Chart", Title = "Decrease in Kindle Charge by Day"
).WithXAxis(Title = "Day", TitleColor = Color.Purple
).WithYAxis(Title = "Charge", TitleColor = Color.Purple
).WithStyling(BorderColor = Color.DarkRed, BorderWidth = 3)
Kindle Charge by Day Column Chart (Basic).

Kindle Charge by Day Column Chart (Basic).

Same again, except for in 3D (with a limit of 100 ‘units’ placed on then Y axis):

//Produce some test data (representing a drop in Kindle charge by day)
let kindleChargeData = [
    "Monday", 100;
    "Tuesday", 91;
    "Wednesday", 87;
    "Thursday", 76;
    "Friday", 61;
    "Saturday", 55;
    "Sunday", 50;
]

//Produce a column chart (with some basic styling for the chart; covering colours and borders, etc.)
Chart.Column(kindleChargeData, Color=Color.Green, Name = "Kindle Charge Chart", Title = "Decrease in Kindle Charge by Day"
).WithXAxis(Title = "Day", TitleColor = Color.Purple
).WithYAxis(Title = "Charge", TitleColor = Color.Purple, Max = 100.00           //Apply a max here to prevent the chart from going beyond 100 on the Y axis
).WithStyling(BorderColor = Color.DarkRed, BorderWidth = 3
).With3D()                                                                      //Because I can't resist!!!
Kindle Charge by Day Column Chart (3D).

Kindle Charge by Day Column Chart (3D).

Just as a brief illustration, it is possible to combine individual charts into a unified entity using Chart.Combine as follows:

//A brief Chart.Combine example
Chart.Combine(
    [   
        Chart.Column([ 12 ], Labels = ["Amy"])
        Chart.Column([ 9 ], Labels = ["Buster"]) 
        Chart.Column([ 10 ], Labels = ["Dave"])
        Chart.Column([ 13 ], Labels = ["Shirley"])
        Chart.Column([ 7 ], Labels = ["Stephanie"])
        Chart.Column([ 11 ], Labels = ["Bob"])
    ]
    ).WithTitle(Text = "Tasks Completed"
    ).WithXAxis(Title = "No. of Tasks"
    ).WithYAxis(Max = 15.00)
Combined Column Chart.

Combined Column Chart.

Bar Chart

In this example we introduce the use of System.Random to produce a bar chart. After creating and binding ‘randomValue’ we utilise this in the construction of a for loop (which creates an integer list based on a number multiplied by a random number between a minimum and maximum limit). Drawing from previous learning, I’ve then mapped each value in this first list to an additional function (using List.map) to add another random value on to each initial integer. Lastly, we call Chart.Bar (the most basic form of the constructor) with the resultant data. Comments are inline to hopefully clarify how the code is working:

//Bind a new variable to use in RNG
let randomValue = new Random()

//Using alternative syntax this time as we are not calling a more complex Chart.Bar constructor (for styling purposes, etc.). Do some RNG and produce a bar chart
[ for i in 0..15 -> i * randomValue.Next(10, 20) ]      //For zero to fifteen, times i by a random value (between the minimum and maximum specified)
|> List.map(fun i -> i + randomValue.Next(1, 2))        //Map the result list to another function that adds another random value (between the minimum/maximum specified) to each initial value
|> Chart.Bar                                            //Produce the chart
Bar Chart Basic Example.

Bar Chart Basic Example.

Pie Chart

Moving on, we have a slightly more in depth example here using the HtmlProvider to scrape data off of a couple of Wiki pages for use in pie charts. The first example illustrates the breakdown of house types within Leigh in Surrey (I’ve never been but I’m sure it’s lovely. Leigh, here is your moment in the limelight!):

//Get a type denoting our pages structure (so we can strongly type against it), then retrieve live data by calling .Load
type WikiSurreyLeighStructure = HtmlProvider<"data_structure.html">
let liveDataTable = WikiSurreyLeighStructure.Load("https://en.wikipedia.org/wiki/Leigh,_Surrey").Tables.``Demography and housing 2``

//Prepare data scraped from the web page (details on the housing types in Leigh, Surrey)
let propertyTypeData = [
    String.Format("Detached ({0})", liveDataTable.Rows.[0].Detached), liveDataTable.Rows.[0].Detached
    String.Format("Semi-Detached ({0})", liveDataTable.Rows.[0].``Semi-detached``) , liveDataTable.Rows.[0].``Semi-detached``
    String.Format("Terraced ({0})", liveDataTable.Rows.[0].Terraced), liveDataTable.Rows.[0].Terraced 
    String.Format("Apartments ({0})", liveDataTable.Rows.[0].``Flats and apartments``), liveDataTable.Rows.[0].``Flats and apartments`` ]
 
//Display this data in a styled Pie Chart     
Chart.Pie(propertyTypeData
).WithTitle(Text = "Property Types in Leigh (Surrey)", FontSize = 26.00, FontStyle = FontStyle.Bold
).WithStyling(BorderColor = Color.BlueViolet, BorderWidth = 2
).With3D()
Leigh in Surrey Property Types Pie Chart.

Leigh in Surrey Property Types Pie Chart.

I’ve added the data_structure html document into the root of my project to provide F# with an understanding of the structure of the HTML page I want to interrogate (i.e. provide an entity which I can strongly type against). Using live data returned from hooking into this types .Load method, we are able to construct a simple data set (a string * int list), which we pass to Chart.Pie using some additional methods to create a ‘not all that great’ chart style! Apologies all, it turns out chart styling isn’t my forte ๐Ÿ˜‰

Here’s on additional example showing the split (by millions) of males and females in the UK:

//Provide a type that outlines the page 'structure', so we can strongly type against it
type PopulationHtmlStructure = HtmlProvider<"data_structure_two.html">
 
//Retrieve live page data (and the relevant population html table)
let populationLiveDataPage = PopulationHtmlStructure.Load("https://en.wikipedia.org/wiki/Demography_of_the_United_Kingdom")
let populationLiveDataTable = populationLiveDataPage.Tables.``Age structure 2``
 
//Extract some data for Males and Females
let allPopulationData =
    [
        String.Format("Male ({0})", populationLiveDataTable.Rows.[4].Population), populationLiveDataTable.Rows.[4].Population;
        String.Format("Female ({0})", populationLiveDataTable.Rows.[4].Population2), populationLiveDataTable.Rows.[4].Population2;
    ]

//Simpler syntax, just call Chart.Pie with no extra bells and whistles
allPopulationData
|> Chart.Pie
 
//Produce a 3D pie chart, with some custom styling, based on this data
Chart.Pie(allPopulationData
).WithTitle(Text = "Population by Gender (Millions) - UK", FontName = "Verdana", FontSize = 26.00, FontStyle = FontStyle.Bold
).WithStyling(BorderColor = Color.AliceBlue, BorderWidth = 2
).With3D()
Population Gender Split UK Pie Chart  (Millions - Basic).

Population Gender Split UK Pie Chart (Millions – Basic).

Population Gender Split UK Pie Chart (Millions - 3D).

Population Gender Split UK Pie Chart (Millions – 3D).

Point Chart

Lastly, here is a randomly generated point chart, just to illustrate how this can be used:

//Declare and bind another 'random'
let anotherRandomValue = new Random()

//Create a basic point chart using the simpler |> syntax (denoting we are calling the basic Chart.Point constructor with no frills)
[for i in 0..500 -> anotherRandomValue.NextDouble(), anotherRandomValue.NextDouble()]
|> Chart.Point
Basic Point Chart.

Basic Point Chart.

//What does it look like in 3D. Hmmmm, not great!
Chart.Point([for i in 0..500 -> anotherRandomValue.NextDouble(), anotherRandomValue.NextDouble()]).With3D()
3D Point Chart.

3D Point Chart.

I hope this has provided a small insight into how you might use this in the wild. There are a fair few more chart types available so I’ll leave it up to you to dive in! Personally, I can see a window here to get hold of the JsonProvider and use this to access and manipulate data from my Fitbit. This is something I may touch on in the future so watch this space!

Thanks for reading through; full code listing below for convenience. Cheers and bye for now ๐Ÿ™‚

Full Listing

//FSharp Charting (with HtmlProvider usage) - Full Scripting Example

//#region Setup

//Navigate to the directory containing the FSharp.Charting.fsx file
#I "..\packages\FSharp.Charting.0.90.13"

//Read, compile and run the FSharpt.Charting.fsx file (to access the functionality of FSharp.Charting.dll)
#load "FSharp.Charting.fsx"

//Read in the FSharp.Data and FSharp.Data.TypeProviders assemblies (so we can make use of the HtmlProvider)
#r @"..\packages\FSharp.Data.2.2.5\lib\net40\FSharp.Data.dll"
#r @"..\packages\FSharp.Data.TypeProviders.0.0.1\lib\net40\FSharp.Data.TypeProviders.dll"

//Access namespaces as required by the examples. System for the 'Random' type and use of String.Format, FSharp.Data for the HtmlProvider, FSharp.Charting for what is says on the tin
//and System.Drawing for chart styling elements
open System
open FSharp.Data
open FSharp.Charting
open System.Drawing

//#endregion Setup

//#region Line Chart

//Define an array of resting heart rate values
let restingHeartRateValues = [69; 74; 76; 71; 68; 65; 64; 79; 80; 75; 72; 65; 63; 62; 59; 65; 80; 61; 59]

//Produce a line chart to visualise this data (with a Name, Title and labels for the X and Y axis)
Chart.Line(restingHeartRateValues, Name = "Resting Heart Rate Chart", Title = "Resting Heart Rate by Days", XTitle = "Days", YTitle = "Heart Rate (BPM)"
).WithYAxis(Min=55.0).With3D() //X Axis starts at 55 and with 3D

//#endregion Line Chart

//#region Column Chart

//Produce some test data (representing a drop in Kindle charge by day)
let kindleChargeData = [
    "Monday", 100;
    "Tuesday", 
    "Wednesday", 87;
    "Thursday", 76;
    "Friday", 61;
    "Saturday", 55;
    "Sunday", 50;
]

//Produce a column chart (with some basic styling for the chart; covering colours and borders, etc.)
Chart.Column(kindleChargeData, Color=Color.Green, Name = "Kindle Charge Chart", Title = "Decrease in Kindle Charge by Day"
).WithXAxis(Title = "Day", TitleColor = Color.Purple
).WithYAxis(Title = "Charge", TitleColor = Color.Purple, Max = 100.00           //Apply a max here to prevent the chart from going beyond 100 on the Y axis
).WithStyling(BorderColor = Color.DarkRed, BorderWidth = 3
).With3D()                                                                      //Because I can't resist!!!

//A brief Chart.Combine example
Chart.Combine(
    [   
        Chart.Column([ 12 ], Labels = ["Amy"])
        Chart.Column([ 9 ], Labels = ["Buster"]) 
        Chart.Column([ 10 ], Labels = ["Dave"])
        Chart.Column([ 13 ], Labels = ["Shirley"])
        Chart.Column([ 7 ], Labels = ["Stephanie"])
        Chart.Column([ 11 ], Labels = ["Bob"])
    ]
    ).WithTitle(Text = "Tasks Completed"
    ).WithXAxis(Title = "No. of Tasks"
    ).WithYAxis(Max = 15.00)

//#endregion Column Chart

//#region Bar Chart

//Bind a new variable to use in RNG
let randomValue = new Random()

//Using alternative syntax this time as we are not calling a more complex Chart.Bar constructor (for styling purposes, etc.). Do some RNG and produce a bar chart
[ for i in 0..15 -> i * randomValue.Next(10, 20) ]      //For zero to fifteen, times i by a random value (between the minimum and maximum specified)
|> List.map(fun i -> i + randomValue.Next(1, 2))        //Map the result list to another function that adds another random value (between the minimum/maximum specified) to each initial value
|> Chart.Bar                                            //Produce the chart

//#endregion Bar Chart

//#region Pie Chart

//---------------PIE CHART EXAMPLE ONE---------------

//Get a type denoting our pages structure (so we can strongly type against it), then retrieve live data by calling .Load
type WikiSurreyLeighStructure = HtmlProvider<"data_structure.html">
let liveDataTable = WikiSurreyLeighStructure.Load("https://en.wikipedia.org/wiki/Leigh,_Surrey").Tables.``Demography and housing 2``

//Prepare data scraped from the web page (details on the housing types in Leigh, Surrey)
let propertyTypeData = [
    String.Format("Detached ({0})", liveDataTable.Rows.[0].Detached), liveDataTable.Rows.[0].Detached
    String.Format("Semi-Detached ({0})", liveDataTable.Rows.[0].``Semi-detached``) , liveDataTable.Rows.[0].``Semi-detached``
    String.Format("Terraced ({0})", liveDataTable.Rows.[0].Terraced), liveDataTable.Rows.[0].Terraced 
    String.Format("Apartments ({0})", liveDataTable.Rows.[0].``Flats and apartments``), liveDataTable.Rows.[0].``Flats and apartments`` ]
 
//Display this data in a styled pie chart     
Chart.Pie(propertyTypeData
).WithTitle(Text = "Property Types in Leigh (Surrey)", FontSize = 26.00, FontStyle = FontStyle.Bold
).WithStyling(BorderColor = Color.BlueViolet, BorderWidth = 2
).With3D()

//---------------PIE CHART EXAMPLE TWO---------------
 
//Provide a type that outlines the page 'structure', so we can strongly type against it
type PopulationHtmlStructure = HtmlProvider<"data_structure_two.html">
 
//Retrieve live page data (and the relevant population html table)
let populationLiveDataPage = PopulationHtmlStructure.Load("https://en.wikipedia.org/wiki/Demography_of_the_United_Kingdom")
let populationLiveDataTable = populationLiveDataPage.Tables.``Age structure 2``
 
//Extract some data for Males and Females
let allPopulationData =
    [
        String.Format("Male ({0})", populationLiveDataTable.Rows.[4].Population), populationLiveDataTable.Rows.[4].Population;
        String.Format("Female ({0})", populationLiveDataTable.Rows.[4].Population2), populationLiveDataTable.Rows.[4].Population2;
    ]

//Simpler syntax, just call Chart.Pie with no extra bells and whistles
allPopulationData
|> Chart.Pie
 
//Produce a 3D pie chart, with some custom styling, based on this data
Chart.Pie(allPopulationData
).WithTitle(Text = "Population by Gender (Millions) - UK", FontName = "Verdana", FontSize = 26.00, FontStyle = FontStyle.Bold
).WithStyling(BorderColor = Color.AliceBlue, BorderWidth = 2
).With3D()

//#endregion Pie Chart

//#region Point Chart

//Declare and bind another 'random'
let anotherRandomValue = new Random()

//Create a basic point chart using the simpler |> syntax (denoting we are calling the basic Chart.Point constructor with no frills)
[for i in 0..500 -> anotherRandomValue.NextDouble(), anotherRandomValue.NextDouble()]
|> Chart.Point

//What does it look like in 3D. Hmmmm, not great!
Chart.Point([for i in 0..500 -> anotherRandomValue.NextDouble(), anotherRandomValue.NextDouble()]).With3D()

//#endregion Point Chart

Implementing reCAPTHCA

I wanted to outline some recent work I’ve done with the reCAPTCHA Google API. Although not too difficult to implement, I did struggle a little to find C# based server side examples on how to ultimately validate a CAPTCHA. To start us off however, what is reCAPTCHA?

reCAPTCHA is essentially a mechanism to protect your sites functionality from spam and other kinds of abusive activity. It’s free, which is a massive bonus, and as a cherry on top every solved CAPTCHA is used to annotate images and build on machine learning datasets. This data feeds into solving a myriad of problems, including improving maps and solving AI conundrums. The actual term is an acronym for Completely Automated Public Turing test to tell Computers and Humans Apart. For any history buffs, details on how this concept came about can be found here (in fact, there seems to be an interesting ‘origin of’ debate here):

Wiki CAPTCHA Documentation

And something a bit more fun:

To get started using reCAPTCHA, and for further information, you just need to visit the following link:

Google reCAPTCHA

Utilising reCAPTCHA version 2.0 seemed like the way to go for me and has a number of benefits. For example, it’s possible for this implementation to automatically confirm some requests as not being malicious in nature, without the need for a CAPTCHA to be solved. In addition, the CAPTCHAs themselves in version 2 are much nicer for a human to solve, relying on a party to pick out characteristics in an image, rather than trying to read ever more complex and convoluted character strings embedded in a given image. Image resolution (to pick out particular objects) is still a field whereby programs struggle somewhat, so this form of reCAPTCHA falls into a more secure bracket also.

Using reCAPTCHA

The basic process boils down to following these steps:

  • Go to the Google reCAPTCHA site and click on Get reCAPTCHA.
  • Sign in or sign up, do what you’ve got to do!
  • Register the domain where you want to embed reCAPTCHA. This will enable you to receive the relevant API keys to create and validate CAPTCHAs.
  • Add the relevant JavaScript to your page.
  • Embed the Site key in the page being served to the user (we’ll go over this below).
  • Use the Secret Key in your server side logic to validate the CAPTCHA response (based on user input). This is done by sending a request to the Google API siteverify address. Again, I’ll cover this below.
  • Get the response and see if the CAPTCHA has been solved correctly, simple as that.

First things first, you’ll want to safely note down your Site and Secret key for further use, these can be viewed again at any time by logging into the reCAPTCHA portal (where you signed up). So you’ve registered your domain and have the relevant keys, we now need to embed reCAPTCHA by adding the following element to the page you want to target:

<head>
...
    <!--Use async/defer as necessary if you desire-->
    <script src='https://www.google.com/recaptcha/api.js'></script>
...
</head>
<body>
    ...
    <!--The id attribute is not absolutely required, but I have listed it here as I make further use of (basically a style choice) for an jQuery AJAX call (could just use the class however)-->
    <div id="g-recaptcha-response" class="g-recaptcha" data-sitekey="YOUR_SITE_KEY_GOES_HERE"></div>
    ...
</body>

Be sure to drop the Site key you were provided with in the data-sitekey attribute, within the div outlined (and add the JavaScript reference listed to your page). Load up your page and you should see something akin to the following:

reCAPTCHA V2 Control.

reCAPTCHA V2 Control.

This is a super start. If you are doing a simple post on submit, you’ll be able to pull information out of the standard request object and use this server side. For me however, I wanted something incredibly lightweight so I went with the following jQuery AJAX call (I may tweak this in my personal implementation so treat this as not yet finalised, but it provides you with an idea of the structure nonetheless):


//Defines an outline (structure) for a javascript contact object
function Contact(name, email, message, recaptchaClientResponse) {
	this.Name = name
	this.Email = email;
	this.Message = message;
	this.RecaptchaClientResponse = recaptchaClientResponse;
}

...

//Submit Enquiry button click handler
$(".submit-enquiry").click(function (e) {

	//Hide the alert bar on every new request (TODO - More code required to tidy up classes on the alert div)
	$(".alert").hide();

	//Use ajax to call the service HandleEmailRequest method
	$.ajax({
		cache: false,
		async: true,
		type: "POST",
		dataType: "json",
		processData: false,
		data: JSON.stringify(
			{
				contactObj: new Contact
					(
						$("#NameTextBox").val(),
						$("#EmailTextBox").val(),
						$("#MessageTextArea").val(),
						$("#g-recaptcha-response").val()
					)
			}),
		url: "URL_TO_A_SERVICE.svc/HandleEmailRequest",
		contentType: "application/json;charset=utf-8",
		success: function (evt) {
			//Evaluate the response and add content to alert bar
			if (evt.SendEmailResult)
			{
				$(".alert").addClass("alert-success").html("<p>Message successfully sent!</p>").slideDown(1000);
			}
			else
			{
				$(".alert").addClass("alert-danger").html("<p>We couldn not send the message, sorry about that.</p>").slideDown(1000);
			}

			//Reset the recaptcha control after every request
			grecaptcha.reset();
		},
		error: function (evt) {
			//Add content to the alert bar to show the request failed
			$(".alert").addClass("alert-danger").html("<p>We could not send the message, sorry about that.</p>").slideDown(1000);

			//Reset the recaptcha control after every request
			grecaptcha.reset();
		}
	});
});

The first part of this code encapsulates the idea of a contact, in my case at least (i.e. a user leaving a message on the web page that will become an email). This is just an easy way for me to encapsulate details during the AJAX call. Using jQuery, I’ve attached a handler to the submit button on my page which, apart from a little UI manipulation (for an alert bar element), in essence just makes a call to a service (via the url parameter) using details that the client has provided, including information on the solved CAPTCHA. This is passed to the service using the data parameter; note the use of jQuery to get details of the CAPTCHA the user has completed ($(“#g-recaptcha-response”).val()). This is passed as JSON to the service. Once a request has been validated, the return value (a simple boolean in my case) is inspected and an alert is shown to the user before resetting the reCAPTCHA control (another spam control mechanism that I’ve added in for extra peace of mind). Lastly, for me, the use of JSON.stringify was absolutely key as I want to work with JSON data over the wire. More details can be found here:

JSON.stringify() Documentation

This is where it got a little trickier to proceed. On the reCAPTCHA documentation site, for version 2.0, I could only see examples for PHP:

reCAPTCHA Code Examples Available.

reCAPTCHA Code Examples Available.

So, what you’ll see next is the culmination of my digging around for a jQuery/AJAX/C# solution to this particular head-scratcher. Hopefully, it proves useful to anyone interested in going down this route.

Let’s get going! On the service side, you’ll need something like the following, to gather up the AJAX request:

/// <summary>
/// Represents a Contact (Potential Customer) contacting
/// the recipient with an enquiry.
/// </summary>
[DataContract]
public class Contact
{
	#region Automatic Properties (Data Members)

	/// <summary>
	/// The Contacts full name.
	/// </summary>
	[DataMember]
	public string Name { get; set; }

	/// <summary>
	/// The Contacts email address.
	/// </summary>
	[DataMember]
	public string Email { get; set; }

	/// <summary>
	/// The Contacts message to the recipient.
	/// </summary>
	[DataMember]
	public string Message { get; set; }

	/// <summary>
	/// A string that represents the clients reCAPTCHA
	/// (V2) response (passed along with other Contact
	/// information and processed before a message can be sent).
	/// </summary>
	[DataMember]
	public string RecaptchaClientResponse { get; set; }

	#endregion Automatic Properties (Data Members)
}

...

/// <summary>
/// Outlines the HandleEmailRequest method that is part of this service.
/// Consumes and returns a JSON format message (called from the client
/// with details that instantiate a Contact object). Method designed to 
/// process reCAPTCHA details and, on success, send an email to
/// the designated (recipient) email address. 
/// </summary>
/// <param name="contactObj">Contact details associated with the person requesting information.</param>
/// <returns></returns>
[OperationContract]
[WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.Wrapped)]
bool HandleEmailRequest(Contact contactObj);

...

/// <summary>
/// Public service method that attempts to send a
/// user message to the recipient as an email.
/// </summary>
/// <param name="contactObj">The Contact object constructed from JSON (passed from the client).</param>
/// <returns>A boolean that represents if this process was successful.</returns>
public bool HandleEmailRequest(Contact contactObj) => new EmailSender(contactObj).SendEmail();

I’ve given you a roll-up of an example Contact class (that is instantiated from the JSON automatically on call to the service), an example service interface definition and the outline of a service method (contained in a class implementing this interface). These of course are in separate files, but I’ve lined them up side-by-side to make it easier to absorb. In my case, the details are passed to and wrapped in an EmailSender class, the reCAPTCHA validation being called internally by the SendEmail method (as a private method called ValidateRecaptchaClientResponse):

/// <summary>
/// Private helper method that looks at the Contact object
/// associated with this EmailSender and attempts to verify
/// if the reCAPTCHA client response is valid (before attempting to
/// send an email message to the recipient). 
/// </summary>
/// <returns>A boolean that represents if reCAPTCHA validation succeeded or failed.</returns>
private bool ValidateRecaptchaClientResponse()
{
	//Online reference used as a basis for this solution: http://www.codeproject.com/Tips/851004/How-to-Validate-Recaptcha-V-Server-side
	try
	{
		//Make a web request to the reCAPTCHA siteverify (api) with the clients reCAPTCHA response. Utilise the Response Stream to attempt to resolve the JSON returned
		HttpWebRequest wr = (HttpWebRequest)WebRequest.Create(string.Concat("https://www.google.com/recaptcha/api/siteverify?secret=YOUR_SITE_SECRET_KEY_GOES_HERE&response=", contactObj.RecaptchaClientResponse));

		using (WebResponse response = wr.GetResponse())
		{
			using (StreamReader sr = new StreamReader(response.GetResponseStream()))
			{
				//Use a JavaScriptSerializer to transpose the JSON Response (sr.ReadToEnd()) into a RecaptchaResponse object. Alter the 'Success' string of this object to a bool if possible
				bool success = false;
				bool.TryParse(new JavaScriptSerializer().Deserialize<RecaptcaResponse>(sr.ReadToEnd()).Success, out success);

				//Return a value that denotes if this reCAPTCHA request was a success or failure
				return success;
			}
		}
	}
	catch (Exception ex)
	{
		//Catch any exceptions and write them to the output window (better logging required in future). Return false at the end of this method, issue occurred
		System.Diagnostics.Debug.WriteLine($"An error occurred whilst validating the ReCaptcha user response. Type: { ex.GetType().Name } Error: { ex.Message }.");
	}

	//If we hit this portion of the code something definitely went wrong - Return false
	return false;
}

Lines fourteen and twenty two are of the most interest here. On line fourteen, you will be required to insert your site ‘Secret key’ into a request to the siteverify address we mentioned earlier. The response that needs to be appended to this string is equal to the reCAPTCHA information you gleaned from the client. You’ll notice that line twenty two makes use of a RecaptchaResponse type; which is basically an object wrapper used to contain information from the deserialised JSON response (as part of a reCAPTCHA check). This is outlined as follows:

/// <summary>
/// Represents an object (constructed from JSON)
/// that outlines a reCAPTCHA Response, and the pieces
/// of information returned from a verification check.
/// </summary>
public class RecaptcaResponse
{
	#region Automatic Properties

	/// <summary>
	/// The success status of the reCAPTHCA request.
	/// </summary>
	public string Success { get; set; }

	/// <summary>
	/// The Error Descriptions returned (Possibly to implement
	/// in the future).
	/// </summary>
	//public string ErrorDescription { get; set; }

	#endregion Automatic Properties
}

The actual JSON returned from the response stream takes the following form, so it is possible to extract error codes also if you desire (for me, I’m ripping a simple boolean out of this based on the success value):

{
  "success": true|false,
  "error-codes": [...]   // optional
}

On a very last note, cited in the code above but to reiterate, this link was invaluable:

Code Project reCAPTCHA Validation Example

That’s about it for a basic end to end sample.

The API documentation (and the steps listed on the reCAPTCHA site after registration) are pretty good, so you should be in safe enough hands:

reCAPTCHA API Documentation

Thanks all and take care until the next time.