CODEWITHSUNDEEP
c#sql-serverjsonserializationsqlclr
Parsa
Parsa

Reputation: 11574

How to use Json Parser in CLR Procedure?

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)

Error ScreenShot

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.

Upvotes: 6

Views: 10136

Answers (4)

Ken Hadden
Ken Hadden

Reputation: 186

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/

Upvotes: 2

Geraldo Diaz
Geraldo Diaz

Reputation: 356

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

Upvotes: 2

Solomon Rutzky
Solomon Rutzky

Reputation: 48776

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.

Upvotes: 6

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

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

Key files:

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:

Upvotes: 2

Related Questions