How to use Json Parser in CLR Procedure?

Question

I am getting CRAZY trying to use a Json Serializer/Deserializer in my Class Library and import my Assembly in SQL Server.

I am working with a WebAPI that response Json string , and I want to create CLR Sql Procedure that call that API and use the api result.

I tried 2 ways to Deserialize Json string :

1) System.Web.Script.Serialization

2) System.Runtime.Serialization.Json

First one get me this error :

Assembly 'system.web.extensions, version=4.0.0.0, culture=neutral, publickeytoken=31bf3856ad364e35.' was not found in the SQL catalog. (Microsoft SQL Server, Error: 6503)

And Second one :

Assembly 'system.runtime.serialization, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog. (Microsoft SQL Server, Error: 6503)

Are there any way to parse json in my class library ?! (Except of creating a Json Serializer/Deserializer for my self in class library !!!)

Visual Studio 2015 Community , Microsoft Sql Server 2016 Developer

Thank you in Advance.

Yves

Answer 1

Unfortunately there is no method that is both part of the .NET Framework (i.e. built-in) and SAFE.

If you want a built-in method, then you could try using the DataContractJsonSerializer class, which is in the System.Runtime.Serialization.Json namespace, which in turn is found in System.Runtime.Serialization.dll. You can find an example of using it here: How to: Serialize and Deserialize JSON Data. HOWEVER, in order to use this inside of SQL Server, you will need to import the System.Runtime.Serialization.dll since it is not one of the Supported .NET Framework Libraries. And because it is not supported, that means three things:

1. You need to set the database containing the Assemblies to TRUSTWORTHY ON (due to needing the PERMISSION_SET to be UNSAFE), something which is generally advised against due to it being a security risk.

2. You cannot be certain that the underlying code is not doing something that could cause "odd" behavior, such as caching values in static class variables. SQLCLR uses a single App Domain per each Assembly-owner + Database combination. Hence the class will be shared across all sessions that execute that code.

3. You cannot be guaranteed that System.Runtime.Serialization.dll (or either of its two dependent libraries: System.ServiceModel.Internals and SMDiagnostics) won't be changed into a mixed-mode DLL in a future .NET Framework update. Only pure MSIL libraries are allowed in SQL Server, so if any of those 3 ever change to instead be "mixed", then your code in SQL Server will start failing and there is no way to fix it; you will have to re-code it. And this has happened before: System.ServiceModel became mixed-mode with the release of .NET 4.0, and so code using it works in SQL Server 2005, 2008, and 2008 R2 (all linked to CLR v 2.0 and Framework versions 2.0 - 3.5), but won't work starting in SQL Server 2012 (all linked to CLR v 4.0 and Framework versions 4.0 and newer).

But, if you want to try it, do the following (it will auto-load the 2 dependent DLLs):

USE [someDB];

ALTER DATABASE CURRENT SET TRUSTWORTHY ON;

CREATE ASSEMBLY [System.Runtime.Serialization]
FROM 'C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll'
WITH PERMISSION_SET = UNSAFE;

OR, you can include code to parse the JSON in your project. You have some options here as well:

1. While the "preferred" JSON parser is Json.NET, I have not been able to get it to load as a SAFE Assembly. It has been a few years since I have tried, but it had quite a few static class variables being used to cache values (helps performance, but won't work in a shared environment), and I seem to recall that it relied on some unsupported libraries (e.g. System.Runtime.Serialization).

2. I have used JsonFx with some success. That code also needed some updates to take care of static class variables, but it was possible. This project can handle converting from JSON to a few different markups.

3. As mentioned in @EvaldasBuinauskas's answer, you can try the LitJSON project. I have not tried this project so am not sure how well it works. It seems to be a bit smaller than the JsonFX project (doesn't do the other formats), but as of right now it has 25 outstanding issues while JsonFX only has 16.

   You should probably look at the list "Issues" for both projects to make sure that there isn't something reported that would cause an error for you.

Comose

The code you gave worked , thank you very much , but it has warning that said : if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working . what is your suggestion ? include code to parse the JSON in your project ? or keep going with importing System.Runtime.Serialization ? – Yves

@Yves Yes, that "warning" is exactly what I had mentioned in point # 3 in the top section (using ServiceModel as an example). Given that JSON, just like XML, is just a text format, it is just a matter of parsing it (as opposed to needing a resource that is only available in the .NET Framework libraries). This is why I use JsonFX. It's not perfect, but it does what I need, with the Assembly marked as SAFE and the Database set to TRUSTWORTHY OFF. But if you just need this for one or two documents (not in a general sense), then I would write it by hand and be done with it. – Comose

I used JsonFx too , while I wanted to add JsonFx to sqlserver assemblies , it gived me this error : <<< CREATE ASSEMBLY failed because type 'XmlInTransformer' in safe assembly 'JsonFx' has a static field 'DefaultObjectName'. Attributes of static fields in safe assemblies must be marked readonly in Visual C#, ReadOnly in Visual Basic, or initonly in Visual C++ and intermediate language. >>>After that I go to JsonFx source and make that attributes ReadOnly !!! And it works , is that true ??? – Yves

I think with making them read only , maybe in future It throw unexpected Errors ... – Yves

@Yves Yes, marking it as readonly is what I meant by "that code also needed some updates to take care of static class variables". It seems to work fine in my usage of it, but I am not populating an object with it. Still, it shouldn't generate an error later on as that would be caught by the compiler. I think it might be able to show up as "odd" behavior, but that is why we test ;-). – Comose

Answer 2

I've had a similar issue myself, look at the way https://github.com/WCOMAB/SqlServerSlackAPI imports LitJSON to its' project.

Key files:

• https://github.com/WCOMAB/SqlServerSlackAPI/blob/master/src/SqlServerSlackAPI/Include_T4Include.tt
• https://github.com/WCOMAB/SqlServerSlackAPI/blob/master/src/SqlServerSlackAPI/Include_T4Include.cs

You can take similar approach. Using this method I didn't have to import any other extra CLR to SQL Server instance.

Worth mentioning, that assembly will have to be unsave. In order to deploy it you'll have two options:

• Set database to TRUSTWORTHY
• Setup asymmetric key. This will help you: Security risks of setting trustworthy = on in sql server 2012
  • Following link has detailed instructions how to do that : http://www.sqlservercentral.com/articles/SQLCLR/138155/

Marchioness

I set my database TRUSTWORTHY on , and it already gives me error. According to GitHub links you gaved , the final way that I can come up with this problem is create Json parser for my self ? ( with regex or split , etc .) – Yves

You also can import those libraries that are mentioned in error messages to sql server. – Marchioness

when I add this assembly it gives me this Error : Assembly 'system.servicemodel.internals, was not found in the SQL catalog. Warning: The Microsoft .NET Framework assembly 'system.runtime.serialization, you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details. (Microsoft SQL Server, Error: 6503) – Yves

I add it like other assembelies . I found system.runtime.serialization.dll path and paste it on AssemblyPath in SQL server new assembly form. – Yves

Just go down the path until you have all pre-requisite assemblies, I think you'll have to add like 5 in total. – Marchioness

Answer 3

Take a look to this repository https://github.com/geral2/SQL-APIConsumer.

STEP 1

CREATE DATABASE TestDB; GO

STEP 2

USE TestDB GO sp_configure 'clr enabled',1 RECONFIGURE GO

STEP 3

ALTER DATABASE TESTDB SET TRUSTWORTHY ON GO

STEP 4

CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE--external_access GO

STEP 5

CREATE ASSEMBLY [Newtonsoft.Json] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Newtonsoft.Json.dll' WITH PERMISSION_SET = UNSAFE go

Deweydewhirst

Answer 4

This is an old question but I feel like it is something that everyone will eventually run into. Especially now that JSON has become ubiquitous in data exchange operations. I won't go into too many details on how to run the queries involved because it is really no different than any other ado.net connection/command scenario...

But if you are on SQL Server 2016 or later, just open a Context Connection from within your CLR sproc:

    using (SqlConnection conn = new SqlConnection("Context Connection=true")) { // ...

Pass your json string in as a parameter:

    var jsonParam = cmd.Parameters.Add("@json", System.Data.SqlDbType.NVarChar, -1);
    jsonParam.Value = jsonString;

Then use sql server's built-in json support to parse your JSON. You can then access single values with JSON_VALUE and ExecuteScalar:

using (SqlCommand cmd = new SqlCommand(@"select JSON_VALUE(@json, '$.path.to.data')", conn)) {
    return cmd.ExecuteScalar()?.ToString();
}

Or if you have an array of similar objects in your json, you can use OPENJSON with a defined schema (OPENJSON's WITH clause) to build a result set from your @json parameter. Then use ExecuteReader to get the data. See "Defining a Schema" here: https://database.guide/introduction-to-openjson-with-examples-sql-server/

Piet