Create Database and its Tables
CREATE Database Ramanujan;
GO
Use Ramanujan;
GO
CREATE TABLE dbo.Employees
(
EmployeeID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Employees PRIMARY KEY,
Name VARCHAR(50),
Phone VARCHAR(50),
Email VARCHAR(50),
Country VARCHAR(50)
);
GO
CREATE PROCEDURE InsertTable_Employees
@Name VARCHAR(50),
@Phone VARCHAR(50),
@Email VARCHAR(50),
@Country VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN
INSERT INTO Employees (Name, Phone, Email, Country)
VALUES (@Name, @Phone, @Email, @Country);
END
END;
GO
Create ASP.NET Core application using Empty template.
Modify Program.cs file.
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllersWithViews();
var app = builder.Build();
// Configure the HTTP request pipeline.
if (!app.Environment.IsDevelopment())
{
app.UseExceptionHandler("/Home/Error");
}
app.UseStaticFiles();
app.UseRouting();
app.UseAuthorization();
app.MapControllerRoute(
name: "default",
pattern: "{controller=Home}/{action=Index}/{id?}");
app.Run();
Create Person model class.
using System.Text.Json.Serialization;
namespace JsonDataApp.Models;
public class Person
{
[JsonPropertyName("name")]
public string Name { get; set; } = string.Empty;
[JsonPropertyName("phone")]
public string Phone { get; set; } = string.Empty;
[JsonPropertyName("email")]
public string Email { get; set; } = string.Empty;
[JsonPropertyName("country")]
public string Country { get; set; } = string.Empty;
}
The following code contains RunStoredProcedure method that can be used to call the stored procedure - InsertTable_Employees - which inserts a record into Employees table.
using Microsoft.Data.SqlClient;
using System.Data;
namespace JsonDataApp.Data;
public class DatabaseOperations
{
const string connString = "Server=DESKTOP-PUI1MDR\\SQLEXPRESS;Database=DiwaliGift;Integrated Security=True;TrustServerCertificate=True;";
public int RunStoredProcedure(string name, string phone, string email, string country)
{
string commandText = "InsertTable_Employees";
using (SqlConnection sqlConnection = new SqlConnection(connString))
{
using (SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
// Add typed parameters with explicit sizes matching the database schema
sqlCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = (object?)name ?? DBNull.Value;
sqlCommand.Parameters.Add("@Phone", SqlDbType.VarChar, 50).Value = (object?)phone ?? DBNull.Value;
sqlCommand.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = (object?)email ?? DBNull.Value;
sqlCommand.Parameters.Add("@Country", SqlDbType.VarChar, 50).Value = (object?)country ?? DBNull.Value;
sqlConnection.Open();
int rowsAffected = sqlCommand.ExecuteNonQuery();
return rowsAffected;
}
}
}
}
The following code loads data from JSON file into Database table:
using JsonDataApp.Models;
using System.Text.Json;
namespace JsonDataApp.Data
{
public class JsonEmployeeImporter
{
private readonly DatabaseOperations _dbOps = new DatabaseOperations();
public int ImportPersonsFromJson(string webRootPath)
{
// Build the absolute path to wwwroot/record/persons.json
string filePath = Path.Combine(webRootPath, "record", "persons.json");
if (!File.Exists(filePath))
{
throw new FileNotFoundException($"File not found at: {filePath}");
}
// Read and deserialize the JSON
string jsonContent = File.ReadAllText(filePath);
var options = new JsonSerializerOptions { PropertyNameCaseInsensitive = true };
List<Person>? persons = JsonSerializer.Deserialize<List<Person>>(jsonContent, options);
if (persons == null || persons.Count == 0)
{
return 0;
}
int totalInserted = 0;
// Iterate and insert each person
foreach (var person in persons)
{
_dbOps.RunStoredProcedure(
person.Name,
person.Phone,
person.Email,
person.Country
);
totalInserted++;
}
return totalInserted;
}
}
}
using JsonDataApp.Data;
using Microsoft.AspNetCore.Mvc;
namespace JsonDataApp.Controllers;
[ApiController]
[Route("api/[controller]")]
public class ImportController : ControllerBase
{
private readonly IWebHostEnvironment _env;
public ImportController(IWebHostEnvironment env)
{
_env = env;
}
[HttpPost("load-persons")]
public IActionResult LoadPersons()
{
var importer = new JsonEmployeeImporter();
int count = importer.ImportPersonsFromJson(_env.WebRootPath);
return Ok(new { message = $"Successfully processed {count} records." });
}
}
Open Postman to call HTTP POST request at LoadPersons action method.
https://localhost:5001/api/import/load-personsYou can even create MVC controller to insert a record.
using JsonDataApp.Data;
using JsonDataApp.Models;
using Microsoft.AspNetCore.Mvc;
using System.Diagnostics;
namespace JsonDataApp.Controllers
{
public class HomeController : Controller
{
public IActionResult Index()
{
return View();
}
[HttpPost]
public IActionResult Index(Person person)
{
if (ModelState.IsValid)
{
string name = person.Name;
string phone = person.Phone;
string email = person.Email;
string country = person.Country;
DatabaseOperations operation = new DatabaseOperations();
int result = operation.RunStoredProcedure(name, phone, email, country);
ViewData["rowsAffected"] = result;
}
return View();
}
}
}
The related Form to insert a record:
@model JsonDataApp.Models.Person
@{
ViewData["Title"] = "Index";
}
<h3>Insert record</h3>
@ViewData["rowsAffected"]
<hr />
<div class="row">
<div class="col-md-4">
<form asp-action="Index">
<div asp-validation-summary="ModelOnly" class="text-danger"></div>
<div class="form-group">
<label asp-for="Name" class="control-label"></label>
<input asp-for="Name" class="form-control" />
<span asp-validation-for="Name" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Phone" class="control-label"></label>
<input asp-for="Phone" class="form-control" />
<span asp-validation-for="Phone" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Email" class="control-label"></label>
<input asp-for="Email" class="form-control" />
<span asp-validation-for="Email" class="text-danger"></span>
</div>
<div class="form-group">
<label asp-for="Country" class="control-label"></label>
<input asp-for="Country" class="form-control" />
<span asp-validation-for="Country" class="text-danger"></span>
</div>
<div class="form-group">
<input type="submit" value="Create" class="btn btn-primary" />
</div>
</form>
</div>
</div>
<div>
<a asp-action="Index">Back to List</a>
</div>