CRUD Operation : CRUD stands for Create, Read, Update, and Delete
Here we learn how to do CRUD operation using ASP.NET MVC ,jQuery and stored procedure
step by step in easy way :
Open Visual Studio and Create a ASP.NET MVC Web Application :
I am using VS -2012 :
Click on New Project
Now you are Interact with below available Template :
Now Choose Language And Application Type :- Web >>ASP.NET MVC 4 Web Application
and Write your Application Name
And Click on Ok Button
Now ,Will open a new Window
Select Basic and click on ok Button
Now Application is creating please wait
Now Application is ready to Code :
You can see your solution folder files .
Now add a Controller
Click on , Controller folder >> Add >> Controller
Add your Controller Name and Click on Add Button
Default1 is by default Name , you can replace it appropriate name which you want.
Now your controller is ready , with by default Index Action :
Now need to add View :
Move your cursor inside the Index Action and click on right and Add View Option (you can directly add using key Ctrl+M , Ctrl+V )
Add View Name And click on Add Button :
Now your View is ready with by default html code : Modify it according to you
Now design a Student Registration form to Understand the CRUD operation Concept :
@{
Layout = null;
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Student Registration Form</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="~/Scripts/Studentdata.js"></script>
</head>
<body>
<div class="container">
<h1>Student Registration Form</h1>
<div class="mb-3">
<label for="fullName" class="form-label">Full Name</label>
<input type="text" class="form-control" id="fullName" placeholder="Enter your full name">
</div>
<div class="mb-3">
<label for="email" class="form-label">Email address</label>
<input type="email" class="form-control" id="email" placeholder="name@example.com">
</div>
<div class="mb-3">
<label for="dob" class="form-label">Date of Birth</label>
<input type="date" class="form-control" id="dob">
</div>
<div class="mb-3">
<label for="gender" class="form-label">Gender</label>
<select class="form-select" id="gender">
<option value="">Select your gender</option>
<option value="male">Male</option>
<option value="female">Female</option>
<option value="other">Other</option>
</select>
</div>
<button type="submit" id="btnsubmit" class="btn btn-primary">Submit</button>
<button type="reset" id="btnReset" class="btn btn-danger">Reset</button>
<input type="hidden" id="hfStudentId" value="0">
<br/> <br/>
<div id="dvGrid"> <!--It is used to bind the Grid-->
</div>
</div>
</body>
</html>
Now run your Application :
your UI Part is ready :
There are four Input Control which is used to take Input from student to save details :
Now need to add Code on controller to Interact With Client Request (Like To save data, Show data, edit data and delete data):
Firstly Write method which is responsible to execute procedure (We use it many times : - When data insert , and When need to Fetch data from database etc..)
Add a class And Write two Overload methods to communicate with database :
right click on models folder and Add >> Class
Now new window will open : choose class : Add your class name .
and write below methods inside the class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace Application.Models
{
public class SQLhelper
{
public static DataTable ExecuteProcedure(string[,] Param, string ExecuteProcedure)
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("ConnecTionString"));
// connection string is added insside the web.config file
SqlCommand cmd = new SqlCommand(ExecuteProcedure, con);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < Param.Length / 2; i++)
{
cmd.Parameters.AddWithValue(Param[i, 0], Param[i, 1]);
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
public static DataTable ExecuteProcedure(string ExecuteProcedure)
{
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("ConnecTionString"));
// // connection string is added insside the web.config file
SqlCommand cmd = new SqlCommand(ExecuteProcedure, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
Now add your models reference on your controller :
Let's Create Table and Procedures :
Need to create a Table "StudentDetails"
Now create procedures
- Insert Update Student Data (responsible for Insert data and Update data behalf the Student Id)
- Show Student Data (If pass the student Id then give particular student data, is used to fill the data to input control, for updating data .)
- Delete data (delete particular records from database.)
/*Create Table*/
create Table StudentDetails
(
StudentID Int Identity Constraint PK_StudentDetails_StudentID Primary Key(StudentID),
StudentName Varchar(200),
Email Varchar(50),
DOB DateTime,
Gender Varchar(20),
CreatedDate DateTime Constraint DF_StudentDetails_CreatedDate Default(GetDate()),
ModifiedBy Varchar(50),
ModifiedDate DateTime
)
Go
/*Procedure for data insert and update*/
Create proc USP_InsertUpdateStudentData
(
@StudentId Int,
@StudentName Varchar(200),
@Email Varchar(50),
@DOB DateTime,
@Gender Varchar(20)
)
As
Begin
Begin Try
Declare @result Varchar(Max)='',@Status Int
Begin Tran
IF(@StudentId<>0)
Begin
Update StudentDetails Set StudentName=@StudentName
,Email=@Email,DOB=@DOB,Gender=@Gender,
ModifiedDate=GETDATE() Where StudentID=@StudentId
Set @result='Data updated Successfully.!'
Set @Status=2
End
Else
Begin
Insert into StudentDetails(StudentName,Email,DOB,Gender,CreatedDate)
Values(@StudentName,@Email,@DOB,@Gender,GETDATE())
Set @result='Data inserted successfully.'
Set @Status=1
End
Commit Tran
End try
Begin Catch
Rollback Tran
Set @result=ERROR_MESSAGE()+' Error on Line No '+Cast(ERROR_LINE() as varchar(max));
End Catch
Select @result as [Result],@Status as [Status]
End
/*Procedure for show data and fetch particular data for update*/
Go
Create Proc USP_ShowStudentDetails
(
@StudentID Int
)
As
Begin
Declare @Query Nvarchar(Max)='',@Cond Varchar(Max)=''
If @StudentID<>0 Set @Cond=' And SD.StudentID=@StudentID'
Set @Query='Select SD.StudentID
,SD.StudentName,SD.Email
,Convert(varchar(10),sd.DOB,101) as [DOB]
,Sd.Gender
,Convert(varchar(10),sd.CreatedDate,101) as [CreatedDate]
,Convert(varchar(10),sd.ModifiedDate,101) as [ModifiedDate]
From StudentDetails SD Where 1=1'+@Cond
Exec Sys.Sp_ExecuteSql
@Stmt=@Query,
@Param=N'
@StudentID Int',
@StudentID=@StudentID;
End
/*procedure for delete data*/
Go
Create Proc USP_Delete_StudentDetails
(
@studentId Int
)
As
Begin
Declare @result Varchar(Max)='',@Status Int=0
Begin Try
Begin Tran
If Not Exists(Select StudentID from StudentDetails Where StudentID=@StudentID)
Begin
Set @result='Id does not Exists.!'
End
Else
Begin
Delete from StudentDetails Where StudentID=@StudentID
Set @Status=1
Set @result='Data deleted successfully.'
End
Commit Tran
End Try
Begin Catch
Set @result=ERROR_MESSAGE()+' Error On Line No :'+Cast(ERROR_LINE() As Varchar)
End Catch
Select @result As [result],@Status As [Status]
End
Now Connect your .NET Application with database
Open your View>> Server Explorer >> Right click on Data Connections >>Add Connections
Paste your server name :
To Get Server Name : Open your SQL Server
Copy Server Name and Paste it inside VS and Choose your DB Name
Now Your App is connected with DB :
now you can see as snippet
Now right click on highlighted portion : go to properties and copy ConnectionString
Need to add a key in web.config file for connectionString inside the appSettings section
Now create methods to manage clients request and process them accordingly (like Insert ,update, show and delete data)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using Application.Models;
using System.Text;
namespace Application.Controllers
{
public class HomeController : Controller
{
//
// GET: /Home/
public ActionResult Index()
{
return View();
}
[HttpPost]
public JsonResult InsertUpdateSudentDetails(string studentId, string studentName, string emailId,
string dob, string gender)
{
Dictionary<string, string> Dic = new Dictionary<string, string>();
Dic["Result"] = "";
Dic["Status"] = "0";
DataTable dt = new DataTable();
try
{
if (studentName.Trim() == "")
{
Dic["Result"] = "Please enter your Name.";
}
else if (emailId.Trim() == "")
{
Dic["Result"] = "Please enter your emailId.";
}
else if (dob.Trim() == "")
{
Dic["Result"] = "Please enter DOB.";
}
else if (gender.Trim() == "")
{
Dic["Result"] = "Please enter your gender";
}
else
{
string[,] Param = new string[,]
{
{"@StudentId",studentId.Trim()},
{"@StudentName",studentName.Trim()},
{"@Email",emailId.Trim()},
{"@DOB",dob},
{"@Gender",gender.Trim()},
};
dt = SQLhelper.ExecuteProcedure(Param, "USP_InsertUpdateStudentData");
if (dt.Rows.Count > 0)
{
Dic["Result"] = dt.Rows[0]["result"].ToString();
Dic["Status"] = dt.Rows[0]["Status"].ToString();
}
}
}
catch (Exception Ex)
{
Dic["Result"] = Ex.Message;
}
var jsonResult = Json(Dic, JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return jsonResult;
}
[HttpPost]
public JsonResult ShowData()
{
Dictionary<string, string> dic = new Dictionary<string, string>();
dic["Result"] = "";
dic["Grid"] = "";
StringBuilder htmlCode = new StringBuilder();
try
{
string[,] Param = new string[,]
{
{"@StudentID","0"},
};
DataTable Dt = SQLhelper.ExecuteProcedure(Param, "USP_ShowStudentDetails");
if (Dt.Rows.Count > 0)
{
htmlCode.Append("<table class='table table-responsive table-bordered table-stripped table-hover'>");
htmlCode.Append("<tr>");
htmlCode.Append("<th>Sr.No</th>");
htmlCode.Append("<th>StudentName</th>");
htmlCode.Append("<th>Email</th>");
htmlCode.Append("<th>DOB</th>");
htmlCode.Append("<th>Gender</th>");
htmlCode.Append("<th>CreatedDate</th>");
htmlCode.Append("<th>ModifedDate</th>");
htmlCode.Append("<th>Edit</th>");
htmlCode.Append("<th>Del</th>");
htmlCode.Append("</tr>");
int a=1;
foreach (DataRow rw in Dt.Rows)
{
htmlCode.Append("<tr>");
htmlCode.Append("<td>"+a+"</td>");
htmlCode.Append("<td>" + rw["StudentName"].ToString() + "</td>");
htmlCode.Append("<td>" + rw["Email"].ToString() + "</td>");
htmlCode.Append("<td>" + rw["DOB"].ToString() + "</td>");
htmlCode.Append("<td>" + rw["Gender"].ToString() + "</td>");
htmlCode.Append("<td>" + rw["CreatedDate"].ToString() + "</td>");
htmlCode.Append("<td>" + rw["ModifiedDate"].ToString() + "</td>");
htmlCode.Append("<td><i class='fa fa-edit' style='font-size: 22px;' onclick='EditData(" + rw["StudentID"].ToString() + ")'></i></td>");
htmlCode.Append("<td><i class='fa fa-trash' style='font-size: 22px;' onclick='DeleteData(" + rw["StudentID"].ToString() + ")'></i></td>");
htmlCode.Append("</tr>");
a++;
}
htmlCode.Append("</table>");
}
dic["Grid"] = htmlCode.ToString();
}
catch (Exception Ex)
{
dic["Result"] = Ex.Message;
}
var jsonResult = Json(dic, JsonRequestBehavior.AllowGet);
jsonResult.MaxJsonLength = int.MaxValue;
return jsonResult;
}
public JsonResult EditData(string StudentId)
{
Dictionary<string, string> dic = new Dictionary<string, string>();
dic["Result"] = "";
try
{
string[,] Param = new string[,]
{
{"@StudentID ",StudentId}
};
DataTable dt = SQLhelper.ExecuteProcedure(Param, "USP_ShowStudentDetails");
if (dt.Rows.Count > 0)
{
dic["StudentID"] = dt.Rows[0]["StudentID"].ToString();
dic["StudentName"] = dt.Rows[0]["StudentName"].ToString();
dic["Email"] = dt.Rows[0]["Email"].ToString();
dic["Gender"] = dt.Rows[0]["Gender"].ToString();
dic["dob"] = dt.Rows[0]["DOB"].ToString();
}
}
catch (Exception ex)
{
dic["Result"] = ex.Message;
}
return Json(dic);
}
[HttpPost]
public JsonResult DeleteData(string StudentId)
{
Dictionary<string, string> dic = new Dictionary<string, string>();
dic["Result"] = "";
try
{
string[,] Param = new string[,]
{
{"@StudentID",StudentId},
};
DataTable dt = SQLhelper.ExecuteProcedure(Param, "USP_Delete_StudentDetails");
if (dt.Rows.Count > 0)
{
dic["Result"] = dt.Rows[0]["result"].ToString();
}
}
catch (Exception ex)
{
dic["Result"] = ex.Message;
}
return Json(dic);
}
}
}
Now add jQuery code
var getUrl = window.location;
var baseUrl = getUrl.protocol + "//" + getUrl.host + "/";
$(document).ready(function () {
$("#btnsubmit").click(function () {
InsertUpdate();
})
Clear();
})
function InsertUpdate() {
try {
$.post(baseUrl + "Home/InsertUpdateSudentDetails", {
studentId: $("#hfStudentId").val(),
studentName: $("#fullName").val(),
emailId: $("#email").val(),
dob: $("#dob").val(),
gender: $("#gender").val()
}, function (data) {
if (data.Status == "1" || data.Status == "2") {
alert(data.Result);
Clear();
}
if (data.Result != "" && data.Status == "0") {
alert(data.Result);
}
});
} catch (e) {
alert("Error In method :" + e.message);
}
}
function Clear() {
$("#hfStudentId").val("0");
$("#fullName").val("");
$("#email").val("");
$("#dob").val("");
$("#gender").val("");
ShowData();
}
function ShowData() {
try {
$.post(baseUrl + "Home/ShowData", {}, function (data) {
if (data.Grid != "") {
$("#dvGrid").html(data.Grid);
} else {
$("#dvGrid").html("");
}
if (data.Result != "")
{
alert(data.Result);
}
})
} catch (e) {
alert("Error in Method " + e.message);
}
}
function EditData(studentId) {
try {
$.post(baseUrl + "Home/EditData", {
StudentId: studentId
}, function (data) {
debugger;
if (data.Result == "") {
$("#hfStudentId").val(data.StudentID);
$("#fullName").val(data.StudentName);
$("#email").val(data.Email);
var dateParts = data.dob.split('/');
var formattedDate = dateParts[2] + '-' + dateParts[0] + '-' + dateParts[1];
$("#dob").val(formattedDate);
$("#gender").val(data.Gender);
} else if (data.Result != "")
{
alert(data.Result);
}
})
} catch (e) {
alert("Error in Method " + e.message);
}
}
function DeleteData(studentId) {
try {
if (confirm("are you sure"))
{
$.post(baseUrl + "Home/DeleteData", {
StudentId: studentId
}, function (data) {
if (data.Result != "") {
alert(data.Result);
Clear();
}
})
}
} catch (e) {
alert("Error in Method " + e.message);
}
}
Now your CRUD is ready !.
If you have any query then simply drop a message our team you revert ASAP Thanks.