Calling a stored procedure in MVC

Question

I have a stored procedure that i am trying to call to return a result to my view. I have a class in my models folder that i am trying to call it from.

 public  class MasterMenu
    {

        public  List<USP_MenuList_ForUser_G_Result> GetMenus(int userId)
        {


            List<USP_MenuList_ForUser_G_Result> GetMenuListForUser = null;
            using (MenuEntities dataContext = new MenuEntities())
            {
                GetMenuListForUser = dataContext.USP_MenuList_ForUser_G(56367).ToList();
                return GetMenuListForUser;
            }
        }

I was trying to reach it in my view like so

<body>

    <div id="header">
        <div id="logo">
        </div>

    </div>
    @if (Model.GetMenus(56367) != null && Model.GetMenus(56367).Count > 0)
    { }



    @RenderBody()
</body>

I was not sure what to do in my controller to reach the stored procedure. The procedure takes one parameter which is User ID and all of the tables that it needs to access are in my EDMX. Here is what my controller looks like.

public class MasterMenuController : Controller
    {
        private MenuEntities menuEntities = new MenuEntities();
}

I also have this class which looks like a db context class but was automatically generated when i created the EDMX.

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace Adds.Areas.Mvc.Menu.Models
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Data.Entity.Core.Objects;
    using System.Linq;

    public partial class MenuEntities : DbContext
    {
        public MenuEntities()
            : base("name=MenuEntities")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

        public virtual DbSet<tblAgent> tblAgents { get; set; }
        public virtual DbSet<tblMenu> tblMenus { get; set; }
        public virtual DbSet<tblQualifier> tblQualifiers { get; set; }
        public virtual DbSet<tblRoleMenuMapping> tblRoleMenuMappings { get; set; }
        public virtual DbSet<tblRole> tblRoles { get; set; }
        public virtual DbSet<tblUserRoleMapping> tblUserRoleMappings { get; set; }
        public virtual DbSet<tblUsersLogin> tblUsersLogins { get; set; }
        public virtual DbSet<tblUserMenuMapping> tblUserMenuMappings { get; set; }

        public virtual ObjectResult<USP_MenuList_ForUser_G_Result> USP_MenuList_ForUser_G(Nullable<int> userID)
        {
            var userIDParameter = userID.HasValue ?
                new ObjectParameter("UserID", userID) :
                new ObjectParameter("UserID", typeof(int));

            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<USP_MenuList_ForUser_G_Result>("USP_MenuList_ForUser_G", userIDParameter);
        }
    }
}

It seems according to the generated context he's already imported the function. — Jason W, Jan 21 '16 at 15:20

score 2 · Accepted Answer · answered Jan 21 '16 at 15:20

Your controller has to make it available to the view, such as part of the model passed into a strongly type view or in ViewBag. Since it appears you're doing this in a global layout or partial view or something since it's an always visible menu, you might consider wrapping the code to add it to ViewBag in a global filter that is added to the site in your global.asax.

Calling a stored procedure in MVC

1 Answers1