在ASP.Net Core项目中使用ADO.Net将JSON类型作为参数传递给SQL Server 2016存储过程 [英] Passing JSON type as parameter to SQL Server 2016 stored procedure using ADO.Net in ASP.Net Core project

问题描述

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

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.

推荐答案

在sql服务器中没有json data type，您可以简单地将json作为varchar发送到存储过程.

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

如果要将json映射到表，可以使用OPENJSON将数据转换为rows和columns.

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

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

AS
BEGIN

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

如果您想将json对象与表进行映射，您可以这样做

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

//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))

此处非常优质而详尽的文章，将为您提供处理json data

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

这篇关于在ASP.Net Core项目中使用ADO.Net将JSON类型作为参数传递给SQL Server 2016存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！