SQL uses newtonsoft. JSON to process JSON strings

Environment:

SQL server2005;

Vs2010;

For how to deploy CLR assembly in SQL, you can search for it on the Internet.

The string to be processed in SQL is too weak to be processed by arrays, let alone the JSON string. Well, it cannot be processed, I will add a processing assembly to it (fortunately, this function has been added to 2005 ).

First of all, I do not know much about CLR, and I have not yet understood how to register a DLL to SQL. I directly used vs2010 to create a database project for SQL Server, then deploy it to SQL, saving the amount of operations.

Here we will talk about the CLR database project. The first development did not really understand the reference, so we could not select the local newtonsoft. JSON. DLL file, and a lot of C # class libraries do not exist, so how can I use newtonsoft. JSON?

Okay, I made the dumbest way to directly change newtonsoft. the JSON source code is copied into my project. There are a lot of errors, first of all.. Net version check error, which will be directly used later. net3.5 method (2005 does not support 4.0 or more.. runtime. all classes in serialization, such as enummemberattribute, are stored. Okay, I will copy the code of this class again. OK, one-to-one copy ends, compile, haha, that's right! Start deployment.

Wait for half a day to prompt an error: the Assembly cannot be found in the SQL Directory: system. Core, version = 3.5.0.0. I am dizzy for half a day. How can I import this stuff into the SQL directory? Finally, an old man said:

SQL Server does not support system. Core. dll assembly. You should first load:

EXEC dbo.sp_changedbowner @loginame = N'sa', @map = trueGOsp_configure 'clr enabled', 1GORECONFIGUREGOALTER DATABASE [stzpbbs] SET TRUSTWORTHY ONGOCREATE ASSEMBLY [System.Core]AUTHORIZATION [dbo]FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'WITH PERMISSION_SET = UNSAFEGO

Change your database name, run, and run successfully. Click the database Assembly directory, and then deploy, and prompt that the Assembly cannot be found in the SQL directory: system. XML. that means the system. the core has been introduced correctly. Here, I have not continued, since the system. core can be directly introduced into the database, newtonsoft. what if the JSON line does not work? Following this idea, a total of databases need to be introduced:

System. Core. dll

System. xml. LINQ. dll

Smdiagnostics. dll

System. runtime. serialization. dll

Newtonsoft. JSON. dll

Note: permission_set = unsafe, all of which are set to unsafe.

Newtonsoft. JSON. after the DLL is successfully added, open vs2010 to create a database project and add references. newtonsoft is displayed in the list. JSON library. Haha, click reference to create a user-defined function. Now you can use newtonsoft directly. the JSON method deserializes the JSON string. The content is as follows:

Using system; using system. data; using system. data. sqlclient; using system. data. sqltypes; using Microsoft. sqlserver. server; using newtonsoft. JSON; using newtonsoft. JSON. LINQ; public partial class userdefinedfunctions {[Microsoft. sqlserver. server. sqlfunction] public static sqlstring JSON (string JSON, string key) {// place the code jobject ja = (jobject) jsonconvert here. deserializeobject (JSON); string Re = JA [Key]. tostring (); return New sqlstring (re);} [Microsoft. sqlserver. server. sqlfunction] public static sqlstring jsonarray (string JSON, int S, string key) {// place the code jarray ja = (jarray) jsonconvert here. deserializeobject (JSON); string Re = JA [s] [Key]. tostring (); return New sqlstring (re );}};

Finally, deploy the database, and call the method in the database:

Print DBO. JSON ('{key: "001", value: "Hello"}', 'value') print DBO. jsonarray ('[{key: "001", value: "Hello"}, {key: "002", value: "bad"}]', 1, 'value ')

 

Hi!

 

Author:Kunoy Source:Http://blog.csdn.net/kunoy Statement:The authors write blogs to sum up experience and exchange learning.
If you need to reprint the statement, please keep it as much as possible and provide the original article connection clearly on the article page. Thank you!