How to use Json Parser in a CLR procedure?

Question

I get CRAZY trying to use Json Serializer / Deserializer in my class library and import my assembly into SQL Server.

I am working with a WebAPI that responds with a Json string, and I want to create a Sql CLR procedure that calls this API and uses the result of the api.

I tried two ways to deserialize a Json string:

1) System.Web.Script.Serialization

2) System.Runtime.Serialization.Json

First I will get this error:

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

And the second one:

Assembly 'system.runtime.serialization, version = 4.0.0.0, = , publickeytoken = b77a5c561934e089. ' SQL. (Microsoft SQL Server, : 6503)

json ?! ( Json Serializer/Deserializer !!!)

Visual Studio 2015, Microsoft Sql Server 2016

Advance.

Answer 1

, , .NET Framework (.. ) SAFE.

, DataContractJsonSerializer, System.Runtime.Serialization.Json, , , System.Runtime.Serialization.dll. : . JSON. , SQL Server, System.Runtime.Serialization.dll, .NET Framework. , :

• , , TRUSTWORTHY ON (- PERMISSION_SET UNSAFE), - .

• , -, "" , . SQLCLR Assembly-owner + Database. , , .

• , System.Runtime.Serialization.dll ( : System.ServiceModel.Internals SMDiagnostics) DLL .NET Framework. SQL Server MSIL, , 3 - , "", SQL Server , ; . : System.ServiceModel .NET 4.0, SQL Server 2005, 2008 2008 R2 ( CLR v 2.0 Framework 2.0 - 3.5), SQL Server 2012 ( CLR 4.0 Framework 4.0 ).

, ( 2 DLL):

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;

,, JSON . :

• "" JSON Json.NET, SAFE. , , ( , ), , , , (, System.Runtime.Serialization).

• JsonFx . , , . JSON .

• @EvaldasBuinauskas, LitJSON. , , . , , JsonFX ( ), 25 , JsonFX 16.

  , "" , , , .

Answer 2

, https://github.com/WCOMAB/SqlServerSlackAPI LitJSON .

:

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

. , - CLR SQL Server.

, . , :

• TRUSTWORTHY
• . : trustworthy = on SQL Server 2012
  • , : http://www.sqlservercentral.com/articles/SQLCLR/138155/