Java, .NET, PHP, PYTHON, ANGULAR, .NET, ML, Data Scince, Testing, CI Tutorials in Easy Languages.

"Best Software Training, Internship, Project Development center of Indore India, Helpline 780506-3968"

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>


Remaining .cshtml code will be same 

Post a Comment

If you have any doubt in programming or join online classes then you can contact us by comment .

Previous Post Next Post