CRUD Operation Using Procedure
Follow on GitHub:
Until now we have been using Entity Framework For CRUD Operation. Entities mapped automatically to Database table and Object (By Using Entity Framework). Now here we are going to use Manual Mapping(Business Object) instead of Entity Framework. Business Object Contain both State(data) and behavior(Method), that is Logic Specific to the Business. Here in this Article, we will create CRUD Operation Using SQL PROCEDURE.
Follow the following steps in order to implement “Part 2: CRUD Operation Using PROCEDURE.”.
Step-1 : Create Following tables in SQL Server.
Here I have Created 1 tables – 1) table that you are watching in the imag is TblEmployee Table. 2) Second Image is the Output that we want to Achieve.
Click To Link for Database Code- database code.
Step-2) create a New ASPMVC4 Application Named “CrudOperationUsingProcedure”
Step -3) Add A Class (Employee.cs)To map Table with Application.
Go to Solution Explorer -> Right Click on Models Folder ->Add -> Class ->Write the Name of class “Employee.cs”.
public class Employee {
public int ID { get; set; }
[Required(ErrorMessage=”Name is Required“)]
public string Name { get; set; }
[Required(ErrorMessage = “Gender is Required“)]
public string Gender { get; set; }
[Required(ErrorMessage = “City is Required“)]
public string City { get; set; }
[DataType(DataType.Date)]
[Required(ErrorMessage = “Date is Required“)]
public DateTime DateOfBirth { get; set; }
}
Step-4) Create Procedure(in SQL Server) to Fetch All Records of Table.
Step-5) You have to create a Custom Model class in Models Folder to Fetch All Records
Go to Solution Explorer -> Right Click on Models Folder ->Add -> Class ->Write the Name of class “BusinessLayer”.
Click To Link for Database Code- database code.
Step-2) create a New ASPMVC4 Application Named “CrudOperationUsingProcedure”
Go to Solution Explorer -> Right Click on Models Folder ->Add -> Class ->Write the Name of class “Employee.cs”.
public class Employee {
public int ID { get; set; }
}
[Required(ErrorMessage=”Name is Required“)]
public string Name { get; set; }
[Required(ErrorMessage = “Gender is Required“)]
public string Gender { get; set; }
[Required(ErrorMessage = “City is Required“)]
public string City { get; set; }
[DataType(DataType.Date)]
[Required(ErrorMessage = “Date is Required“)]
public DateTime DateOfBirth { get; set; }
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;using System.Data.Entity;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace CrudOperationUsingProcedure.Models
{
public class BusinessLayer
{
SqlConnection con;
public BusinessLayer()
{
Connection();
}
private void Connection()
{
string connectionString = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
con = new SqlConnection(connectionString);
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
con.Open();
}
public IEnumerable<Employee> Employees
{
get
{
BusinessLayer b = new BusinessLayer();
List<Employee> employees = new List<Employee>();
SqlCommand cmd = new SqlCommand(“spGetAllEmployee”,con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee emp = new Employee();
emp.ID = Convert.ToInt32(rdr[“ID”]);
emp.Name = rdr[“Name”].ToString();
emp.Gender = rdr[“Gender”].ToString();
emp.City = rdr[“City”].ToString();
emp.DateOfBirth = Convert.ToDateTime(rdr[“DateofBirth”]);
employees.Add(emp);
}
return employees;
}
}
}
Step 6) Now Create a Controller Named “HomeController“
Go to Solution Explorer -> Right Click on Controllers Folder ->Add -> Controller ->Write the Name of Controller “HomeController”.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CrudOperationUsingProcedure.Models;
namespace CrudOperationUsingProcedure.Controllers
{
public class HomeController : Controller
{
BusinessLayer db;
public ActionResult Index()
{
db = new BusinessLayer();
return View(db.Employees.ToList());
}
}
}
Step 7): Add Connection String in Web.Config .
<connectionStrings>
<add name=”DBCS” connectionString=”server=DHANANJAY\SQLEXPRESS;
Database=DemoDatabase;
User Id=sa;Password=1”
providerName=”System.Data.SqlClient“/>
</connectionStrings>
Step 8): Now Create a View Page To Render Details
Go to Solution Explorer -> Right Click on Index Action Method ->Add View (“Index.cshtml“)->Check Strongly Type view(Employee.cs) using List Template
@model IEnumerable<CrudOperationUsingProcedure.Models.Employee>
@{
ViewBag.Title = “Index”;
}
<h2>Index</h2>
<p>
@Html.ActionLink(“Create New“, “Create“)
</p>
<table>
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Gender)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.DateOfBirth)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Gender)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.DisplayFor(modelItem => item.DateOfBirth)
</td>
<td>
@Html.ActionLink(“Edit“, “Edit“, new { id=item.ID }) |
@Html.ActionLink(“Details“, “Details“, new { id=item.ID }) |
@Html.ActionLink(“Delete“, “Delete“, new { id=item.ID })
</td>
</tr>
}
</table>
Now We Are going to Add Employee in Database.Follow these Steps to Create Operation
Step: 1) Update Your Business Model “BusinessLayer” . Add Following Code to Add Employee.
public void AddEmployee(Employee E)
{
SqlCommand cmd = new SqlCommand(“spAddEmployee”,con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paraName = new SqlParameter();
paraName.ParameterName = “@Name”;
paraName.Value = E.Name;
cmd.Parameters.Add(“@Name”,E.Name);
SqlParameter paraGender = new SqlParameter();
paraGender.ParameterName = “@Gender”;
paraGender.Value = E.Gender;
cmd.Parameters.Add(paraGender);
SqlParameter paraCity = new SqlParameter();
paraCity.ParameterName = “@City”;
paraCity.Value = E.City;
cmd.Parameters.Add(paraCity);
SqlParameter paraDateofBirth = new SqlParameter();
paraDateofBirth.ParameterName = “@DateofBirth”;
paraDateofBirth.Value = E.DateOfBirth;
cmd.Parameters.Add(paraDateofBirth);
cmd.ExecuteNonQuery();
}
Step: 2) Update Your Controller(HomeController) Add Following Code:
[HttpGet]
public ActionResult Create()
{
return View();
}
[HttpPost]
public string Create(Employee E)
{
new BusinessLayer().AddEmployee(E);
return string.Format(“<script>alert(‘Employee Added </script>‘);location.assign(‘/Home/Create’);”);
}
Step: 3) Add View Page(“Create.cshtml” , Strongly type Employee.cs) For Create Method.
@model CrudOperationUsingProcedure.Models.Employee
@{
ViewBag.Title = “Create”;
}
<h2>Create</h2>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Employee</legend>
< div class =”editor-label”>
@Html.LabelFor(model => model.Name)
< /div>
< div class =”editor-field”>
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
< /div>
< div class =”editor-label”>
@Html.LabelFor(model => model.Gender)
< /div>
< div class =”editor-field”>
@Html.RadioButtonFor(model => model.Gender, “Male”) Male
@Html.RadioButtonFor(model=>model.Gender,”Female”) Female
@Html.ValidationMessageFor(model => model.Gender)
< /div>
< div class =”editor-label”>
@Html.LabelFor(model => model.City)
</div>
< div class =”editor-field”>
@Html.EditorFor(model => model.City)
@Html.ValidationMessageFor(model => model.City)
< /div>
< div class =”editor-label“>
@Html.LabelFor(model => model.DateOfBirth)
< /div>
< div class =”editor-field“>
@Html.EditorFor(model => model.DateOfBirth)
@Html.ValidationMessageFor(model => model.DateOfBirth)
< /div>
<p>
<input type =”submit” value=”Create” />
</p>
</fieldset>
}
@Html.ActionLink(“Back to List”, “Index”)
@section Scripts {
@Scripts.Render(“~/bundles/jqueryval”)
}
Now We Are going to Update Employee in Database.Follow these Steps to Edit Operation
Step: 1) Update Your Business Model “BusinessLayer”. Add Following Code to Update Employee.
public void SaveEmployee(Employee E)
{
SqlCommand cmd = new SqlCommand(“spSaveEmployee”,con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paraID = new SqlParameter();
paraID.ParameterName = “@Id”;
paraID.Value = E.ID;
cmd.Parameters.Add(“@ID”, E.ID);
SqlParameter paraName = new SqlParameter();
paraName.ParameterName = “@Name”;
paraName.Value = E.Name;
cmd.Parameters.Add(“@Name”,E.Name);
SqlParameter paraGender = new SqlParameter();
paraGender.ParameterName = “@Gender”;
paraGender.Value = E.Gender;
cmd.Parameters.Add(“@Gender”,E.Gender);
SqlParameter paraCity = new SqlParameter();
paraCity.ParameterName = “@City”;
paraCity.Value = E.City;
cmd.Parameters.Add(“@City”,E.City);
SqlParameter paraDate = new SqlParameter();
paraDate.ParameterName = “@DateOfBirth”;
paraDate.Value = E.DateOfBirth;
cmd.Parameters.Add(“@DateOfBirth”,E.DateOfBirth);
cmd.ExecuteNonQuery();
}
Step: 2) Update Your Controller(HomeController) Add Following Code:
[HttpGet]
public ActionResult Edit(int id)
{
return View(new BusinessLayer().Employees.Single(x=>x.ID==id));
}
[HttpPost]
public string Edit(Employee E)
{
new BusinessLayer().SaveEmployee(E);
return string.Format(“alert(‘Employee Updated ‘);location.assign(‘/Home/Index’);”);
}
Step: 3) Add View Page(“Edit.cshtml” , Strongly type Employee.cs) For Create Method.
@model CrudOperationUsingProcedure.Models.Employee
@{
ViewBag.Title = “Edit”;
}
h2>Edit</h2>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Employee</legend>
@Html.HiddenFor(model => model.ID)
@Html.LabelFor(model => model.Name)
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
@Html.LabelFor(model => model.Gender)
@Html.DropDownListFor(model => model.Gender, new SelectList(new [] {“Male”, “Female”}),Model.Gender)
@Html.ValidationMessageFor(model => model.Gender)
@Html.LabelFor(model => model.City)
@Html.EditorFor(model => model.City)
@Html.ValidationMessageFor(model => model.City)
@Html.LabelFor(model => model.DateOfBirth)
@Html.EditorFor(model => model.DateOfBirth) @Model.DateOfBirth
@Html.ValidationMessageFor(model => model.DateOfBirth)
<p>
<input type=”submit” value=”Save” />
</p>
</fieldset>
}
@Html.ActionLink(“Back to List”, “Index”)
@section Scripts {
@Scripts.Render(“~/bundles/jqueryval”)
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;using System.Data.Entity;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace CrudOperationUsingProcedure.Models
{
public class BusinessLayer
{
SqlConnection con;
public BusinessLayer()
{
Connection();
}
private void Connection()
{
string connectionString = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
con = new SqlConnection(connectionString);
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
con.Open();
}
public IEnumerable<Employee> Employees
{
get
{
BusinessLayer b = new BusinessLayer();
List<Employee> employees = new List<Employee>();
SqlCommand cmd = new SqlCommand(“spGetAllEmployee”,con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee emp = new Employee();
emp.ID = Convert.ToInt32(rdr[“ID”]);
emp.Name = rdr[“Name”].ToString();
emp.Gender = rdr[“Gender”].ToString();
emp.City = rdr[“City”].ToString();
emp.DateOfBirth = Convert.ToDateTime(rdr[“DateofBirth”]);
employees.Add(emp);
}
return employees;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CrudOperationUsingProcedure.Models;
namespace CrudOperationUsingProcedure.Controllers
{
public class HomeController : Controller
{
BusinessLayer db;
public ActionResult Index()
{
db = new BusinessLayer();
return View(db.Employees.ToList());
}
}
}
<connectionStrings>
<add name=”DBCS” connectionString=”server=DHANANJAY\SQLEXPRESS;
Database=DemoDatabase;
User Id=sa;Password=1”
providerName=”System.Data.SqlClient“/>
</connectionStrings>
@model IEnumerable<CrudOperationUsingProcedure.Models.Employee>
@{
ViewBag.Title = “Index”;
}
<h2>Index</h2>
<p>
@Html.ActionLink(“Create New“, “Create“)
</p>
<table>
<tr>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Gender)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.DateOfBirth)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Gender)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.DisplayFor(modelItem => item.DateOfBirth)
</td>
<td>
@Html.ActionLink(“Edit“, “Edit“, new { id=item.ID }) |
@Html.ActionLink(“Details“, “Details“, new { id=item.ID }) |
@Html.ActionLink(“Delete“, “Delete“, new { id=item.ID })
</td>
</tr>
}
</table>
public void AddEmployee(Employee E)
{
SqlCommand cmd = new SqlCommand(“spAddEmployee”,con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paraName = new SqlParameter();
paraName.ParameterName = “@Name”;
paraName.Value = E.Name;
cmd.Parameters.Add(“@Name”,E.Name);
SqlParameter paraGender = new SqlParameter();
paraGender.ParameterName = “@Gender”;
paraGender.Value = E.Gender;
cmd.Parameters.Add(paraGender);
SqlParameter paraCity = new SqlParameter();
paraCity.ParameterName = “@City”;
paraCity.Value = E.City;
cmd.Parameters.Add(paraCity);
SqlParameter paraDateofBirth = new SqlParameter();
paraDateofBirth.ParameterName = “@DateofBirth”;
paraDateofBirth.Value = E.DateOfBirth;
cmd.Parameters.Add(paraDateofBirth);
cmd.ExecuteNonQuery();
}
[HttpGet]
public ActionResult Create()
{
return View();
}
[HttpPost]
public string Create(Employee E)
{
new BusinessLayer().AddEmployee(E);
return string.Format(“<script>alert(‘Employee Added </script>‘);location.assign(‘/Home/Create’);”);
}
@model CrudOperationUsingProcedure.Models.Employee
@{
ViewBag.Title = “Create”;
}
<h2>Create</h2>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Employee</legend>
< div class =”editor-label”>
@Html.LabelFor(model => model.Name)
< /div>
< div class =”editor-field”>
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
< /div>
< div class =”editor-label”>
@Html.LabelFor(model => model.Gender)
< /div>
< div class =”editor-field”>
@Html.RadioButtonFor(model => model.Gender, “Male”) Male
@Html.RadioButtonFor(model=>model.Gender,”Female”) Female
@Html.ValidationMessageFor(model => model.Gender)
< /div>
< div class =”editor-label”>
@Html.LabelFor(model => model.City)
</div>
< div class =”editor-field”>
@Html.EditorFor(model => model.City)
@Html.ValidationMessageFor(model => model.City)
< /div>
< div class =”editor-label“>
@Html.LabelFor(model => model.DateOfBirth)
< /div>
< div class =”editor-field“>
@Html.EditorFor(model => model.DateOfBirth)
@Html.ValidationMessageFor(model => model.DateOfBirth)
< /div>
<p>
<input type =”submit” value=”Create” />
</p>
</fieldset>
}
@Html.ActionLink(“Back to List”, “Index”)
@section Scripts {
@Scripts.Render(“~/bundles/jqueryval”)
}
public void SaveEmployee(Employee E)
{
SqlCommand cmd = new SqlCommand(“spSaveEmployee”,con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paraID = new SqlParameter();
paraID.ParameterName = “@Id”;
paraID.Value = E.ID;
cmd.Parameters.Add(“@ID”, E.ID);
SqlParameter paraName = new SqlParameter();
paraName.ParameterName = “@Name”;
paraName.Value = E.Name;
cmd.Parameters.Add(“@Name”,E.Name);
SqlParameter paraGender = new SqlParameter();
paraGender.ParameterName = “@Gender”;
paraGender.Value = E.Gender;
cmd.Parameters.Add(“@Gender”,E.Gender);
SqlParameter paraCity = new SqlParameter();
paraCity.ParameterName = “@City”;
paraCity.Value = E.City;
cmd.Parameters.Add(“@City”,E.City);
SqlParameter paraDate = new SqlParameter();
paraDate.ParameterName = “@DateOfBirth”;
paraDate.Value = E.DateOfBirth;
cmd.Parameters.Add(“@DateOfBirth”,E.DateOfBirth);
cmd.ExecuteNonQuery();
}
[HttpGet]
public ActionResult Edit(int id)
{
return View(new BusinessLayer().Employees.Single(x=>x.ID==id));
}
[HttpPost]
public string Edit(Employee E)
{
new BusinessLayer().SaveEmployee(E);
return string.Format(“alert(‘Employee Updated ‘);location.assign(‘/Home/Index’);”);
}
@model CrudOperationUsingProcedure.Models.Employee
@{
ViewBag.Title = “Edit”;
}
h2>Edit</h2>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Employee</legend>
@Html.HiddenFor(model => model.ID)
@Html.LabelFor(model => model.Name)
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
@Html.LabelFor(model => model.Gender)
@Html.DropDownListFor(model => model.Gender, new SelectList(new [] {“Male”, “Female”}),Model.Gender)
@Html.ValidationMessageFor(model => model.Gender)
@Html.LabelFor(model => model.City)
@Html.EditorFor(model => model.City)
@Html.ValidationMessageFor(model => model.City)
@Html.LabelFor(model => model.DateOfBirth)
@Html.EditorFor(model => model.DateOfBirth) @Model.DateOfBirth
@Html.ValidationMessageFor(model => model.DateOfBirth)
<p>
<input type=”submit” value=”Save” />
</p>
</fieldset>
}
@Html.ActionLink(“Back to List”, “Index”)
@section Scripts {
@Scripts.Render(“~/bundles/jqueryval”)
}
Now We Are going to Delete Employee FromDatabase.Follow these Steps to Delete Operation
Step: 1) Update Your Business Model “BusinessLayer”. Add Following Code to Update Employee.
public void DeleteEmployee(int id)
{
SqlCommand cmd = new SqlCommand(“spDeleteEmployee”, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter Paraid = new SqlParameter();
Paraid.ParameterName = “@ID”;
Paraid.Value = id;
cmd.Parameters.Add(Paraid);
cmd.ExecuteNonQuery();
}
Step: 2) Update Your Controller(HomeController) Add Following Code:
public ActionResult Delete(int id)
{
return View(new BusinessLayer().Employees.Single(x=>x.ID==id));
}
[HttpPost]
public string Delete(Employee E)
{
new BusinessLayer().DeleteEmployee(E.ID);
return string.Format(“alert(‘Employee Deleted ‘);location.assign(‘/Home/Index’);”);
}
Step: 3) Add View Page(“Edit.cshtml” , Strongly type Employee.cs) For Create Method.
@model CrudOperationUsingProcedure.Models.Employee
@{
ViewBag.Title = “Delete”;
}
<h2>Delete</h2>
<h3>Are you sure you want to delete this?</h3>
<fieldset>
<legend>Employee</legend>
@Html.DisplayNameFor(model => model.Name)
@Html.DisplayFor(model => model.Name)
@Html.DisplayNameFor(model => model.Gender)
@Html.DisplayFor(model => model.Gender)
@Html.DisplayNameFor(model => model.City)
@Html.DisplayFor(model => model.City)
@Html.DisplayNameFor(model => model.DateOfBirth)
@Html.DisplayFor(model => model.DateOfBirth)
</fieldset>
@using (Html.BeginForm()) {
<p>
<input type=”submit” value=”Delete” /> |
@Html.ActionLink(“Back to List”, “Index”)
</p>
}
Step: 1) Update Your Business Model “BusinessLayer”. Add Following Code to Update Employee.
public void DeleteEmployee(int id)
{
SqlCommand cmd = new SqlCommand(“spDeleteEmployee”, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter Paraid = new SqlParameter();
Paraid.ParameterName = “@ID”;
Paraid.Value = id;
cmd.Parameters.Add(Paraid);
cmd.ExecuteNonQuery();
}
public ActionResult Delete(int id)
{
return View(new BusinessLayer().Employees.Single(x=>x.ID==id));
}
[HttpPost]
public string Delete(Employee E)
{
new BusinessLayer().DeleteEmployee(E.ID);
return string.Format(“alert(‘Employee Deleted ‘);location.assign(‘/Home/Index’);”);
}
@model CrudOperationUsingProcedure.Models.Employee
@{
ViewBag.Title = “Delete”;
}
<h2>Delete</h2>
<h3>Are you sure you want to delete this?</h3>
<fieldset>
<legend>Employee</legend>
@Html.DisplayNameFor(model => model.Name)
@Html.DisplayFor(model => model.Name)
@Html.DisplayNameFor(model => model.Gender)
@Html.DisplayFor(model => model.Gender)
@Html.DisplayNameFor(model => model.City)
@Html.DisplayFor(model => model.City)
@Html.DisplayNameFor(model => model.DateOfBirth)
@Html.DisplayFor(model => model.DateOfBirth)
</fieldset>
@using (Html.BeginForm()) {
<p>
<input type=”submit” value=”Delete” /> |
@Html.ActionLink(“Back to List”, “Index”)
</p>
}
