Passing a JSON type as a parameter to a SQL Server 2016 stored procedure using ADO.Net in an ASP.Net Core project

Question

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

Answer 1

There is no json data type in sql sever, you can just send json as varchar to the stored procedure.

If you want to map json to a table, you can 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 

If you want to map json objects to a 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)) 

Here is a very good and detailed article in which you will talk in detail about json data

Answer 2

SQL Server 2016 has built-in JSON support - there is a new JSON data type (which is based on nvarchar ), as well as a FOR JSON command to convert the output from a query to JSON format

Microsoft did not include a separate JSON data type — instead, there are a number of JSON functions (for batch processing database rows in JSON or for parsing JSON in relational data) that work with columns of type NVARCHAR(n)

If you have JSON text, you can extract data from JSON or make sure that JSON is properly formatted using the built-in functions JSON_VALUE , JSON_QUERY and ISJSON . For more complex queries and analysis, the OPENJSON function can convert an array of JSON objects into a set of strings. Any SQL query can be executed in the returned result set. Finally, there is a FOR JSON clause that allows you to format query results as JSON text.

So, I recommend using NVARCHAR(MAX) as a parameter to the stored procedure.