Curd Operation using stored procedure through entity framework

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

And click generate From Database

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

Then choose tables and store procedure.

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 .

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
{
    publicclassHomeController : Controller
    {
        //
        // GET: /Home/
 
        publicActionResult 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>
  
    <scriptsrc="~/Scripts/jquery.validate.min.js"></script>
    <scriptsrc="~/Scripts/jquery.validate.unobtrusive.js"></script>
<scriptsrc="~/Scripts/jquery-3.1.1.js"></script>
@*<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.9.0.js"></script>*@
    <scriptsrc="~/Scripts/jquery.unobtrusive-ajax.min.js"></script>      
<h2>Registration Form</h2>
 @using (Ajax.BeginForm("Add", "Home", newAjaxOptions
    {
        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,newSelectList(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><inputtype="submit"value="Save"/></td>
       @ViewData["Message"]
 
    </tr>
</table>
}
<divid="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.

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

<tableborder="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 asList<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 },
                 newAjaxOptions
                 {
                     OnSuccess="Filldata",
                     InsertionMode = InsertionMode.Replace,
                     HttpMethod = "GET",
 
                 }) </td>
            <td>@Ajax.ActionLink("Delete", "Delete", new { Id = d.Id },
                 newAjaxOptions
                 {
                    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

        publicActionResult Index()
        {
            var context = newDemoEntities();
            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/
 
        publicActionResult Index()
        {
            var context = newDemoEntities();
            ViewBag.Country =context.CountryLists;
            ViewBag.users =context.RegistrationForms.ToList();
 
            return View();
        }
 
        [HttpPost]
        publicActionResult Add(RegistrationForm Model)
        {
            using (var context = newDemoEntities())
            {
                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 = newDemoEntities();
            ViewBag.users =context1.RegistrationForms.ToList();
            return PartialView("List");
        }
        
        publicActionResult Edit(int id)
        {
            var context = newDemoEntities();
            //var data = context.GetbyId(id);
           var data =context.RegistrationForms.Find(id);
            //return PartialView("List",data);
            return Json(data, JsonRequestBehavior.AllowGet);
        }
 
        publicActionResult Delete(int id)
        {
            var context = newDemoEntities();
            context.deleterecord(id);
            ViewBag.users =context.RegistrationForms.ToList();
            return PartialView("List");
 
        }
    }
}

Our page will look like

blog

Curd Operation using stored procedure through entity framework

Manish Kumar

Leave Comment

Comments

Liked By