In this article, we’ll implement CRUD operations in Asp.Net MVC with jQuery Ajax Using JSON without Entity Framework.
Tools used for the development of the project are as below:
- Visual Studio 2019
- MSSQL Database
Final Tutorial Product – Images
Project Source Code
You can download this project source code from here : https://bit.ly/3NCRoep
Create Asp.Net MVC Project
First of all, let’s create an Asp.net MVC Project, Open your Visual Studio 2019 and perform the process below:
Go to File > New > Project.
Give the project a name as ASPDotNetMVCWithJQuery, Then select MVC Template. and hit the Create Project button.
Now we have a brand new Asp.Net MVC Application. Before continuing with this project, we need to create a database backend that the application will talk to in the process of making the crud application possible.
Create a Database
In this application, we’ll deal with student details. Therefore a Student table is created using following SQL Script.
First create a Database called StudentDB then run the script below in a new query window of MSSQL Management Studio
USE [StudentDB]
GO
/****** Object: Table [dbo].[Student] Script Date: 03/07/2022 11:56:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Age] [int] NULL,
[RegistrationDate] [datetime] NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
The created Database view will be visible as per the below screenshot.
Add MVC Controller
Right click on Controllers folder, Add > Controller. Select Empty MVC Template, then name your controller as StudentController. Currently this Student controller has one action method Index. In-order to implement CRUD Operations, we’ll add following action methods in Student Controller.
public class StudentController : Controller
{
// GET: Student
public ActionResult Index()
{
return View();
}
}
Index – Display other action method views using tab controller. Like Home/ Dashboard for the controller.
GetData – Return an HTML table, to list records from Student table.
HttpGet AddOrEdit – Should return a view containing form for Insert and Update Operation. If the method parameter id is zero, we’ll return form for Insert Operation otherwise It will be form to update a student with StudentID is equal to given id parameter.
HttpPost AddOrEdit – Form returned from HttpGet AddOrEdit will be posted to this action method. Inside this action method, Insert Or Update Operation will be done based on StudentID.
HttpPost Delete – Used to Delete an student record with given EmployeeID through id parameter.
Add Database Connection to the Application
We first add a database connection to the application we just created in the Web.Config File and then reference it in our controller as below
<connectionStrings>
<add name="studentDBConn" connectionString="Data Source={Database Name};Initial Catalog=StudentDB;Persist Security Info=True;User ID={Database UserName};Password={Database Password}"
providerName="System.Data.SqlClient" />
</connectionStrings>Initialize Database Connection in Controller.
The next step is to initialize the connection string within the controller before the Index endpoint as below.
//shared database connections.
string studentDBConn = ConfigurationManager.ConnectionStrings["studentDBConn"].ConnectionString;We also need to make the StudentController Default when the page loads or as the first landing section. For that you can update RouteConfig as follows.
public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Student", action = "Index", id = UrlParameter.Optional }
);
}
}Create Model for Expected Student Data
The model will have the necessary properties that relate to the student such as Name and Age
The student model is as below:
public class Student
{
public int StudentID { get; set; }
[Required(ErrorMessage = "FirstName is a required Field")]
public string FirstName { get; set; }
[Required(ErrorMessage = "LastName is a required Field")]
public string LastName { get; set; }
[Required(ErrorMessage = "Age is Required")]
[Range(1, int.MaxValue, ErrorMessage = "The age must be greater than 0")]
public int Age { get; set; }
[Required(ErrorMessage = "Registration Date is a required Field")]
//[DisplayFormat(DataFormatString = "{0:yyyy/MM/dd hh:mm tt}", ApplyFormatInEditMode = true)]
public DateTime RegistrationDate { get; set; }
}Front End Application Design
In the global style sheet – Site.css inside Content folder, additional CSS was added to help with better presentation on MVC validation errors as below.
.field-validation-error {
color: #e80c4d;
font-weight: bold;
}
input.input-validation-error {
border: 1px solid #e80c4d;
}
We will use two jQuery plugins inside this application, they are jQuery Datatable and NotifyJS. Datatable plugin is used to add more useful functions to a normal HTML table. In-order to add NotifyJS Plugin, you can go to the plugin website – NotifyJs, then download minified java script file – notify.min.js. Add this script file inside Scripts folder.
After this installation the final Layout Page( _Layout.cshtml ) for our Application will look like the below:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - My ASP.NET Application</title>
@Styles.Render("~/Content/css")
@Scripts.Render("~/bundles/modernizr")
<link href="~/Content/Site.css" rel="stylesheet" />
<link href="~/Content/themes/base/jquery-ui.min.css" rel="stylesheet" />
<link href="~/Content/bootstrap-datetimepicker.min.css" rel="stylesheet" />
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.5.0/css/all.css" integrity="sha384-B4dIYHKNBt8Bc12p+WXckhzcICo0wtJAoU8YZTY5qE0Id1GSseTk6S+L3BlXeVIU" crossorigin="anonymous">
</head>
<body>
<div class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
@Html.ActionLink("Student Portal", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
<li>@Html.ActionLink("Students", "Index", "Student")</li>
</ul>
</div>
</div>
</div>
<div class="container body-content">
@RenderBody()
<hr />
<footer>
<p>© @DateTime.Now.Year - Asp.Net MVC CRUD Operations</p>
</footer>
</div>
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/bootstrap")
<script src="~/Scripts/jquery-ui-1.12.1.min.js"></script>
<script src="~/Scripts/moment.min.js"></script>
<script src="~/Scripts/bootstrap-datetimepicker.min.js"></script>
<script src="~/Scripts/notify.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
@RenderSection("scripts", required: false)
</body>
</html>
Load All Students from Database
To load all students from the database we first need to the GetData endpoint in the student Controller that will be called as a JQuery Ajax Expression to load all specified details as seen in the final output screen.
The final Output is as below For the GetData endpoint is as below
public ActionResult GetData()
{
var student = new Student();
List<Student> StudentList = new List<Student>();
try
{
using (con = new SqlConnection(studentDBConn))
{
SqlCommand cmd = new SqlCommand("GetStudentData", con);
cmd.CommandType = CommandType.StoredProcedure;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
//18 / 05 / 2021 01:10:00
SqlDataAdapter sd = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sd.Fill(dt);
CultureInfo culture = new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
StudentList.Add(
new Student
{
StudentID = Convert.ToInt32(dr["StudentID"]),
FirstName = Convert.ToString(dr["FirstName"]),
LastName = Convert.ToString(dr["LastName"]),
Age = Convert.ToInt32(dr["Age"]),
//RegistrationDate = DateTime.Parse(Convert.ToString(dr["RegistrationDate"]))
//RegistrationDate = JsonConvert.DeserializeObject<DateTime>(dr["RegistrationDate"].ToString())
RegistrationDate = Convert.ToDateTime(dr["RegistrationDate"].ToString()),
});
}
}
}
catch (Exception ex)
{
ErrHandler.WriteLog(ex.Message, "GETSTUDENTDATA");
}
finally
{
//close connection.
con.Close();
}The linking view for this endpoint is the Index view, which is also our default view. The view contains ajax expressions to manage the CRUD functionalities including Delete, Edit and Update
@{
ViewBag.Title = "Student List";
}
<h2>Student Management</h2>
<a href="#" class="btn btn-success" style="margin-bottom:10px" onclick="PopupForm('@Url.Action("AddOrEdit","Student")')">
<i class="fa fa-plus" aria-hidden="true"></i> Add New </a>
<table id="studentTableData" class="table table-striped table-bordered" style="width:100%">
<thead>
<tr>
<th>FirstName</th>
<th>LastName</th>
<th>Age</th>
<th>Registration Date</th>
<th></th>
</tr>
</thead>
</table>
<!--Datatables css styling-->
<link href="https://cdn.datatables.net/1.12.0/css/dataTables.bootstrap.min.css" rel="stylesheet" />
@section scripts {
<script src="https://cdn.datatables.net/1.12.0/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.12.0/js/dataTables.bootstrap.min.js"></script>
<script>
var Popup, dataTable;
$(document).ready(function () {
$("#studentTableData").DataTable({
"ajax": {
"url": "/Student/GetData",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "FirstName" },
{ "data": "LastName" },
{ "data": "Age" },
{
"data": "RegistrationDate",
"render": function (data) {
var pattern = /Date\(([^)]+)\)/;
var results = pattern.exec(data);
var dt = new Date(parseFloat(results[1])).toLocaleString("en-US", { dateStyle: "medium", timeStyle: "short", hourCycle: 'h12' });
return dt;
}
},
{"data":"StudentID" , "render" : function (data) {
return "<a class='btn btn-primary btn-sm' onclick=PopupForm('@Url.Action("AddOrEdit","Student")/" + data + "')><i class='fas fa-edit'></i> Edit</a><a class='btn btn-danger btn-sm' style='margin-left:5px' onclick=Delete(" + data + ")><i class='fa fa-trash'></i> Delete</a>";
},
"orderable": false,
"searchable": false,
"width": "150px"
}
],
"language": {
"emptyTable": "No data found, Please click on <b>Add New</b> Button"
}
});
});
function PopupForm(url) {
var formDiv = $('<div/>');
$.get(url)
.done(function (response) {
formDiv.html(response);
Popup = formDiv.dialog({
autoOpen: true,
resizable: false,
title: 'Fill Student Details',
height: 500,
width: 500,
close: function () {
Popup.dialog('destroy').remove();
}
});
});
}
function SubmitForm(form) {
$.validator.unobtrusive.parse(form);
if ($(form).valid()) {
$.ajax({
type: "POST",
url: form.action,
data: $(form).serialize(),
success: function (data) {
if (data.success) {
Popup.dialog('close');
//reload the datatable
$('#studentTableData').DataTable().ajax.reload()
$.notify(data.message, {
globalPosition: "top center",
className: "success"
})
}
}
});
}
return false;
}
function Delete(id) {
if(confirm('Are You Sure to Delete this Employee Record ?'))
{
$.ajax({
type: "POST",
url: '@Url.Action("Delete","Student")/'+ id,
success: function (data) {
if (data.success)
{
//reload the datatable
$('#studentTableData').DataTable().ajax.reload()
//display the notify message.
$.notify(data.message, {
globalPosition: "top center",
className: "success"
})
}
}
});
}
}
</script>
}Final Student Controller
The Student controller has additional endpoints to handle the reminder of the CRUD functions, which include the below extract for the whole StudentController
The points to note is that the procedures included in the code below are part of the full source code already uploaded to github for you to access and follow along.
The Procedures include: GetStudentData(to get all students from the database), GetStudentDataById(Get details of a specific student), InsertStudentData(Insert a new student in the database), UpdateStudentData( Update details of a specific student), DeleteStudentData(Delete details of a specific student)
The Full path to controller in project is: ~\Controllers\StudentController.cs
public class StudentController : Controller
{
//shared database connections.
string studentDBConn = ConfigurationManager.ConnectionStrings["studentDBConn"].ConnectionString;
SqlConnection con;
Response response = new Response();
// GET: Student
public ActionResult Index()
{
return View();
}
public ActionResult GetData()
{
var student = new Student();
List<Student> StudentList = new List<Student>();
try
{
using (con = new SqlConnection(studentDBConn))
{
SqlCommand cmd = new SqlCommand("GetStudentData", con);
cmd.CommandType = CommandType.StoredProcedure;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
//18 / 05 / 2021 01:10:00
SqlDataAdapter sd = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sd.Fill(dt);
CultureInfo culture = new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
StudentList.Add(
new Student
{
StudentID = Convert.ToInt32(dr["StudentID"]),
FirstName = Convert.ToString(dr["FirstName"]),
LastName = Convert.ToString(dr["LastName"]),
Age = Convert.ToInt32(dr["Age"]),
//RegistrationDate = DateTime.Parse(Convert.ToString(dr["RegistrationDate"]))
//RegistrationDate = JsonConvert.DeserializeObject<DateTime>(dr["RegistrationDate"].ToString())
RegistrationDate = Convert.ToDateTime(dr["RegistrationDate"].ToString()),
});
}
}
}
catch (Exception ex)
{
ErrHandler.WriteLog(ex.Message, "GETSTUDENTDATA");
}
finally
{
//close connection.
con.Close();
}
//return StudentList;
return Json(new { data = StudentList }, JsonRequestBehavior.AllowGet);
}
[HttpGet]
public ActionResult AddOrEdit(int id = 0)
{
List<Student> studentList = new List<Student>();
var student = new Student();
if (id == 0)
return View(new Student());
else
{
try
{
using (con = new SqlConnection(studentDBConn))
{
SqlCommand cmd = new SqlCommand("GetStudentDataById", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@StudID", id));
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr == null)
{
return HttpNotFound();
}
else
{
while (rdr.Read())
{
student.FirstName = rdr["FirstName"].ToString();
student.LastName = rdr["LastName"].ToString();
student.Age = Convert.ToInt32(rdr["Age"]);
student.RegistrationDate = DateTime.Parse(rdr["RegistrationDate"].ToString());
student.StudentID = id;
studentList.Add(student);
}
}
con.Close();
}
}
catch (Exception ex)
{
ErrHandler.WriteLog(ex.Message, "GETSTUDENTDATA");
}
finally
{
//close connection.
con.Close();
}
return View(student);
//return Json(new { success = "success", studentList }, JsonRequestBehavior.AllowGet);
}
}
[HttpPost]
public ActionResult AddOrEdit(Student std)
{
try
{
//cheeck for new student
if (std.StudentID == 0)
{
using (con = new SqlConnection(studentDBConn))
{
SqlCommand cmd = new SqlCommand("InsertStudentData", con);
cmd.Parameters.Add(new SqlParameter("@FirstName", std.FirstName));
cmd.Parameters.Add(new SqlParameter("@LastName", std.LastName));
cmd.Parameters.Add(new SqlParameter("@Age", std.Age));
cmd.Parameters.Add(new SqlParameter("@RegistrationDate", std.RegistrationDate));
cmd.CommandType = CommandType.StoredProcedure;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataReader rdr = cmd.ExecuteReader();
}
//formulate the response message
response.Status = true;
response.Message = "Saved Successfully";
}
//update existing student.
else
{
using (con = new SqlConnection(studentDBConn))
{
SqlCommand cmd = new SqlCommand("UpdateStudentData", con);
cmd.Parameters.Add(new SqlParameter("@StudentID", std.StudentID));
cmd.Parameters.Add(new SqlParameter("@FirstName", std.FirstName));
cmd.Parameters.Add(new SqlParameter("@LastName", std.LastName));
cmd.Parameters.Add(new SqlParameter("@Age", std.Age));
cmd.Parameters.Add(new SqlParameter("@RegistrationDate", std.RegistrationDate));
cmd.CommandType = CommandType.StoredProcedure;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataReader rdr = cmd.ExecuteReader();
}
//formulate the response message
response.Status = true;
response.Message = "Updated Successfully";
}
}
catch (Exception ex)
{
ErrHandler.WriteLog(ex.Message, "INSERTSTUDENTDATA");
}
finally
{
//close connection.
con.Close();
}
return Json(new { success = response.Status, message = response.Message }, JsonRequestBehavior.AllowGet);
}
[HttpPost]
public ActionResult Delete(int id)
{
try
{
using (con = new SqlConnection(studentDBConn))
{
SqlCommand cmd = new SqlCommand("DeleteStudentData", con);
cmd.Parameters.Add(new SqlParameter("@StudentID", id));
cmd.CommandType = CommandType.StoredProcedure;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataReader rdr = cmd.ExecuteReader();
}
//formulate the response message
response.Status = true;
response.Message = "Deleted Successfully";
}
catch (Exception ex)
{
ErrHandler.WriteLog(ex.Message, "DELETESTUDENTDATA");
}
return Json(new { success = response.Status, message = response.Message }, JsonRequestBehavior.AllowGet);
}
}Final Student CRUD View
The final Student View to handle all the CRUD Functions is as below.
The view can be traced with the path as : ~\Views\Student\AddOrEdit.cshtml
@model MVCOps.Models.Student
@{
Layout = null;
}
@using (Html.BeginForm("AddOrEdit", "Student", FormMethod.Post, new { onsubmit = "return SubmitForm(this)" }))
{
@Html.HiddenFor(model => model.StudentID)
<div class="form-group">
@Html.LabelFor(model => model.FirstName, new { @class = "control-label" })
@Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.FirstName)
</div>
<div class="form-group">
@Html.LabelFor(model => model.LastName, new { @class = "control-label" })
@Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.LastName)
</div>
<div class="form-group">
@Html.LabelFor(model => model.Age, new { @class = "control-label" })
@Html.EditorFor(model => model.Age, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Age, "", new { @class = "text-danger" })
</div>
@*<div class="form-group">
@Html.LabelFor(model => model.RegistrationDate, htmlAttributes: new { @class = "control-label" })
@Html.EditorFor(model => model.RegistrationDate, new { htmlAttributes = new { @class = "form-control", type = "datetime-local"} })
@Html.ValidationMessageFor(model => model.RegistrationDate, "", new { @class = "text-danger" })
</div>*@
<div class="container">
<div class="row col-md-12">
<div >
<div class="form-group">
<span style="margin:0px;"> @Html.Label("Registration Date", htmlAttributes: new { @class = "control-label" })</span>
<div class='input-group date col-sm-3' id='datetimepicker1'>
@Html.EditorFor(model => model.RegistrationDate, "{0:yyyy/MM/dd hh:mm tt}", new { htmlAttributes = new { @class = "form-control" } })
<span class="input-group-addon">
<span class="glyphicon glyphicon-calendar"></span>
</span>
@Html.ValidationMessageFor(model => model.RegistrationDate, "", new { @class = "text-danger" })
</div>
</div>
</div>
</div>
</div>
<div class="form-group">
<input type="submit" value="Submit" class="btn btn-primary" />
<input type="reset" value="Reset" class="btn" />
</div>
}
<script type="text/javascript">
$(function () {
$('#datetimepicker1').datetimepicker({
});
});
</script>Incase you need to understand how to manage the project you created, please follow the link How to use Git commands in Azure DevOps
Hello Shaik
Sorry about that I forgot to include it in the repository. Will send you later today. I will update you once done.
Hello Shaik
Please redownload the repository
The data has been updated as per the linkSQL Data Procedures