In this article I will explain that how to do create, edit, Update, select and delete operation with stored procedure entity framework means data first approach.

First step is to create data base and table.

CREATE TABLE [dbo].[RegistrationForm](
          [Id] [int] IDENTITY(1,1) primary key NOT NULL,
          [FName] [varchar](50) NULL,
          [MName] [varchar](50) NULL,
          [LName] [varchar](50) NULL,
          [Dob] [datetime] NOT NULL,
          [Mobile] [nchar](10) NULL,
          [City] [varchar](50) NULL,
          [Pin] [varchar](50) NULL,
          [Address] [varchar](250) NULL,
);


And for bind dropdown with database create table and store records

CREATE TABLE [dbo].[CountryList](
          [Id] [int] IDENTITY(1,1) primary key NOT NULL,
          [Country] [varchar](50) NULL,
);

Now will create stored procedure for Insert, select and delete record

Procedure for insert record

Create Proc [dbo].[InsertRecord]
(
@Fname varchar(50),
@MName varchar(50 ),
@LName varchar(50),
@Dob datetime,
@Mobile nchar(10),
@City varchar(50),
@Pin varchar(50),
@Address varchar(250)
)
As
Begin
Insert into RegistrationForm(FName,MName,LName,Dob,Mobile,City,Pin,Address) values(@Fname,@MName,@LName,@Dob,@Mobile,@City,@Pin,@Address)
End

 

Procedure for delete record

Create Proc [dbo].[deleterecord]
(
@id int
)
as
Begin
Delete from dbo.RegistrationForm where Id=@id
End

 

 for selecting record

Create Proc [dbo].[GetbyId](
@Id int)
As
Begin
Select * from RegistrationForm where Id=@Id
End

 

And for Updating records

 

Create proc [dbo].[UpdateRecord](
 
 @id int,
 @FirstName varchar(50),
 @MName varchar(50),
 @LName varchar(50),
 @Dob datetime,
 @Mobile nchar(10),
 @City varchar(50),
 @Pin varchar(50),
 @Address varchar(250)
 
)
as
begin
 UPDATE dbo.RegistrationForm
SET
FName=@FirstName,MName=@MName,LName=@LName,Dob=@Dob,Mobile=@Mobile,City=@City,Pin=@Pin,Address=@Address
where Id=@Id
end

 

Now add New Project Demo. Right Click on the Models folder and select


Ado.Net Entity Data Model

Curd Operation using stored procedure through entity framework

And click generate From Database

Curd Operation using stored procedure through entity framework

And next and then give connection information and select your database.

 Curd Operation using stored procedure through entity framework

Then choose tables and store procedure.

 Curd Operation using stored procedure through entity framework

And then finish.

In the next step we will create Home Controller for adding controller right click on the controllers folder and go to add and then click controller then a pop up will be appear from here you can  change you can give your controller name  .

 Curd Operation using stored procedure through entity framework

 After adding controller your home controller will look like this.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
 
namespace MvcApplication3.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/
 
        public ActionResult Index()
        {
            return View();
        }
 
    }
}

 

 For adding view right click on the Index method and add view.

In the Index view we can design our presentation view. 

@model DemoStoreproc.Models.RegistrationForm   
@{
    ViewBag.Title = "Index";
}
<style>
    Input[type="Text"] {
        width:200px;
       
    }
    Input[type="Submit"] {
        color:blue;
        background-color:aquamarine;
        width:90px;
       
    }
</style>
 
    <script src="~/Scripts/jquery.validate.min.js"></script>
    <script src="~/Scripts/jquery.validate.unobtrusive.js"></script>
<script src="~/Scripts/jquery-3.1.1.js"></script>
@*<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.9.0.js"></script>*@
    <script src="~/Scripts/jquery.unobtrusive-ajax.min.js"></script>     
<h2>Registration Form</h2>
 @using (Ajax.BeginForm("Add", "Home", new AjaxOptions
    {
        HttpMethod = "POST",
        UpdateTargetId = "target",
        OnSuccess = "updateSuccess",
 
    }, new { id = "form1" }))
{
<table>
    <tr>
        <td>First Name</td>
        <td>@Html.TextBoxFor(model => model.FName)</td>
        <td>Middle Name</td>
        <td>@Html.TextBoxFor(model => model.MName)</td>
        <td>Last Name</td>
        <td>@Html.TextBoxFor(model => model.LName)</td>
    </tr>
    <tr>
        <td>Dob</td>
        <td>@Html.TextBoxFor(model => model.Dob)</td>
        <td>Mobile</td>
        <td>@Html.TextBoxFor(model => model.Mobile)</td>
        <td>Country</td>
        <td>@Html.DropDownListFor(model =>model.City,new SelectList(ViewBag.city,"Country","Country"))</td>
    </tr>
    <tr>
        <td>Pin</td>
        <td>@Html.TextBoxFor(model => model.Pin)</td>
        <td>Address</td>
        <td>@Html.TextAreaFor(model =>model.Address)</td>
 
    </tr>
    <tr><td><input type="submit" value="Save" /></td>
       @ViewData["Message"]
 
    </tr>
</table>
}
<div id="target">
    @Html.Partial("List");
</div>
  <script>  
         function updateSuccess(data) {
             $('form')[0].reset();           
         }
</script>

 

And for adding partial view right click on the views folder then go to add and then click view. And tick at the Create as a partial view.

 Curd Operation using stored procedure through entity framework

In the partial view we create grid for show records. And for filling records I have used view bag.

 

<table border="1" cellspacing="0" cellpadding="0">
    <tr>
        <td>First Name</td>
        <td>Middle Name</td>
        <td>Last Name</td>
        <td>DOB</td>
        <td>Mobile</td>
        <td>City</td>
        <td>Pin</td>
        <td>Address</td>
    </tr>
    @foreach (var d in ViewBag.users as List<DemoStoreproc.Models.RegistrationForm>)
    {
        <tr>
            <td>@d.FName</td>
            <td>@d.MName</td>
            <td>@d.LName</td>
            <td>@(d.Dob != null ? d.Dob.ToString("dd/MM/yyyy") : "")</td>
            <td>@d.Mobile</td>
            <td>@d.City</td>
            <td>@d.Pin</td>
            <td>@d.Address</td>
            <td>@Html.ActionLink("Edit", "Edit", new { Id = d.Id },
                 new AjaxOptions
                 {
                     OnSuccess="Filldata",
                     InsertionMode = InsertionMode.Replace,
                     HttpMethod = "GET",
 
                 }) </td>
            <td>@Ajax.ActionLink("Delete", "Delete", new { Id = d.Id },
                 new AjaxOptions
                 {
                    UpdateTargetId="target",
                     InsertionMode = InsertionMode.Replace,
                     HttpMethod = "POST"
                 })</td>
 
 
        </tr>
    }
</table>
<script>
    function Filldata(data) {
        console.log(data);
        $('#Id').val(data.Id);
        $('#FName').val(data.FName);
       $('#MName').val(data.MName);
        $('#LName').val(data.LName);       
        $('#Dob').val(data.Dob);      
        $('#Mobile').val(data.Mobile);
        $('#Pin').val(data.Pin);
        $('#Address').val(data.Address);
    }
</script>


For binding dropdown in view using viewbag. In the controller 

        public ActionResult Index()
        {
            var context = new DemoEntities();
            ViewBag.Country =context.CountryLists;
            return View();
        }

And in the view

<td>@Html.DropDownListFor(model => model.City, new SelectList(ViewBag.Country, "Country", "Country"), "-Select-")</td>

 

In the home controller write the following code for Inserting records, editing records and deleting records DemoEntities is the database context name. We can access stored procedure using instance of database context. InsertRecord is the stored procedure name.

using (var context = new DemoEntities())
            {
              
                context.InsertRecord(Model.FName,Model.MName, Model.LName, Model.Dob, Model.Mobile, Model.City, Model.Pin, Model.Address);
                context.SaveChanges();
      

 

All controller code is 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
usingDemoStoreproc.Models;
using System.Data;
using System.Data.Entity;
 
namespace DemoStoreproc.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/
 
        public ActionResult Index()
        {
            var context = new DemoEntities();
            ViewBag.Country =context.CountryLists;
            ViewBag.users =context.RegistrationForms.ToList();
 
            return View();
        }
 
        [HttpPost]
        public ActionResult Add(RegistrationForm Model)
        {
            using (var context = new DemoEntities())
            {
                if (Model.Id == 0)
                {
                    context.InsertRecord(Model.FName, Model.MName, Model.LName, Model.Dob, Model.Mobile, Model.City, Model.Pin, Model.Address);
 
                }
                else
                {
                    context.UpdateRecord(Model.Id, Model.FName, Model.MName, Model.LName, Model.Dob, Model.Mobile, Model.City, Model.Pin, Model.Address);
                }
 
                //context.RegistrationForms.Add(Model);
                ViewData["Message"] = "Success";
 
            }
            var context1 = new DemoEntities();
            ViewBag.users =context1.RegistrationForms.ToList();
            return PartialView("List");
        }
       
        public ActionResult Edit(int id)
        {
            var context = new DemoEntities();
            //var data = context.GetbyId(id);
           var data =context.RegistrationForms.Find(id);
            //return PartialView("List",data);
            return Json(data, JsonRequestBehavior.AllowGet);
        }
 
        public ActionResult Delete(int id)
        {
            var context = new DemoEntities();
            context.deleterecord(id);
            ViewBag.users =context.RegistrationForms.ToList();
            return PartialView("List");
 
        }
    }
}

  

Our page will look like 

Curd Operation using stored procedure through entity framework

  Modified On Mar-17-2018 03:51:51 AM

Leave Comment