Network Programming in .NET

If your application stores data in SQL server as a JSON value, you will find it difficult to read out individual properties on this data. This means that you can’t do joins on fields that are held within the data, or any aggregate queries. It’s just not flexible at all.

So, as the name suggests, I’ve used a C# CLR UDF (User defined function) to do this, where it takes in the string, processes it within the CLR and returns it to SQL server.

To give you a few “anti-patterns” of things that don’t work. You may find that SQL server only supports a limited set of .NET assemblies, so you can’t import Newtonsoft to handle the JSON, nor can you use System.Runtime.Serialization, which is not allowed by SQL server either. So I had to use a home grown JSON parser, by Procurios (http://techblog.procurios.nl/k/news/view/14605/14863/how-do-i-write-my-own-parser-(for-json).html) – thanks @procurios

So, creating a new CLR Project in Visual Studio, I added the procurios JSON class, and this code;

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString CLR_ReadJSON(string json, string property)
{
var o = JSON.JsonDecode(json) as Hashtable;
return o[property].ToString();
}

I compiled the DLL, transferred it to the server, and added a new Assembly, which I’ve called CLR_DataTools (The above code was in a namespace called StoredProcedures)

Then I wrote the following SQL code to define the UDF

CREATE FUNCTION [dbo].[CLR_UdfReadJson]
(
@json [nvarchar](4000),
@property [nvarchar](4000)
)
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLR_DataTools].[StoredProcedures].[CLR_ReadJSON]
GO

And that’s all you need! (Although this took me a few hours to figure out)

As an aside, here’s some code I wrote to handle XPath queries on XML within SQL server, but I am aware that there are better ways to do this;

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString CLR_ReadXML(string xml, string xPath)
{
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml(xml);
var xn = xdoc.DocumentElement.SelectSingleNode(xPath);
var strXml = xn.InnerXml;
return strXml;
}

and then defined the SQL UDF as follows;

CREATE FUNCTION [dbo].[CLR_UdfReadXML]
(
@xml [nvarchar](4000),
@xPath [nvarchar](4000)
)
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [CLR_DataTools].[StoredProcedures].[CLR_ReadXML]
GO