OPENJSON
It is an SQL function. That is used to convert JSON text and return a key and value as a row and column format.
Step 1: Following is the procedure used to access data using JSON string.
CREATE PROC [dbo].[p_JsonCustomeOrder]
(
@CustomerIdJson NVARCHAR(MAX)='[{"Id":1},{"Id":2},{"Id":3}]'
)
AS
BEGIN
SELECT cus.FirstName
,cus.LastName
,ord.*
FROM [Order] ord
INNER JOIN Customer cus ON ord.CustomerId = cus.Id
INNER JOIN (
SELECT ID
FROM OPENJSON(@CustomerIdJson)
WITH (ID INT '$.Id')
) AS JsonVal ON JsonVal.ID = ord.CustomerId
Order By ord.CustomerId
END
Step 2: Following is the C# code used to generate JSON and pass it as SQL parameters to get the appropriate data.
using Newtonsoft.Json;
using System;
using System.Data;
namespace AppJSON_SQL
{
internal class Program
{
static void Main(string[] args)
{
DataSet ds = fnGetCustomerOrder();
DataView view = new DataView(ds.Tables[0]);
DataTable distinctValues = view.ToTable(true, "CustomerId", "FirstName", "LastName");
if (ds != null && ds.Tables.Count > 0)
{
foreach(DataRow dr in distinctValues.Rows)
{
int CustomerId = Convert.ToInt32(dr["CustomerId"]);
Console.WriteLine("Customer Name: " + (String)dr["FirstName"] + " " + (String)dr["LastName"]);
foreach (DataRow row in ds.Tables[0].Rows)
{
int Id = Convert.ToInt32(row["CustomerId"]);
if (Id == CustomerId)
{
Console.WriteLine((String)row["OrderNumber"] + " " + Convert.ToDateTime(row["OrderDate"]).ToShortDateString() + " " + Convert.ToDecimal(row["TotalAmount"]));
}
}
}
}
Console.ReadKey();
}
private static DataSet fnGetCustomerOrder()
{
try
{
string obj = "[{\"Id\":1},{\"Id\":2},{\"Id\":2},{\"Id\":3},{\"Id\":4}]";
string json = JsonConvert.SerializeObject(obj);
SQL objSql = new SQL();
objSql.AddParameter("@CustomerIdJson", DbType.String, ParameterDirection.Input, 0, obj);
DataSet ds = objSql.ExecuteDataSet("p_JsonCustomeOrder");
return ds;
}
catch (Exception)
{
return null;
}
}
}
}
Step 3: Output
No comments:
Post a Comment