Wednesday, 8 January 2014

Execute Stored Procedure using Entity Framework in ASP.NET MVC

In this article we are going to see how to execute the stored procedure in entity framework ,In MVC we are going to see the how to add the EF.

Execute the following script in the database to create a stored procedure.

CREATE PROCEDURE FETCHEMPLOYEES
AS
BEGIN
      SELECT * FROM EMPTABLE
END

CREATE PROCEDURE FETCHEMPLOYEE(@ID INT)
AS
BEGIN
      SELECT * FROM EMPTABLE WHERE ID = @ID
END



Create a Empty MVC project, select Razor as Engine. Right click the Models and add new item.Select Data in the left pane and click ADO.NET entity framework model.Give a name as Employee.edmx and press ok.




Choose the model content as Generate from the database,and click next.




 Now select the New Connection and give the Entities name, then click next. 




Then select the all stored procedure which are need for the project. Give the model name as Employee Model and click Finish.




Right click the Employee.edmx and click the model browser to see the following items.




Expand the EmployeeModel.Store and select the stored procedure and the Tables need to be in project. that need to be added in entities.and click add Function Import to import the stored procedure as Function to the code.




In the Add Function Import screen please give the function name as you need and select the return type of that stored procedure based on your requirement. In this project return type is a emptable value , if your are returning a complex type by combining a multiple tables. The entities that are seen in the collection is listed by selected what are selected in the tables in the EmployeeModel.edmx.



Now add a cs file as Empmodel.cs in the Models folder to iterate values from db through stored procedure.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Objects;

namespace EFSample.Models
{
    public class EmpModel
    {
        EmployeeEntities empdb = new EmployeeEntities();
       
        public List<EMPTABLE> GetEmployees()
        {
           return empdb.FETCHEMPLOYEES().ToList();  
        }

        public EMPTABLE GetEmployee(int? id)
        {
            return empdb.FETCHEMPLOYEE(id).ToList().Single();
        }
    }
}
  

Add the EmployeeController 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using EFSample.Models;

namespace EFSample.Controllers
{
    public class EmployeeController : Controller
    {
        Models.EmpModel mod = new Models.EmpModel();

        public ActionResult Index()
        {
            List<EMPTABLE> result = mod.GetEmployees();
            return View(result);
        }

        public ActionResult Details(int id)
        {
            EMPTABLE result = mod.GetEmployee(id);
            return View(result);
        }

    }
}



Add the Index View and Details View.

Index.cshtml
@model IEnumerable<EFSample.Models.EMPTABLE>

@{
    ViewBag.Title = "Employee Information";
}

<h2>Employees</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table style="border:2px solid Pink">
    <tr>
        <th style="color:Blue">
            @Html.DisplayNameFor(model => model.NAME)
        </th>
        <th style="color:Blue">
            @Html.DisplayNameFor(model => model.DEPTID)
        </th>
        <th style="color:Blue">
            @Html.DisplayNameFor(model => model.COUNTRY)
        </th>
        <th style="color:Blue">
            @Html.DisplayNameFor(model => model.MARRIED)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.NAME)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.DEPTID)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.COUNTRY)
        </td>
       
        <td>           
            @Html.ActionLink("Details", "Details", new { id=item.ID })        
        </td>
    </tr>
}

</table>



Details.cshtml
@model EFSample.Models.EMPTABLE

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>
<link href="../../Site.css" rel="stylesheet" type="text/css" />
<fieldset>
    <legend>EMPTABLE</legend>

   
    <table>
   
    <tr>
    <td>  @Html.DisplayNameFor(model => model.NAME) :</td><td style="font-weight:bold">@Html.DisplayFor(model => model.NAME)</td>   
    </tr>
   
    <tr>
    <td> @Html.DisplayNameFor(model => model.DEPTID) :</td><td style="font-weight:bold"> @Html.DisplayFor(model => model.DEPTID)</td>
    </tr>

    <tr>
    <td>  @Html.DisplayNameFor(model => model.COUNTRY) :</td><td style="font-weight:bold">@Html.DisplayFor(model => model.COUNTRY)</td>
    </tr>
   
    </table>                 

</fieldset>
<p>
    @Html.ActionLink("Edit", "Edit", new { id=Model.ID }) |
    @Html.ActionLink("Back to List", "Index")
</p>


Output:

Index.View



 Details.View




From this article you can learn how to add the stored procedure as function in the Model using Entity Framework in ASP.NET MVC.


28 comments:

  1. very helpful
    thanks alot

    ReplyDelete
  2. Thank you soo much... this article helped me a lot :)

    ReplyDelete
  3. HI rajesh

    I m having two input parameters to my stored procedure and i m not getting create complex type and i want to show the list of values what we get from SP into a nested webgrid can u plz guide me waiting for ur reply
    Thanks&Regards
    Sandeep

    ReplyDelete
  4. Hi Sandeep,

    If you have a complex type then create a strongly typed class for that , then select that class in the option shown as Complex in the UI of AddFunctionImport screen. This will solve your issue easily.

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. Hello,

    Thank you very helpful...on the print screen you are adding a function import with the name of FETCHEMPLOYEE and then you return empdb.FETCHEMPLOYEES().ToList(); is it just a mistyped?

    ReplyDelete
  7. I did same as you have explained but its not working. In (model) .cs file i am calling SP as you have explained but its giving error "no overload for method 'xyz' takes 0 argument" when i put cursor over it?????

    ReplyDelete
  8. Hi Anuj,

    I am import the Two stored procedures thats y, it had two methods with different parameters .

    ReplyDelete
  9. Can you show how to join 2 tables to read data using stored procedure and use them in mvc

    ReplyDelete
    Replies
    1. Create a StoredProcedure and place the logic of joining two tables in that for refer : http://dotnetvisio.blogspot.in/2013/11/sql-server-notes-5.html for join and import the stored procedure as i mentioned above. There is a another way you can place a join condition in Entity Framework by placing a code including refer this link http://dotnetvisio.blogspot.in/2014/12/join-two-tables-in-entity-framework.html

      Delete
  10. This comment has been removed by a blog administrator.

    ReplyDelete
  11. what should be the return type of the fucntion if the stored procedure is as follows

    ALTER PROCEDURE [dbo].[stp_kbArticle_Get]
    @pstrkbID char ( 32 ) ,
    @pstrSource_Cd varchar ( 10 ) ,
    @pintkbArticleID int
    AS
    select
    kbArticle.Article_ID ,
    Article_Title,
    ArticleThumbnail,
    Publish,
    kbArticle.DateCreated,
    kbArticleSection.Section_ID,
    kbArticleSection.SectionText,
    kbSectionType.SectionName
    from
    kbArticle (NOLOCK) ,
    kbArticleSection (NOLOCK) ,
    kbSectionType (NOLOCK)
    where
    kbArticle.KBID =@pstrkbID
    AND kbArticle.Article_ID = @pintkbArticleID
    AND kbSectionType.sectiontype = kbArticleSection.sectionType
    AND kbArticleSection.article_id = kbArticle.article_id
    AND kbArticle.KBID = kbArticleSection.KBID
    AND kbArticle.Source_Cd = @pstrSource_Cd
    AND kbArticleSection.Source_Cd = @pstrSource_Cd

    ORDER by SectionSequence

    ReplyDelete
    Replies
    1. I have used the complextype when creating the function import

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Create a class that matches the return data types from storedProcedure, then map that class to the Function with complex type

      Delete
  12. This comment has been removed by the author.

    ReplyDelete
  13. nice..
    by jotdeep

    ReplyDelete
  14. Very nice.
    by Sohan.

    ReplyDelete
  15. hi rajesh i want to display two tables data as one table but i don't know how to do can you help me for this query

    ReplyDelete
  16. Given so much information in it. its very useful .perfect explanation about Dot net framework.Thanks for your valuable information. dot net training in chennai velachery | best dot net training in chennai

    ReplyDelete

  17. It is really a great work and the way in which u r sharing the knowledge is excellent.
    Thanks for helping me to understand basic concepts. As a beginner in Dot Net programming your post help me a lot.Thanks for your informative article.
    dot net training in velachery | dot net training institute in velachery

    ReplyDelete