In one of the previous article, I have mentioned CRUD using jQGrid in ASP.NET MVC, in this article, we will be creating a system for CRUD (Create, read, Update , Delete) operations in ASP.NET MVC with Inline Editing ,Deleting, & Updating data using Validations & connecting data to database without Entity Framework, we will be using direct SQL Query to Save/Update to database using Stored procedures.
If you are looking for CRUD using Entity Framework in MVC, here is the article for it: Performing CRUD Operation in ASP.NET MVC Using Entity Framework
So here are the details, step by step:
1 .Create a new project in your Visual Studio(File-> New->Project-> From web(Left pane)-> Select "Asp.Net Web project"(right pane)) .
a) Enter a Name, Click "Ok"
b) Select "MVC" template from project and click "Ok"
2. Now, for example:I have this tables in my database with following rows , which we need to update.
Here, there are two tables tblCustomer and tblProduct having one-to-many relationship among them.[CustStatus & ProdStatus table columns are false by default for active products].
you can create this table in your local database by executing the below script :
-//Create Statement :
Create table tblCustomer
(
CustId bigint not null primary key,
CustName nvarchar(max) not null,
CustStatus bit not null Default(0)
)
Create table tblProduct
(
ProdId bigint not null primary key,
CustId bigint not null,
ProdName nvarchar(max) not null,
ProdPrice bigint not null,
ProdStatus bit not null Default(0)
)
--//Insert Statement :
insert into tblCustomer values(1,'Snehal',0)
insert into tblCustomer values(2,'Sanvi',0)
insert into tblCustomer values(3,'Priyu',0)
insert into tblCustomer values(4,'Dinesh',0)
insert into tblCustomer values(5,'Praful',0)
insert into tblProduct values(1,1,'TV',50000,0)
insert into tblProduct values(2,2,'Laptop',35000,0)
insert into tblProduct values(3,3,'Mobile',20000,0)
insert into tblProduct values(4,4,'Car',50000,0)
insert into tblProduct values(5,5,'Chair',500,0)3. For this project , you need to include following styles and scripts :
Navigate to Home-> Index View and add below styles and scripts :
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet" type="text/css" />
<link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap3-dialog/1.34.5/css/bootstrap-dialog.min.css" rel="stylesheet" type="text/css" />
<script src="https://code.jquery.com/jquery-2.1.4.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap3-dialog/1.34.5/js/bootstrap-dialog.min.js"></script>4.To display existing data form two tables using join : [active products only(not deleted)]
a) create below Stored Procedure in SQL :
Create Procedure GetCustomerDetails
As
Begin
select Cust.CustId,CustName,ProdId,ProdName,ProdPrice from tblCustomer Cust
inner join tblProduct Prod
on Cust.CustId = Prod.CustId
where Prod.ProdStatus = 0
End
b) create CustomerProductVM under Models Folder for keeping tblProduct & tblCUstomer data in one object :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace InplaceEditing.Models
{
public class CustomerProductVM
{
public long CustId { get; set; }
public string CustName { get; set; }
public long ProdId { get; set; }
public long ProdPrice { get; set; }
public string ProdName { get; set; }
}
}c) add below code to HomeController to display data in view :
using InplaceEditing.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace InplaceEditing.Controllers
{
public class HomeController : Controller
{
Customer objCust = new Customer();
Product objProd = new Product();
public ActionResult Index()
{
List<CustomerProductVM> lstCust = GetCustomerDetails();
return View(lstCust);
}
public List<CustomerProductVM> GetCustomerDetails()
{
List<Customer> lstCust = new List<Customer>();
List<Product> lstProd = new List<Product>();
List<CustomerProductVM> lst = new List<CustomerProductVM>();
using (SqlConnection con = new SqlConnection("Data Source=HPLAPTOP;Integrated Security=true;Initial Catalog=SNE"))
{
using (SqlCommand cmd = new SqlCommand("GetCustomerDetails", con))
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CustomerProductVM obj = new CustomerProductVM();
obj.CustId = dr["CustId"] != DBNull.Value ? Convert.ToInt64(dr["CustId"]) : 0;
obj.CustName = dr["CustName"] != DBNull.Value ? Convert.ToString(dr["CustName"]) : string.Empty;
obj.ProdId = dr["ProdId"] != DBNull.Value ? Convert.ToInt64(dr["ProdId"]) : 0;
obj.ProdPrice = dr["ProdPrice"] != DBNull.Value ? Convert.ToInt64(dr["ProdPrice"]) : 0;
obj.ProdName = dr["ProdName"] != DBNull.Value ? Convert.ToString(dr["ProdName"]) : string.Empty;
lst.Add(obj);
}
dr.Close();
con.Close();
}
}
return lst;
}
in the above code we are fetching values from database using ADO.NET reading the result set
and returning data of two tables to only one list using CustomerProductVM ViewModel.
d.add below code to Home-> Index View to display data like this :
@using InplaceEditing.Models
@model List<CustomerProductVM>
@{
ViewBag.Title = "Home Page";
Layout = null;
Int64 newcustID = 0;
Int64 newprodID = 0;
}
<div class="mtop20">
<input type="button" class="btn btn-primary" id="btnAddProd" value="Add New Product" onclick="AddRowProductData();" />
</div>
<div class="mtop20">
<table id="tblProdinfo" class="table table-bordered table-hover">
<thead>
<tr>
<th id="cname">Customer Name</th>
<th id="pname" class="hideCol hideHideCol_2">Product Name</th>
<th id="pprice" class="hideCol hideHideCol_3">Product Price</th>
<th>Action</th>
</tr>
</thead>
<tbody>
@if (Model.Count() > 0)
{
for (int i = 0; i < Model.Count; i++)
{
newcustID = @Model[i].CustId;
newprodID = @Model[i].ProdId;
<tr id="Tr_@Model[i].ProdId.ToString()">
<td id="tdCName_@Model[i].ProdId.ToString()">@Model[i].CustName</td>
<td id="tdPName_@Model[i].ProdId.ToString()">@Model[i].ProdName</td>
<td id="tdPPrice_@Model[i].ProdId.ToString()">@Model[i].ProdPrice</td>
<td id="txtCName_@Model[i].ProdId.ToString()" style="display:none;"><input type='text' id="txtCName_@Model[i].ProdId.ToString()" value="@Model[i].CustName" /></td>
<td id="txtPName_@Model[i].ProdId.ToString()" style="display:none;"><input type='text' id="txtPName_@Model[i].ProdId.ToString()" value="@Model[i].ProdName" /></td>
<td style="display:none;" id="txtPPrice_@Model[i].ProdId.ToString()"><input type='text' id="txtPPrice_@Model[i].ProdId.ToString()" value="@Model[i].ProdPrice" /></td>
<td>
<input type="button" class="btn btn-primary" id="btnEditProd_@Model[i].ProdId.ToString()" value="Edit" onclick="editRowProductData(@Model[i].ProdId.ToString())" />
<input type="button" class="btn btn-primary" id="btnUpdateProd_@Model[i].ProdId.ToString()" value="Update" style="display:none;" onclick="saveProdData(@Model[i].ProdId.ToString(),@Model[i].CustId.ToString())" />
<input type="button" class="btn btn-primary" id="btnDeleteProd_@Model[i].ProdId.ToString()" value="Delete" onclick="deleteProdData(@Model[i].CustId.ToString(),@Model[i].ProdId.ToString())" />
<input type="button" class="btn btn-primary" value="Cancel" onclick="removeProdData(@Model[i].ProdId)" style="display:none;" id="btnCancelProd_@Model[i].ProdId.ToString()" />
</td>
</tr>
}
}
else
{
<tr>
</tr>
}
</tbody>
</table>
</div>
in the above code , we are displaying values to user in table format using CustomerProductVM ViewModel.
Here ,there is existing data ,so Edit and Delete Buttons are displayed to user at starting
e.On click of Edit Button ,output will be like below:
Particular table TR changes to input type text for each column in db as :
<td id="txtCName_@Model[i].ProdId.ToString()" style="display:none;">
<input type='text' id="txtCName_@Model[i].ProdId.ToString()" value="@Model[i].CustName" />
</td>Similarly for other columns and Update and Cancel Buttons are shown.
For every table row unique id is provided as :<tr id="Tr_@Model[i].ProdId.ToString()">
In script tag, add below code for hiding and showing respective buttons :
<script>
function editRowProductData(i) {
$("#tblProdinfo").find('#btnEditProd_' + i.toString()).hide();
$("#tblProdinfo").find('#btnDeleteProd_' + i.toString()).hide();
$("#tblProdinfo").find('#tdCName_' + i.toString()).hide();
$("#tblProdinfo").find('#tdPName_' + i.toString()).hide();
$("#tblProdinfo").find('#tdPPrice_' + i.toString()).hide();
$("#tblProdinfo").find('#btnUpdateProd_' + i.toString()).show();
$("#tblProdinfo").find('#btnCancelProd_' + i.toString()).show();
$("#tblProdinfo").find('#txtCName_' + i.toString()).show();
$("#tblProdinfo").find('#txtPName_' + i.toString()).show();
$("#tblProdinfo").find('#txtPPrice_' + i.toString()).show();
}
function removeProdData(i) {
$("#tblProdinfo").find('#btnEditProd_' + i.toString()).show();
$("#tblProdinfo").find('#btnDeleteProd_' + i.toString()).show();
$("#tblProdinfo").find('#tdCName_' + i.toString()).show();
$("#tblProdinfo").find('#tdPName_' + i.toString()).show();
$("#tblProdinfo").find('#tdPPrice_' + i.toString()).show();;
$("#tblProdinfo").find('#btnUpdateProd_' + i.toString()).hide();
$("#tblProdinfo").find('#btnCancelProd_' + i.toString()).hide();
$("#tblProdinfo").find('#txtCName_' + i.toString()).hide();
$("#tblProdinfo").find('#txtPName_' + i.toString()).hide();
$("#tblProdinfo").find('#txtPPrice_' + i.toString()).hide();
}
</script>
5. If we do any changes in data , on click of Cancel button changes will get undo and previous data will be shown in format of table TR.
6. On click of delete button , data will be deleted in db : (ProdStatus & CustStatus will change to 1
a.create below Stored Procedures in SQL to delete data :
Create Procedure DeleteProdDetails
@ProdId bigint,
@CustId bigint
As
Begin
update tblProduct
set [ProdStatus] = 1
where CustId = @CustId and ProdId = @ProdId
update tblCustomer
set [CustStatus] = 1
where CustId = @CustId
End
After deleting particular data , CustStatus & ProdStatus will change to 1 (i.e.false) in db.
b. add below code for deleting data from the database :
HomeController :
public JsonResult DeleteProdDetails(string prodId, string custId)
{
if (!string.IsNullOrEmpty(prodId) && !string.IsNullOrEmpty(custId))
{
using (SqlConnection con = new SqlConnection("Data Source=HPLAPTOP;Integrated Security=true;Initial Catalog=SNE"))
{
using (SqlCommand cmd = new SqlCommand("DeleteProdDetails", con))
{
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("ProdId", prodId);
cmd.Parameters.AddWithValue("CustId", custId);
cmd.ExecuteNonQuery();
con.Close();
}
}
}
return Json(new { status = "Success"});
}
In above code, DeleteProdDetails sp is called and returning JSONResult that is passed to ajax call used in deleteProdData() function.
c). We will using below script to call DeleteProdDetails ActionMethod, So in you Home -> Index View use the Script Tag :
function deleteProdData(i, custid) {
$.ajax({
url: "/Home/DeleteProdDetails",
type: "POST",
data: { prodId: i, custId: custid },
success: function (data) {
if (data.status == "Success") {
BootstrapDialog.show({
title: 'Success!',
message: "Data Deleted Successfully!",
buttons: [{
label: 'OK',
action: function (dialog) {
window.location.href = "/Home/Index";
dialog.close();
}
}]
});
}
}
});
}
In above code,In deleteProdData() function : ProductID & CUstomerID are passed respectively to delete particular row data .
After clicking on ‘OK’ button of dialog box data will be updated to list using : window.location.href = "/Home/Index";
7 . add below code for Validations for Existing Data & Newly Added Data using Bootstrap :
Here are the scripts which we are using to validate the form values:
function validateProdForm(i) {
var flag = true;
var msg = "";
var CustName = $("#tblProdinfo").find("input[id=txtCName_" + i.toString() + "]").val();
var ProdName = $("#tblProdinfo").find("input[id=txtPName_" + i.toString() + "]").val();
var ProdPrice = $("#tblProdinfo").find("input[id=txtPPrice_" + i.toString() + "]").val();
if (CustName == "") {
msg += "Customer Name at level " + i + " is required. " + "\n";
$("#txtCName_" + i.toString()).val('');
flag = false;
}
if (ProdName == "") {
msg += "Product Name at level " + i + " is required. " + "\n";
$("#txtPName_" + i.toString()).val('');
flag = false;
}
if (ProdPrice == "") {
msg += "Product Price at level " + i + " is required. " + "\n";
$("#txtPPrice_" + i.toString()).val('');
flag = false;
}
if (flag == false) {
BootstrapDialog.show({
title: 'Warning!',
message: msg
});
}
return flag;
}
in the above code , checking the blank values (consider flag == true initially) for each field and showing error messages respectively using :
BootstrapDialog.show({title: 'Warning!' message: msg });
8 . To Save Updated Data in DB and newly added data to db :
a). create below Stored Procedures in SQL to save data in tblCustomer & tblProduct
[ here Insert & Update statements are used in only one sp ].
Create Procedure Save_CustomerData
@CustId bigint,
@CustName nvarchar(max)
As
Begin
if not exists (select * from tblCustomer where CustId = @CustId)
Begin
insert into tblCustomer values(@CustId,@CustName,0)
End
Else
Begin
update tblCustomer
set CustName = @CustName
where CustId = @CustId
End
End
GO
Create Procedure Save_ProductData
@CustId bigint,
@CustName nvarchar(max),
@ProdId bigint,
@ProdName nvarchar(max),
@ProdPrice bigint
--@CustStatus bit
As
Begin
if not exists (select * from tblProduct where CustId = @CustId and ProdId = @ProdId)
Begin
insert into tblProduct values(@CustId,@ProdId,@ProdName,@ProdPrice,0)
End
Else
Begin
update tblProduct
set ProdName = @ProdName,
ProdPrice = @ProdPrice
where CustId = @CustId and ProdId = @ProdId
End
End
GO
b. By clicking on add New product button , new row is appended to existing table :
c.add below code in Script tag :
globally declared variable to increment id as we add new row :
as newprodID & newcustID are defined in Index View :
var incrprodID = @newprodID +1;
var incrcustID = @newcustID + 1;
<script>
function AddRowProductData() {
console.log("incrprodID :" + incrprodID);
var html =
'<tr id ="Tr_' + incrprodID + '"> '
+ '<td id="txtCName_' + incrprodID + '"><input type="text" id="txtCName_' + incrprodID + '" value="" /></td>'
+ '<td id="txtPName_' + incrprodID + '"><input type="text" id="txtPName_' + incrprodID + '" value="" /></td>'
+ '<td id="txtPPrice_' + incrprodID + '"><input type="text" id="txtPPrice_' + incrprodID + '" value="" /></td>'
+ '<td >'
+ '<input type="button" class="btn btn-primary" id="btnUpdateProd_' + incrprodID + '" value="Update" onclick="saveProdData(' + incrprodID + ',' + incrcustID + ')"/>'
+ '<input type="button" class="btn btn-primary" id="btnCancelProd_' + incrprodID + '" value="Cancel" onclick="removeRow(this)";/>'
+ '</td>'
+ '</tr>';
$(html).appendTo($("#tblProdinfo"));
$("#btnAddProd").attr("disabled", true);
}
</script>In above script , using html string appending new input type field to existing table and add new product button is disabled till data of newly added row is saved in db.
I am adding one row at the time .
d.Here for newly added row :on clicking of cancel button , row will be deleted as using below code in script Tag :
function removeRow(obj) {
console.log("removerow");
var tr = $(obj).closest('tr');
tr.remove();
$("#btnAddProd").attr("disabled", false);
};
Obj means particular table tr which we want to remove.
e.For Newly added row, validations are done by using same function validateProdForm(i) as above.
add below code for saving edited data and newly added data to database in Home -> Index View under Script Tag :
On click of Update Button , data is updated to database using following function :
<script>
function saveProdData(i, custid) {
if (validateProdForm(i)) {
var txtcustName = $("#tblProdinfo").find("input[id=txtCName_" + i.toString() + "]").val();
var prodIDd = i;
var txtprodName = $("#tblProdinfo").find("input[id=txtPName_" + i.toString() + "]").val();
var txtprodPrice = $("#tblProdinfo").find("input[id=txtPPrice_" + i.toString() + "]").val();
var txthdnCustId = custid;
console.log("txtprodPrice : " + txtprodPrice);
var item = {};
var jsonObj = [];
item["CustId"] = txthdnCustId;
item["CustName"] = txtcustName;
item["ProdId"] = prodIDd;
item["ProdName"] = txtprodName;
item["ProdPrice"] = txtprodPrice;
jsonObj.push(item);
console.log("jsonObj : " + jsonObj);
var jsonString = JSON.stringify(jsonObj);
console.log("jsonString : " + jsonString);
$.ajax({
url: "/Home/SaveDataToDB",
type: "POST",
data: { custId: txthdnCustId, dataToSend: jsonString, custName: txtcustName },
success: function (data) {
if (data.status == "Success") {
BootstrapDialog.show({
title: 'Success!',
message: "Data Updated Successfully!",
buttons: [{
label: 'OK',
action: function (dialog) {
window.location.href = "/Home/Index";
removeProdData(i);
$("#btnAddProd").attr("disabled",false);
dialog.close();
}
}]
});
}
}
});
}
}
</script>
first check blank values for form , if there is no blank value make the AjaxCall.
getting all table rows data in Json Format by creating two arrays and pushing each row value to them and finally make Ajax call to save data in db.
On click of OK button,data is updated to list in tablet format by calling :
window.location.href = "/Home/Index";
removeProdData(i);on success of ajax call as follows :
g. add below code in HomeController :
public JsonResult SaveDataToDB(string custId, string dataToSend, string custName)
{
long _CustId = 0;
if (!string.IsNullOrEmpty(custId) && !string.IsNullOrEmpty(custName))
{
long.TryParse(custId, out _CustId);
}
using (SqlConnection con = new SqlConnection("Data Source=HPLAPTOP;Integrated Security=true;Initial Catalog=SNE"))
{
using (SqlCommand cmd = new SqlCommand("Save_CustomerData", con))
{
if (!string.IsNullOrEmpty(custId) && !string.IsNullOrEmpty(custName))
{
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("CustId", custId);
cmd.Parameters.AddWithValue("CustName", custName);
cmd.ExecuteNonQuery();
con.Close();
}
}
if (!string.IsNullOrEmpty(dataToSend))
{
List<CustomerProductVM> _List = Newtonsoft.Json.JsonConvert.DeserializeObject<List<CustomerProductVM>>(dataToSend);
for (int i = 0; i < _List.Count; i++)
{
using (SqlCommand cmd = new SqlCommand("Save_ProductData", con))
{
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("CustId", _List[i].CustId);
cmd.Parameters.AddWithValue("CustName", _List[i].CustName);
cmd.Parameters.AddWithValue("ProdId", _List[i].ProdId);
cmd.Parameters.AddWithValue("ProdName", _List[i].ProdName);
cmd.Parameters.AddWithValue("ProdPrice", _List[i].ProdPrice);
cmd.ExecuteNonQuery();
con.Close();
}
}
}
return Json(new { status = "Success" });
}
}
}
in the above code , SaveDataToDB() function returning JSONMessage
There are 3 parameters : CustId ,CustName for tblCustomer & dataToSend for tblProduct.
Below code is used to deserialize the whole Page Data to object (it is like converting jsonstring to .net object)
List<CustomerProductVM> _List = Newtonsoft.Json.JsonConvert.DeserializeObject<List<CustomerProductVM>>(dataToSend);
And calling two stored procedures to save data in tblCustomer , tblProduct simultaneously.
h.For newly added row , data is saved in db using :
SaveDataToDB(string custId, string dataToSend, string custName) from Controller and saveProdData(i, custid) from script tag
Data is updated in the database as shown below
We can edit & delete the newly added row from database as explained above in the baove steps.
That's it we are done, Thank you for reading.
You may also like to read:
ASP.NET MVC tutorial part-2 connecting to database (reading and saving data)
Buy us a coffee
Become a Patron