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.
There is no json data type
in sql sever you can simply send your json
as varchar
to stored procedure.
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
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))
Here is a very good and detailed article which will give you detailed idea to deal with json data