CRUD Operation using Stored Procedure :-
Stored procedure is used to contain set of SQL quires to provide database operation ,It is pre-compile block of code which will be directly executed when we call it, it provide better performance as compare to SQL query hence we mostly prefer Procedure in place of SQL Query.
Code of Store Procedure:-
First Create Table Student (Rno,Sname,Branch ,.Fees) Column
Procedure for Data Insertion :-
CREATE PROCEDURE [dbo].sp_add_student(@rno int,@sname varchar(50),@branch varchar(50),@fees int)
AS
insert into student(Rno,Sname,Branch,Fees) values(@rno,@sname,@branch,@fees)
Procedure for Data Selection :-
CREATE PROCEDURE [dbo].[sp_get_student]
AS
SELECT * from Student
Procedure for Data Updation :-
CREATE PROCEDURE [dbo].sp_update_student(@rno int,@sname varchar(50),@branch varchar(50),@fees int)
AS
update student set Sname=@sname,Branch=@Branch,Fees=@Fees where Rno=@rno
Procedure for Data Deletion:-
CREATE PROCEDURE [dbo].sp_delete_student(@rno int)
AS
delete from student where Rno=@rno
Complete Code of Controller:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using StoredProcedureExample.Models;
namespace StoredProcedureExample.Controllers
{
public class StudentController : Controller
{
private Database1Entities db = new Database1Entities();
//
// GET: /Student/
public ActionResult Index()
{
return View(db.sp_get_student().ToList());
}
//
// GET: /Student/Details/5
public ActionResult Details(int id = 0)
{
Student student = db.Students.Find(id);
if (student == null)
{
return HttpNotFound();
}
return View(student);
}
//
// GET: /Student/Create
public ActionResult Create()
{
return View();
}
//
// POST: /Student/Create
[HttpPost]
public ActionResult Create(Student student)
{
if (ModelState.IsValid)
{
// db.Students.Add(student);
//db.SaveChanges();
db.sp_add_student(student.Rno, student.Sname, student.Branch, student.Fees);
return RedirectToAction("Index");
}
return View(student);
}
//
// GET: /Student/Edit/5
public ActionResult Edit(int id = 0)
{
Student student = db.Students.Find(id);
if (student == null)
{
return HttpNotFound();
}
return View(student);
}
//
// POST: /Student/Edit/5
[HttpPost]
public ActionResult Edit(Student student)
{
if (ModelState.IsValid)
{
// db.Entry(student).State = EntityState.Modified;
//db.SaveChanges();
db.sp_update_student(student.Rno, student.Sname, student.Branch, student.Fees);
return RedirectToAction("Index");
}
return View(student);
}
//
// GET: /Student/Delete/5
public ActionResult Delete(int id = 0)
{
Student student = db.Students.Find(id);
if (student == null)
{
return HttpNotFound();
}
return View(student);
}
//
// POST: /Student/Delete/5
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
// Student student = db.Students.Find(id);
// db.Students.Remove(student);
// db.SaveChanges();
db.sp_delete_student(id);
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
db.Dispose();
base.Dispose(disposing);
}
}
}
Code of index.cshtml:-
@model IEnumerable<StoredProcedureExample.Models.sp_get_student_Result>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table>
<tr>
<th>
@Html.DisplayNameFor(model => model.Rno)
</th>
<th>
@Html.DisplayNameFor(model => model.Sname)
</th>
<th>
@Html.DisplayNameFor(model => model.Branch)
</th>
<th>
@Html.DisplayNameFor(model => model.Fees)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Rno)
</td>
<td>
@Html.DisplayFor(modelItem => item.Sname)
</td>
<td>
@Html.DisplayFor(modelItem => item.Branch)
</td>
<td>
@Html.DisplayFor(modelItem => item.Fees)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.Rno }) |
@Html.ActionLink("Details", "Details", new { id=item.Rno }) |
@Html.ActionLink("Delete", "Delete", new { id=item.Rno })
</td>
</tr>
}
</table>
Stored procedure is used to contain set of SQL quires to provide database operation ,It is pre-compile block of code which will be directly executed when we call it, it provide better performance as compare to SQL query hence we mostly prefer Procedure in place of SQL Query.
Code of Store Procedure:-
First Create Table Student (Rno,Sname,Branch ,.Fees) Column
Procedure for Data Insertion :-
CREATE PROCEDURE [dbo].sp_add_student(@rno int,@sname varchar(50),@branch varchar(50),@fees int)
AS
insert into student(Rno,Sname,Branch,Fees) values(@rno,@sname,@branch,@fees)
Procedure for Data Selection :-
CREATE PROCEDURE [dbo].[sp_get_student]
AS
SELECT * from Student
Procedure for Data Updation :-
CREATE PROCEDURE [dbo].sp_update_student(@rno int,@sname varchar(50),@branch varchar(50),@fees int)
AS
update student set Sname=@sname,Branch=@Branch,Fees=@Fees where Rno=@rno
Procedure for Data Deletion:-
CREATE PROCEDURE [dbo].sp_delete_student(@rno int)
AS
delete from student where Rno=@rno
Complete Code of Controller:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using StoredProcedureExample.Models;
namespace StoredProcedureExample.Controllers
{
public class StudentController : Controller
{
private Database1Entities db = new Database1Entities();
//
// GET: /Student/
public ActionResult Index()
{
return View(db.sp_get_student().ToList());
}
//
// GET: /Student/Details/5
public ActionResult Details(int id = 0)
{
Student student = db.Students.Find(id);
if (student == null)
{
return HttpNotFound();
}
return View(student);
}
//
// GET: /Student/Create
public ActionResult Create()
{
return View();
}
//
// POST: /Student/Create
[HttpPost]
public ActionResult Create(Student student)
{
if (ModelState.IsValid)
{
// db.Students.Add(student);
//db.SaveChanges();
db.sp_add_student(student.Rno, student.Sname, student.Branch, student.Fees);
return RedirectToAction("Index");
}
return View(student);
}
//
// GET: /Student/Edit/5
public ActionResult Edit(int id = 0)
{
Student student = db.Students.Find(id);
if (student == null)
{
return HttpNotFound();
}
return View(student);
}
//
// POST: /Student/Edit/5
[HttpPost]
public ActionResult Edit(Student student)
{
if (ModelState.IsValid)
{
// db.Entry(student).State = EntityState.Modified;
//db.SaveChanges();
db.sp_update_student(student.Rno, student.Sname, student.Branch, student.Fees);
return RedirectToAction("Index");
}
return View(student);
}
//
// GET: /Student/Delete/5
public ActionResult Delete(int id = 0)
{
Student student = db.Students.Find(id);
if (student == null)
{
return HttpNotFound();
}
return View(student);
}
//
// POST: /Student/Delete/5
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
// Student student = db.Students.Find(id);
// db.Students.Remove(student);
// db.SaveChanges();
db.sp_delete_student(id);
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
db.Dispose();
base.Dispose(disposing);
}
}
}
Code of index.cshtml:-
@model IEnumerable<StoredProcedureExample.Models.sp_get_student_Result>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table>
<tr>
<th>
@Html.DisplayNameFor(model => model.Rno)
</th>
<th>
@Html.DisplayNameFor(model => model.Sname)
</th>
<th>
@Html.DisplayNameFor(model => model.Branch)
</th>
<th>
@Html.DisplayNameFor(model => model.Fees)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Rno)
</td>
<td>
@Html.DisplayFor(modelItem => item.Sname)
</td>
<td>
@Html.DisplayFor(modelItem => item.Branch)
</td>
<td>
@Html.DisplayFor(modelItem => item.Fees)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.Rno }) |
@Html.ActionLink("Details", "Details", new { id=item.Rno }) |
@Html.ActionLink("Delete", "Delete", new { id=item.Rno })
</td>
</tr>
}
</table>
Remaining .cshtml code will be same
POST Answer of Questions and ASK to Doubt