Stored Procedures In Entity Framework 6 In MVC 5

Introduction

As you know we can apply any Entity Framework approach such as Code First and Database First in the ASP.NET MVC Application, so in this article I am explaining the use of Stored Procedures when we use the Code First Approach of Entity Framework in MVC 5 applications.

We can implement the Stored Procedures in Entity Framework 6 and perform only the Insert, Delete and Update operations. One more feature is that it only works for those applications that use the Code First Approach; that is, we first create the structure of the class and next accomplish the application and the database is created when running the application.

You will also learn the Code First Migrations use in here and perform the operations in the database. We use the functionality of Stored Procedures with the Fluent API. Any implementation occurs through a feature called Fluent API.

Prerequisites

Visual Studio 2013 is the prerequisite to work with this article.

So, let's just use the following sections with which we can implement the functionality,

• Create ASP.NET MVC 5 Application
• Adding Model
• Scaffolding in MVC 5
• View in MVC 5
• LOG in Entity Framework
• Working with Stored Procedures

Create ASP.NET MVC 5 Application

In this section we'll create the ASP.NET Web Application with the MVC 5 Project Template. Use the following procedure.

Step 1

Open the Visual Studio 2013 and click on the "New Project".

Step 2

Select the Web from the left pane and create the ASP.NET Web Application.

Step 3

Select the MVC Project Template in the next One ASP.NET Wizard.

Visual Studio automatically creates the MVC 5 application  and adds some files and folders to the solution. Have a look:

Adding Model

In this section, we'll add the class in the models folder. Use the following procedure.

Step 1

Right-click on the Models folder and Add a new Class, "Movie".

Step 2

Edit the code with the following code,

using System;
using System.ComponentModel.DataAnnotations;

namespace MvcStoredProcedureSample.Models
{
    public class Movie
    {
        public int ID { get; set; }
        [Required]
        public string Name { get; set; }
        [Required]
        [Display (Name="Release Date")]
        public DateTime ReleaseDate { get; set; }
        [Required]
        public string Category { get; set; }
    }
}

In the code above, the properties are defined in a class. You can also notice that there is no entity key is defined in the preceding code because we are using the Entity Framework 6 and it is not necessary since the key property is composed by class name + ID. As we have the class named Movie, so the ID property is identified automatically as the primary key. You can also add other properties to the class.

Step 3 - Build the solution.

Working with Entity Framework

Generally when we create the latest MVC project, the Entity Framework is installed as a default. It it is not available in the packages.config file; you can install it from the Package Manager Console by entering the following command:

Install-Package EntityFramework

In my solution, the latest version of Entity Framework, EntityFramework 6.1.0, is installed as the default. Have a look:

You can also update the package by entering the following command in the Package Manager Console:

Update-Package EntityFramework

Scaffolding in MVC 5

In this section we'll add a new scaffolded controller using Entity Framework. So, follow the procedure below.

Step 1

Just right-click on the Controllers folder and click on the Add-> New Scaffolded Item

Step 2

In the next Add Scaffold wizard, select the MVC 5 Controller with views as in the following:

Step 3

In the next Add Controller wizard, select the Model Class and to use the Data Context class we need to add new.

Step 4

Enter the Data Context class as in the following,

Step 5

Now Add the Controller by clicking the Add button

Step 6

Now we have the MovieDbContext class and MoviesController class after scaffolding the controller. Check it out:

MovieDbContext class,

using System.Data.Entity;

namespace MvcStoredProcedureSample.Models
{
    public class MovieDbContext : DbContext
    {
        public MovieDbContext() : base( "name = MovieDbContext" )
        {
        }

        public DbSet<Movie> Movies { get; set; }

    }
}

MoviesController class

using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using MvcStoredProcedureSample.Models;

namespace MvcStoredProcedureSample.Controllers
{
    public class MoviesController : Controller
    {
        private MovieDbContext db = new MovieDbContext();

        // GET: Movies
        public ActionResult Index()
        {
            return View(db.Movies.ToList());
        }

        // GET: Movies/Details/5
        public ActionResult Details(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Movie movie = db.Movies.Find(id);
            if (movie == null)
            {
                return HttpNotFound();
            }
            return View(movie);
        }

        // GET: Movies/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: Movies/Create
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create([Bind(Include = "ID,Name,ReleaseDate,Category")] Movie movie)
        {
            if (ModelState.IsValid)
            {
                db.Movies.Add(movie);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            return View(movie);
        }

        // GET: Movies/Edit/5
        public ActionResult Edit(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Movie movie = db.Movies.Find(id);
            if (movie == null)
            {
                return HttpNotFound();
            }
            return View(movie);
        }

        // POST: Movies/Edit/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for
        // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit([Bind(Include = "ID,Name,ReleaseDate,Category")] Movie movie)
        {
            if (ModelState.IsValid)
            {
                db.Entry(movie).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            return View(movie);
        }

        // GET: Movies/Delete/5
        public ActionResult Delete(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Movie movie = db.Movies.Find(id);
            if (movie == null)
            {
                return HttpNotFound();
            }
            return View(movie);
        }

        // POST: Movies/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(int id)
        {
            Movie movie = db.Movies.Find(id);
            db.Movies.Remove(movie);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }
}

In the code above, the MoviesController is defined that inherits from the Controller. All database accessing methods like Create(), Edit(), Delete() are defined automatically in this controller class.

View in MVC 5

When we use the scaffolding using the MVC 5 Controller with Views using Entity Framework, the Movies folder is automatically created in the Views folder. Check it out:

Now we add an ActionLink in the main layout page of our application to connect with the new controller. So, open the _Layout.cshtml file in the Views/Shared folder and edit the code with the following highlighted code:

<div class="navbar-collapse collapse">
    <ul class="nav navbar-nav">
        <li>@Html.ActionLink("Home", "Index", "Home")</li>
        <li>@Html.ActionLink("About", "About", "Home")</li>
        <li>@Html.ActionLink("Movies","Index", "Movies")</li>
        <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
    </ul>
    @Html.Partial("_LoginPartial")
</div>

Now we run the application. Press F5 to run the application and open the Movies Controller and add some movies. When it done, the Index page will look such as follows:

As you can see that the data is inserted into the table but we do not know which technique the Entity Framework is inserting the data, whether using the Stored Procedure or by T-SQL Statements? Well generally, it uses the T-SQL statements to insert the data because we didn't specify for it to use the Stored Procedure. Just proceed to the next section to use this.

LOG in Entity Framework

Now in this section we'll track what Entity Framework does behind the scenes. We'll add the System.Diagnostics so that we can see the result on the window Output Visual Studio at runtime.

Step 1

Update the controller with the following highlighted code,

using System.Diagnostics;

namespace MvcStoredProcedureSample.Controllers
{
    public class MoviesController : Controller
    {
        private MovieDbContext db = new MovieDbContext();

        public MoviesController()
        {
            db.Database.Log = l => Debug.Write(l);
        }

        // GET: Movies
        public ActionResult Index()
        {
            return View(db.Movies.ToList());
        }

Step 2

Now run the project and open the controller again. When you are viewing the list of movies, do not close the browser and switch to your Visual Studio. Open the Output window and check out the SQL statement. Have a look,

So now we'll use Stored Procedure in the next section.

Working with Stored Procedures

If we want to work with the Stored Procedure then we need to use the Code First Migrations that is very safe, smooth and productive. So use the following procedure.

Step 1

Open the Tools-> NuGet Package Manager->Package Manager Console and enter the following command:

Enable-Migrations

Step 2

Now the data context class will use the Stored Procedure. Open the Context class and update the code as shown below:

namespace MvcStoredProcedureSample.Models
{
    public class MovieDbContext : DbContext
    {
        public MovieDbContext() : base( "name = MovieDbContext" )
        {
        }

        public DbSet<Movie> Movies { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Movie>().MapToStoredProcedures();
        }

    }
}

Step 3

Build the solution. Now in the Package Manager Console enter the following command:

Add-Migration MyMovieSP

You can use any name in the place of MyMovieSP.

It creates the 201405080929139_MyMovieSP.cs file and in which you can see the following code:

namespace MvcStoredProcedureSample.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class MyMovieSP : DbMigration
    {
        public override void Up()
        {
            CreateStoredProcedure(
                "dbo.Movie_Insert",
                p => new
                    {
                        Name = p.String(),
                        ReleaseDate = p.DateTime(),
                        Category = p.String(),
                    },
                body:
                    @"INSERT [dbo].[Movies]([Name], [ReleaseDate], [Category])
                      VALUES (@Name, @ReleaseDate, @Category)

                      DECLARE @ID int
                      SELECT @ID = [ID]
                      FROM [dbo].[Movies]
                      WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()

                      SELECT t0.[ID]
                      FROM [dbo].[Movies] AS t0
                      WHERE @@ROWCOUNT > 0 AND t0.[ID] = @ID"
            );

            CreateStoredProcedure(
                "dbo.Movie_Update",
                p => new
                    {
                        ID = p.Int(),
                        Name = p.String(),
                        ReleaseDate = p.DateTime(),
                        Category = p.String(),
                    },
                body:
                    @"UPDATE [dbo].[Movies]
                      SET [Name] = @Name, [ReleaseDate] = @ReleaseDate, [Category] = @Category
                      WHERE ([ID] = @ID)"
            );

            CreateStoredProcedure(
                "dbo.Movie_Delete",
                p => new
                    {
                        ID = p.Int(),
                    },
                body:
                    @"DELETE [dbo].[Movies]
                      WHERE ([ID] = @ID)"
            );

        }

        public override void Down()
        {
            DropStoredProcedure("dbo.Movie_Delete");
            DropStoredProcedure("dbo.Movie_Update");
            DropStoredProcedure("dbo.Movie_Insert");
        }
    }
}

Step 4

We need to tell the database to create the MyMovieSP. So just enter the following command in the Package Manager Console,

Update-Database

Step 5

You can also check out the Stored Procedure from the Server Explorer. Check out the following screenshot,

Step 6

If you want to check out whether or not the Entity Framework is now using the Stored Procedure, run the application again and add some movies and at the same time check out the Output window,

That's all for now.

Summary

This article described the use of Stored Procedure in the ASP.NET MVC Application using the Entity Framework Code First Approach in Visual Studio 2013. You can also check out the implementation of Stored Procedure in the application of Visual Studio 2013. Thanks for reading.