使用ASP.Net Core项目中的ADO.Net将JSON类型作为参数传递给SQL Server 2016存储过程

Can someone give example how to pass JSON type as parameter to SQL Server 2016 stored procedure using ADO.Net in C# ASP.Net Core Web Api project ? I want to see example of SQL Server 2016 stored procedure and pass of JSON type in C# ASP.Net Core Web Api.

有人可以举例说明如何使用C＃ASP.Net Core Web Api项目中的ADO.Net将JSON类型作为参数传递给SQL Server 2016存储过程吗？我想在C＃ASP.Net Core Web Api中看到SQL Server 2016存储过程的示例和JSON类型的传递。

2 个解决方案

#1

There is no json data type in sql sever you can simply send your json as varchar to stored procedure.

在sql服务器中没有json数据类型，您只需将json作为varchar发送到存储过程即可。

If you want to map your json to table you can use use OPENJSON to convert data to rows and columns.

如果要将json映射到表，可以使用OPENJSON将数据转换为行和列。

CREATE PROCEDURE SaveJSON
@pID int,
@pJson nvarchar(max)

AS
BEGIN

INSERT INTO [YourTable]
       ([ID]
       ,[JSONData])
 VALUES
       (@pID
       ,@pJson)
END

If you want to map json objects with table you can do this

如果要将json对象与表映射，可以执行此操作

//json would be something like this
[
 { "id" : 2,"name": "John"},
 { "id" : 5,"name": "John"}
]

INSERT INTO YourTable (id,Name)
SELECT id, name
FROM OPENJSON(@pJson)
WITH (id int,
name nvarchar(max))

Here is a very good and detailed article which will give you detailed idea to deal with json data

这是一篇非常好的详细文章，它将为您提供处理json数据的详细信息

#2

SQL Server 2016 do have native JSON support - a new JSON datatype (which is based on nvarchar) is there, as well as a FOR JSON command to convert output from a query into JSON format

SQL Server 2016确实具有本机JSON支持 - 一种新的JSON数据类型（基于nvarchar），以及用于将查询输出转换为JSON格式的FOR JSON命令

Microsoft did not include a separate JSON datatype - instead, there are a number of JSON functions (to package up database rows into JSON, or to parse JSON into relational data) which operate on columns of type NVARCHAR(n)

Microsoft没有包含单独的JSON数据类型 - 相反，有许多JSON函数（将数据库行打包成JSON，或将JSON解析为关系数据），这些函数在NVARCHAR（n）类型的列上运行

If you have JSON text, you can extract data from JSON or verify that JSON is properly formatted using built-in functions JSON_VALUE, JSON_QUERY, and ISJSON. For more advanced querying and analysis, the OPENJSON function can transform an array of JSON objects into a set of rows. Any SQL query can be executed on the returned result set. Finally, there is the FOR JSON clause that enables you to format query results as JSON text.

如果您有JSON文本，则可以从JSON中提取数据或使用内置函数JSON_VALUE，JSON_QUERY和ISJSON验证JSON是否已正确格式化。对于更高级的查询和分析，OPENJSON函数可以将JSON对象数组转换为一组行。可以在返回的结果集上执行任何SQL查询。最后，还有FOR JSON子句，使您可以将查询结果格式化为JSON文本。

So, I recommend you use NVARCHAR(MAX) as your stored procedure parameter.

因此，我建议您使用NVARCHAR（MAX）作为存储过程参数。

#1