During this post, I explained how to get information from a database in JSON format. In this one, I will show you how to store information sent to the database in JSON format.

I will use a very common scenario, storing a master-detail combination. For this, I use the Order-Order Details tables in Northwind database, which you can get here.

The goal is to store a new Order, with several Order Details in the same procedure, by using T-SQL OPENJSON.

Like a cooking recipe, I will explain this step by step.

Define the JSON schema.

We want to store the information received in the Order Details table, so its schema will be the schema received in the JSON information.

Simply get the Order Details schema and remove the NOT NULL modifiers. This will be used in the WITH modifier of the OPENJSON statement this way:

OPENJSON(@Details) WITH([OrderID] [INT], [ProductID] [INT],
[UnitPrice] [MONEY], [Quantity] [SMALLINT], [Discount]
[REAL]);

Prepare the Stored Procedure.

Parameters.

It must have parameters to receive all the data for the Orders Table’s columns, and one more containing the entire JSON information for the Order Details table. Notice the OrderID parameter is declared as OUTPUT, so the calling code could retrieve the new Order ID for the inserted row.

   @OrderID        INT OUTPUT
, @CustomerID     NCHAR(5)
, @EmployeeID     INT
, @OrderDate      DATETIME
, @RequiredDate   DATETIME      = NULL
, @ShippedDate    DATETIME      = NULL
, @ShipVia        INT
, @Freight        MONEY
, @ShipName       NVARCHAR(40)
, @ShipAddress    NVARCHAR(60)
, @ShipCity       NVARCHAR(15)
, @ShipRegion     NVARCHAR(15)  = NULL
, @ShipPostalCode NVARCHAR(10)
, @ShipCountry    NVARCHAR(15)
, @Details        NVARCHAR(MAX)

Insert the Orders new row values.

It is a simple insert – values sentence, as follows:

INSERT INTO [Orders]
      (
   [CustomerID]
 , [EmployeeID]
 , [OrderDate]
 , [RequiredDate]
 , [ShippedDate]
 , [ShipVia]
 , [Freight]
 , [ShipName]
 , [ShipAddress]
 , [ShipCity]
 , [ShipRegion]
 , [ShipPostalCode]
 , [ShipCountry]
  )
VALUES
    (
  @CustomerID
, @EmployeeID
, @OrderDate
, @RequiredDate
, @ShippedDate
, @ShipVia
, @Freight
, @ShipName
, @ShipAddress
, @ShipCity
, @ShipRegion
, @ShipPostalCode
, @ShipCountry
);

Get the new inserted OrderId.

For this, the procedure must use the IDENT_CURRENT function.

SET @OrderID = IDENT_CURRENT('[Orders]');

Insert the Order Details using OPENJSON.

In this case, using Insert – select statement, and OPENJSON from the Details parameter as source, declaring it with the previously obtained schema. Notice the utilization of the @OrderID parameter for the Order Id value in each row.

INSERT INTO [Order Details]
      (
   [OrderID]
 , [ProductID]
 , [UnitPrice]
 , [Quantity]
 , [Discount]
  )
       SELECT 
          @OrderID /* Using the new Order ID*/
 
        , [Productid]
        , [UnitPrice]
        , [Quantity]
        , [Discount]
       FROM 
          OPENJSON(@Details) WITH([OrderID] [INT], [ProductID] [INT],
          [UnitPrice] [MONEY], [Quantity] [SMALLINT], [Discount]
          [REAL]);

The C# code.

Define the Order and Order Details entities in your Application.

I created a simple C# Console Application Sample. In it, the Order and Order_Details has been defined by using the Paste Special Paste JSON as Classes feature in Visual Studio. You can see the step by step here.

The Insert routine in the main program.

The code creates a new instance of the Order class, with values,

Order order = new()
{
   CustomerID = "ALFKI",
   EmployeeID = 1,
   OrderDate = DateTime.UtcNow,
   RequiredDate = DateTime.UtcNow.AddDays(5),
   ShipAddress = "Obere Str. 57",
   ShipCity = "Berlin",
   Freight = 12.05F,
   ShipCountry = "Germany",
   ShipName = "Alfreds Futterkiste",
   ShipPostalCode = "12209",
   ShipRegion = null,
   ShipVia = 1
};

Then get information from a previously defined set, (as JSON), to have an array of Order Details.

// Create the details. To Avoid a long code, just get it from a JSON sample
var details = System.Text.Json.JsonSerializer.Deserialize<OrderDetail[]>
   (InsertUsingJSONSample.Properties.Resources.Details);

Create the Connection and Command objects.

A connection object to the database is assigned to a Command object, with the name of the stored procedure as text, which is defined as a Stored Procedure command type.

SqlConnection con = new SqlConnection(InsertUsingJSONSample.Settings1.Default.ConString);
SqlCommand com = new SqlCommand("InsertWithJSONSP", con)
{
   CommandType = System.Data.CommandType.StoredProcedure
};

Add the parameters

Then, all the Order properties are added as parameters, plus one more, Details, containing the Order_Detail array expressed as JSON.

To do so, the code use Reflection to get all the properties in the Order instance and their values.

Note the parameter @OrderID is defined as InputOutput, so the code could retrieve the new Order Id once the procedure ends execution.

foreach (PropertyInfo item in order.GetType().GetProperties())
{
   com.Parameters.AddWithValue("@" + item.Name, item.GetValue(order));
 
}
com.Parameters["@OrderId"].Direction = System.Data.ParameterDirection.InputOutput;

Finally, the command is executed to insert the new Order with the Details and retrieve the new Id.

using (con)
{
   con.Open();
   int retValue = await com.ExecuteNonQueryAsync();
   int NewOrderID = (int)com.Parameters["@OrderId"].Value;
}

As usual, you will find the code sample here.

By using this method, you reduce the calls between your app and the database server, optimizing the response, and including the entire store process in a unique implicit transaction.

HTH