Your stored procedure is designed to insert data from a JSON string into a table called tableA.
Here's the corrected version of your stored
procedure:
Modified the
SELECT statement in the OPENJSON function to correctly map the JSON
properties to table columns.
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using System.Data;
using System.Data.SqlClient;
namespace WebApplication.Controllers
{
public class HomeController1 : Controller
{
public IActionResult Index()
{
return View();
}
// Connection string to your database
private string _connectionString = "....";
// Action method to insert data into TableB
public ActionResult
InsertIntoTableB(string json)
{
try
{
var tableBData =
JsonConvert.DeserializeObject<List<TableBModel>>(json);
using (SqlConnection connection = new
SqlConnection(_connectionString))
{
connection.Open();
foreach (var data in tableBData)
{
SqlCommand command = new SqlCommand("InsertIntoTableB", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@json", JsonConvert.SerializeObject(data));
command.ExecuteNonQuery();
}
}
ViewBag.Message = "Data inserted into TableB
successfully.";
ViewBag.Result = true;
}
catch (Exception ex)
{
ViewBag.Message = "Error inserting data
into TableB: " + ex.Message;
ViewBag.Result = false;
}
return View();
}
// Action method to insert data into TableC
public ActionResult
InsertIntoTableC(string json)
{
try
{
var tableCData =
JsonConvert.DeserializeObject<List<TableCModel>>(json);
using (SqlConnection connection = new
SqlConnection(_connectionString))
{
connection.Open();
foreach (var data in tableCData)
{
SqlCommand command = new SqlCommand("InsertIntoTableC", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@json", JsonConvert.SerializeObject(data));
command.ExecuteNonQuery();
}
}
ViewBag.Message = "Data inserted into
TableC successfully.";
ViewBag.Result = true;
}
catch (Exception ex)
{
ViewBag.Message = "Error inserting data
into TableC: " + ex.Message;
ViewBag.Result = false;
}
return View();
}
// Action method to list data from TableB
public ActionResult ListTableB(string json)
{
List<TableBModel> tableBData = new List<TableBModel>();
try
{
// Deserialize JSON input into a list of TableBModel
List<TableBModel> tableBModels =
JsonConvert.DeserializeObject<List<TableBModel>>(json);
tableBData.AddRange(tableBModels);
}
catch (Exception ex)
{
ViewBag.Message
= "Error deserializing JSON data: " + ex.Message;
}
return View(tableBData);
}
// Action method to list data from TableC
public ActionResult ListTableC(string json)
{
List<TableCModel> tableCData = new List<TableCModel>();
try
{
// Deserialize JSON input into a list of TableCModel
List<TableCModel> tableCModels =
JsonConvert.DeserializeObject<List<TableCModel>>(json);
tableCData.AddRange(tableCModels);
}
catch (Exception ex)
{
ViewBag.Message = "Error deserializing
JSON data: " + ex.Message;
}
return View(tableCData);
}
public class TableBModel
{
public Guid Id { get; set; }
public string Name { get; set; }
public bool IsDeleted { get; set; }
}
public class TableCModel
{
public Guid Id { get; set; }
public string Description { get; set; }
public bool IsDeleted { get; set; }
}
}
}
public class TableAModel
{
public Guid Id { get; set; }
public string Description { get; set; }
public bool IsDeleted { get; set; }
}
// Action method to insert and list data for all three tables
[HttpPost]
public ActionResult InsertAndList(string json)
{
try
{
// Deserialize JSON input into objects of TableAModel,
TableBModel, and TableCModel
var data =
JsonConvert.DeserializeObject<InsertionData>(json);
// Insert data into TableA
InsertIntoTableA(data.TableA);
// Insert data into TableB
InsertIntoTableB(data.TableB);
// Insert data into TableC
InsertIntoTableC(data.TableC);
// Fetch and return data from all three tables for listing
var model = new DataListingViewModel
{
TableAData = ListTableA(),
TableBData = ListTableB(),
TableCData = ListTableC()
};
return View("ListAllData",
model);
}
catch (Exception ex)
{
ViewBag.Message = "Error inserting and
listing data: " + ex.Message;
return View("Error");
}
}
// Method to insert data into TableA
private void
InsertIntoTableA(List<TableAModel> tableAData)
{
using (var connection = new
SqlConnection(_connectionString))
{
connection.Open();
foreach (var item in tableAData)
{
var command = new SqlCommand("InsertIntoTableA",
connection)
{
CommandType = CommandType.StoredProcedure
};
// Set parameters for stored procedure
command.Parameters.AddWithValue("@json", JsonConvert.SerializeObject(item));
//
Execute stored procedure
command.ExecuteNonQuery();
}
}
}
// Method to insert data into TableB
private void
InsertIntoTableB(List<TableBModel> tableBData)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
foreach (var item in tableBData)
{
var command = new SqlCommand("InsertIntoTableB",
connection)
{
CommandType = CommandType.StoredProcedure
};
// Set parameters for stored procedure
command.Parameters.AddWithValue("@json", JsonConvert.SerializeObject(item));
// Execute stored procedure
command.ExecuteNonQuery();
}
}
}
// Method to insert data into TableC
private void InsertIntoTableC(List<TableCModel>
tableCData)
{
using (var connection = new
SqlConnection(_connectionString))
{
connection.Open();
foreach (var item in tableCData)
{
var command = new SqlCommand("InsertIntoTableC",
connection)
{
CommandType = CommandType.StoredProcedure
};
// Set parameters for stored procedure
command.Parameters.AddWithValue("@json",
JsonConvert.SerializeObject(item));
// Execute stored procedure
command.ExecuteNonQuery();
}
}
}
// Method to list data from TableA
private List<TableAModel>
ListTableA()
{
var tableAData = new List<TableAModel>();
// Fetch data from TableA and populate the list
return tableAData;
}
// Method to list data from TableB
private List<TableBModel>
ListTableB()
{
var tableBData = new List<TableBModel>();
// Fetch data from TableB and populate the list
return tableBData;
}
// Method to list data from TableC
private List<TableCModel>
ListTableC()
{
var tableCData = new List<TableCModel>();
// Fetch data from TableC and populate the list
return tableCData;
}