How to use a stored procedure in MVC

Home > Search > How-to
  by

For the purpose of this tutorial, let's say you have already created an Entity Framework Model, View, and Controller in MVC. Under the /View/Example/ folder, you have a file Stage.cshtml, which maps to www.example.com/Example/Stage. We want to use a stored procedure on the Stage page.


You should already have a file under Models that contains a public class for DbContext. In this example, the public class is My_db : DbContext.

using System;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Web.Mvc;

namespace Example.Models
{
    public class table_name
    {
     . . .
    }

    public class My_db : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        };
        public DbSet<table_name> table_name{ get; set; }
    }
}

Enable migrations.

  1. Select Tools > NuGet Package Manager > Package Manager Console.
  2. Type Enable-Migrations -ContextTypeName Example.Models.My_db and press enter. Replace Example.Models.My_db with the namespace and DbContext name in your file. This will add a folder named Migrations and a file named Configuration.cs.

This allows the public class My_db : DbContext to use stored procedures. We next need to tell public class My_db : DbContext to use our stored procedures.


  1. Add modelBuilder.Entity<table_name>().MapToStoredProcedures(); inside public class My_db : DbContext.
using System;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Web.Mvc;

namespace Example.Models
{
    public class table_name
    {
     . . .
    }

    public class Example : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<table_name>().MapToStoredProcedures
            (
              s => s.Query(q => q.HasName("[dbo].[table_name]"));
             );
        };
        public DbSet<table_name> table_name{ get; set; }
    }
}

 

  1. Press Ctrl + Shift + B to build the solution.

Add a migration:

  1. Select Tools > NuGet Package Manager > Package Manager Console.
  2. Type Add-Migration MyStoredProcedures and press enter. Replace MyStoredProcedures with a unique name of your choosing. This will add a folder named Migrations and a file named xxxxxxxxxxxx_MyStoredProcedures.cs.



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments