Calling Stored Procedures from Entity Framework Asp.net MVC

Calling Stored Procedures from Entity Framework Asp.net MVC

Create sample table and Stored Procedure using below script.

CREATE TABLE [dbo].[countries](

[id] [int] IDENTITY(1,1) NOT NULL,

[sortname] [nvarchar](3) NOT NULL,

[name] [nvarchar](150) NOT NULL,

CONSTRAINT [PK_countries] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[countries] ON

GO

INSERT [dbo].[countries] ([id], [sortname], [name]) VALUES (1, N'Ind', N'India')

GO

INSERT [dbo].[countries] ([id], [sortname], [name]) VALUES (2, N'USA', N'USA')

GO

INSERT [dbo].[countries] ([id], [sortname], [name]) VALUES (5, N'Chn', N'China ')

GO

SET IDENTITY_INSERT [dbo].[countries] OFF

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SP_Countries]

As

Begin

SELECT id,sortname ,nameFROM countries

End

==========================================================================================

Right click on solution => Add New Item => Data => ADO.NET Entity Data Model

=> Name it TestModel.edmx => Add => Generate from Database =>

next => New Connection => Provide Server details and select Database

=> Test Connection => Rename Entity Connection => TestModel => next => Select your Stored procedure => Finish.

click on ModelBrowser => Click on Function Imports => click on Get column Information button => click on Create new complete type button => Rename collection of complete to Countries => Clock OK

Build solution.

Controllers => Add Controller=> "CountryController" => Add

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using MVC___Stored_Procedure_example___database_first.Models;

namespace MVC___Stored_Procedure_example___database_first.Controllers

{

public class CountryController : Controller

{

testEntities db = new testEntities();

// GET: Country

public ActionResult Index()

{

return View(db.Countries());

}

}

}

========================================================================================

Index View :

@model IEnumerable<MVC___Stored_Procedure_example___database_first.Models.Countries>

@{

ViewBag.Title = "Index";

}

<h2>Index</h2>

<table>

<tr>

<th>@Html.DisplayNameFor(model => model.First().name)</th>

</tr>

@foreach (var item in Model)

{

<tr>

<td>

@Html.DisplayFor(model => item.name)

</td>

</tr>

}

</table>

============================================================

Values displaying from DB using Stored Procedure