Calling Stored Procedures from Entity Framework Asp.net MVC
Create sample table and Stored Procedure using below script.
CREATETABLE[dbo].[countries](
[id] [int] IDENTITY(1,1)NOTNULL,
[sortname] [nvarchar](3)NOTNULL,
[name] [nvarchar](150)NOTNULL,
CONSTRAINT[PK_countries] PRIMARYKEYCLUSTERED
(
[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
SETIDENTITY_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
SETIDENTITY_INSERT[dbo].[countries] OFF
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATEPROCEDURE[dbo].[SP_Countries]
As
Begin
SELECT id,sortname ,nameFROMcountries
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
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Web;
usingSystem.Web.Mvc;
usingMVC___Stored_Procedure_example___database_first.Models;
namespaceMVC___Stored_Procedure_example___database_first.Controllers
{
publicclassCountryController: Controller
{
testEntitiesdb = newtestEntities();
// GET: Country
publicActionResultIndex()
{
returnView(db.Countries());
}
}
}
========================================================================================
Index View :
@model IEnumerable
@{
ViewBag.Title = "Index";
}
h2>Indexh2>
table>
tr>
th>@Html.DisplayNameFor(model => model.First().name)th>
tr>
@foreach(varitem inModel)
{
tr>
td>
@Html.DisplayFor(model => item.name)
td>
tr>
}
table>
============================================================
Values displaying from DB using Stored Procedure
