How To Call Stored Procedure With Select Query In Asp.Net MVC Entity Framework

This article will show you how to call Stored Procedure with select query statement  using Entity Framework in ASP.Net MVC.

So for this article first we will create a new SP in our DB.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_Studentdetail] AS BEGIN SELECT [Id]       ,[Name]       ,[Address]       ,[Section]       ,[Marks]   FROM [dbo].[StudentDetail]      END

Now check the table data.

Now we will create a new asp.net application and add an entity file (.edmx) in your project. After adding your entity file. Open the entity file and right click on it. And select Update Model.

As you click on it below mention screen will open.

Now select the SP and click on ok button. Now click on Model Browser .

Here check the option Store Procedure / Functions options. Now expand this option and you will get your selected SP.

Now We will add Complex Type. This will be responsible for mapping your all the field data return by you Stored Procedure.

For adding Complex Type we will first right click on Complex type option and select the Add New Complex Type and click on it.

Now right click on SP and click on Add Function Imports. Now change the name of complex type as given format.

Now we will add the field’s exact name with exact same data type which the fields are having.

Now select the data type of field from the list and rename the selected field.

So the same for all other fields.

Now we have to create the create the function for the Stored procedure which we will use in our code. So for creating the function go to Model browser and select the Stored Procedure / Function and right click on it.

Now select the now click on Add Function Imports.

After clicking on Add Function Imports below mention windows will open.

Now we will select the complex option and select the created the Complex name from the dropdown this complex name is the name which we have created in above steps.

Now click on finish button to end of the configuration. Now we will add a model class file and add the below code.

using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace MVC_Demos.Models {     public class StudentModel     {         public List<Student> StudentList { get; set; }     }     public class Student     {         public int Id { get; set; }         public string Name { get; set; }         public string Address { get; set; }         public string Section { get; set; }         public string Marks { get; set; }     } }

Now we will add a controller class file and add the below code into the add the below code.

using MVC_Demos.Models; using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; namespace MVC_Demos.Controllers {     public class HomeController : Controller     {         //         // GET: /Home/         [HttpGet]         public ActionResult Index()         {             StudentModel _studentModel = new StudentModel();             _studentModel.StudentList = GetStudentList();             return View(_studentModel);         }         ///         /// Get student detail from data base list             ///         public List<Student> GetStudentList()         {             List<Student> _student = new List<Student>();             //Create instance for entity class             DemoEntities _demoEntities = new DemoEntities();             var stuData = from data in _demoEntities.SP_Studentdetail()                           select data;             foreach (var item in stuData)             {                 _student.Add(new Student { Id = item.Id, Name = item.Name, Marks = item.Address, Address = item.Address, Section = item.Section });             }             return _student;         }     } }

    

In above code please check the above highlighted code. In this we have accessed the SP function which we have created. Now we will  create the view and add the below code into the page.

@model MVC_Demos.Models.StudentModel @{     ViewBag.Title = "how to call stored procedure in asp.net mvc entity framework"; }asp.net <style type="text/css">     .gridtable {         font-family: verdana,arial,sans-serif;         font-size: 11px;         color: #333333;         border-width: 1px;         border-color: #666666;         border-collapse: collapse;     }         .gridtable th {             border-width: 1px;             padding: 8px;             border-style: solid;             border-color: #666666;             background-color: #dedede;         }         .gridtable td {             border-width: 1px;             padding: 8px;             border-style: solid;             border-color: #666666;             background-color: #ffffff;         }         .gridtable tr:hover td,         .gridtable tr.even:hover td.active,         .gridtable tr.odd:hover td.active {             background: #b6ff00;         } </style> @using (Html.BeginForm("Index", "Home")) {     <table width="100%" cellpadding="5" cellspacing="2" border="0" style="background-color: White;" class="gridtable">         <thead>             <tr>                 <th>Id</th>                 <th>Name</th>                 <th>Marks</th>                 <th>Address</th>                 <th>Section</th>             </tr>         </thead>         @foreach (var item in Model.StudentList)         {             <tr>                 <td>@item.Id</td>                 <td>@item.Name</td>                 <td>@item.Marks</td>                 <td>@item.Address</td>                 <td>@item.Section</td>             </tr>         }             </table> }

Now we have done.  Now check the code.

Now run the application to check the output.