What is Code to Pass JSON to a Stored Procedure

What is Code to Pass JSON to a Stored Procedure

The SQL Server stored procedure should accept the JSON string as an NVARCHAR(MAX) parameter. You can use SQL Server's OPENJSON function to parse the JSON and insert/update data as needed.

To convert the TravelClassModel data into a JSON string, you would use JsonConvert.SerializeObject() method from the Newtonsoft.Json library. Here's how you can serialize the TravelClassModel object into a JSON string, assuming you want to deserialize a list of TravelClassModel objects from a response:

using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using thecodefree.Models;
 
namespace thecodefree.Controllers {
    public class HomeController : Controller
    {
        private readonly ILogger < HomeController > _logger;
 
        public HomeController(ILogger < HomeController > logger)
        {
            _logger = logger;
        }
 
        public IActionResult Index()
        {
            return View();
        }
 
        public IActionResult Privacy()
        {
            return View();
        }
 
        public ActionResult SaveSalesInvoice(SalesInvoiceSetModel salesInvoiceSetModel)
        {
            // Step 1: Serialize the model to JSON string
            var settings = new JsonSerializerSettings
            {
                DateFormatString = "yyyy-MM-dd HH:mm:ss.fff"
            };
            var jsonObj = JsonConvert.SerializeObject(salesInvoiceSetModel, settings);
 
            // Step 2: Add the JSON string as a parameter to the stored procedure
            var sqlparameters = new Dictionary < string, object>
                {
            { "@strJSON", jsonObj }
        };
 
        // Step 3: Call the method to execute the stored procedure
        var result = ExecuteStoredProcedureAsync("SP_Demo_Save", sqlparameters);
 
        // Handle the result (e.g., return to view or redirect)
        return View();
    }
 
        // Method to execute stored procedure with parameters
        private async Task < DataTable > ExecuteStoredProcedureAsync(string procedureName, Dictionary < string, object > parameters)
    {
            DataTable resultTable = new DataTable();
 
        var connectionString = GetConfiguration().GetConnectionString("ConnectionString");
        using(SqlConnection connection = new SqlConnection(connectionString))
        {
            using(SqlCommand command = new SqlCommand(procedureName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;
 
                foreach(var param in parameters)
                {
                    command.Parameters.AddWithValue(param.Key, param.Value);
                }
 
                await connection.OpenAsync();
                using(SqlDataReader reader = await command.ExecuteReaderAsync())
                {
                    resultTable.Load(reader);
                }
            }
        }
 
        return resultTable;
    }
 
        public IConfigurationRoot GetConfiguration()
    {
        var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);
        return builder.Build();
    }
    [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
    {
        return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
    }
}
}
 
 
 
 
using Microsoft.AspNetCore.Hosting.Server;
using Microsoft.AspNetCore.Http.HttpResults;
using Newtonsoft.Json;
using static System.Runtime.InteropServices.JavaScript.JSType;
using System.Reflection.Metadata;
using System.Runtime.Intrinsics.X86;
 
namespace thecodefree.Models
{
    public class ErrorViewModel
    {
        public string? RequestId { get; set; }
 
        public bool ShowRequestId => !string.IsNullOrEmpty(RequestId);
    }
    public class SalesInvoiceSetModel
    {
        public string InvoiceNumber { get; set; }
        public DateTime InvoiceDate { get; set; }
        public CustomerDetailsModel CustomerDetails { get; set; }
        public List<SalesInvoiceItemModel> Items { get; set; }
        public decimal TotalAmount { get; set; }
        public string Remarks { get; set; }
    }
 
    public class CustomerDetailsModel
    {
        public string CustomerName { get; set; }
        public string CustomerEmail { get; set; }
        public string CustomerPhone { get; set; }
        public string BillingAddress { get; set; }
        public string ShippingAddress { get; set; }
    }
 
    public class SalesInvoiceItemModel
    {
        public string ItemCode { get; set; }
        public string ItemName { get; set; }
        public int Quantity { get; set; }
        public decimal UnitPrice { get; set; }
        public decimal TotalPrice => Quantity * UnitPrice; // Calculated property
    }
 
}
 
 
 
{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    },
    "AllowedHosts": "*",
    "Data": {
        "ConnectionString": "Data Source=103;Initial Catalog= _DB;Integrated Security=False;Persist Security Info=False;User ID= _DB;Password= 123"
    }
}
 
 
 
CREATE PROCEDURE SP_Demo_Save
@strJSON NVARCHAR(MAX)
AS
BEGIN
--Parse the JSON string and insert into the SalesInvoice table
    INSERT INTO SalesInvoice(InvoiceNumber, InvoiceDate, CustomerName, TotalAmount)
SELECT
JSON_VALUE(@strJSON, '$.InvoiceNumber'),
    JSON_VALUE(@strJSON, '$.InvoiceDate'),
    JSON_VALUE(@strJSON, '$.CustomerDetails.CustomerName'),
    JSON_VALUE(@strJSON, '$.TotalAmount');
--Parse and insert the items into the SalesInvoiceItems table
    INSERT INTO SalesInvoiceItems(InvoiceNumber, ItemCode, ItemName, Quantity, UnitPrice)
SELECT
JSON_VALUE(@strJSON, '$.InvoiceNumber'),
    Items.ItemCode,
    Items.ItemName,
    Items.Quantity,
    Items.UnitPrice
    FROM OPENJSON(@strJSON, '$.Items')
WITH(
    ItemCode NVARCHAR(50),
    ItemName NVARCHAR(100),
    Quantity INT,
    UnitPrice DECIMAL(10, 2)
);
--Return a success message
    SELECT 'Success' AS Status;
END
 
 
 
CREATE PROCEDURE SP_Demo_Save
    @strJSON NVARCHAR(MAX)
AS
BEGIN
    -- Process the JSON data (for example, use OPENJSON to parse and insert)
    SELECT @strJSON -- Example: Returning the JSON for validation (you would parse and process it here)
END
 
 
 
CREATE PROCEDURE SP_Demo_Save
    @strJSON NVARCHAR(MAX)
AS
BEGIN
    -- Process the JSON data (for example, use OPENJSON to parse and insert)
    SELECT @strJSON -- Example: Returning the JSON for validation (you would parse and process it here)
 
      
DROP TABLE IF EXISTS #SalesInvoice;
SELECT *
INTO #SalesInvoice
FROM OPENJSON(@strJSON)
WITH
(
    PKId UNIQUEIDENTIFIER '$.PKId',
       NAME nvarchar(max) '$.PKId', 
    CreatedDate DATETIME '$.CreatedDate',
    IsDeleted BIT '$.IsDeleted'
);
 
SELECT * FROM #SalesInvoice
 
END