Call Stored Procedure of Complex Type using Entity Framework in ASP.Net MVC

Last Reply one year ago By dharmendr

Posted one year ago

i have this storeprocedure

CREATE PROCEDURE sp_ProjectSummaryReport	
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT Project.ProjectId, Project.ProjectName, Project.TotalCost, Project.InsertDate, Profile.Profilename, ISNULL(totalreceived.received, 0) AS totalreceived, Project.TotalCost - ISNULL(totalreceived.received, 0) AS balance
    FROM Project INNER JOIN
    Profile ON Project.ProfileID = Profile.ProfileID LEFT OUTER JOIN
    (SELECT ProjectId, SUM(Amount) - SUM(Tax) AS received
    FROM ProjectIncome
    GROUP BY ProjectId) AS totalreceived ON Project.ProjectId = totalreceived.ProjectId
END
GO

 view model

    public class ProjectSummaryViewModel
    {
        public string projectname { get; set; }
        public string receivedate { get; set; }
        public string profilename { get; set; }
        public decimal totalcost { get; set; }

        public decimal taxdeduction { get; set; }
        public decimal totalreceive { get; set; }

        public decimal balance { get; set; }
    }

 controller to fetch storeprocedure as function to pass to view

public ActionResult ProjectIncomeSummary()
{
    return View(db.F_ProjectSummary());
}

 

@model IEnumerable<AMProjectDetails.ViewModels.ProjectSummaryViewModel>

@{
    ViewBag.Title = "ProjectIncomeSummary";
}

<h2>ProjectIncomeSummary</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>@Html.DisplayNameFor(model => model.projectname)</th>
        <th>@Html.DisplayNameFor(model => model.receivedate)</th>
        <th>@Html.DisplayNameFor(model => model.profilename)</th>
        <th>@Html.DisplayNameFor(model => model.totalcost)</th>
        <th>@Html.DisplayNameFor(model => model.taxdeduction)</th>
        <th>@Html.DisplayNameFor(model => model.totalreceive)</th>
        <th>@Html.DisplayNameFor(model => model.balance)</th>
        <th></th>
    </tr>
@foreach (var item in Model) {
    <tr>
        <td>@Html.DisplayFor(modelItem => item.projectname)</td>
        <td>@Html.DisplayFor(modelItem => item.receivedate)</td>
        <td>@Html.DisplayFor(modelItem => item.profilename)</td>
        <td>@Html.DisplayFor(modelItem => item.totalcost)</td>
        <td>@Html.DisplayFor(modelItem => item.taxdeduction)</td>
        <td>@Html.DisplayFor(modelItem => item.totalreceive)</td>
        <td>@Html.DisplayFor(modelItem => item.balance)</td>  
    </tr>
}
</table>

i follow this snippet but sp is not bind with my view

ASP.Net MVC: Call Stored Procedure using Entity Framework

in article there is only one table from which data is coming, in my senerio data is coming from multiple table so i cannot pass the entity name on my view as ienumerable model

Posted one year ago

Hi nauna,

Inorder to bind you need to use complex types in Function import window.

Function import window provide Function import name and Stored procedure name.

Then select Complex radiobutton from Returns a Collection Of.

Then click on Get Column Information and then Create New Complex Type.

This will generate a name for the Complex type and use the Complex type as model for your View.

Check this example.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        NorthwindEntities entities = new NorthwindEntities();
        return View(entities.CustomerOrder());
    }
}

SQL

CREATE PROCEDURE [dbo].[Customers_GetCustomersOrder]
AS
BEGIN  
  SELECT TOP 10
		 O.CustomerID,
		 C.ContactName,
		 C.City
  FROM Customers C
  INNER JOIN Orders O ON C.CustomerID = O.CustomerID
END

View

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<IEnumerable<_Bind_SP_Multiple_Table_MVC.CustomerOrder_Result>>" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Index</title>    
</head>
<body>
    <table>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>City</th>
        </tr>
        <% foreach (var item in Model)
           { %>
        <tr>
            <td><%: item.CustomerID %></td>
            <td><%: item.ContactName %></td>
            <td><%: item.City %></td>
        </tr>
        <% } %>
    </table>
</body>
</html>

Screenshot