Post
Ask Question

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.

Create ASP.NET Web Application

Step 3

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

MVC Project Template in VS 2013

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

Solution Explorer in MVC App

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".

Adding Class in Models Folder

Step 2

Edit the code with the following code,

  1. using System;  
  2. using System.ComponentModel.DataAnnotations;  
  3.    
  4. namespace MvcStoredProcedureSample.Models  
  5. {  
  6.     public class Movie  
  7.     {  
  8.         public int ID { get; set; }  
  9.         [Required]  
  10.         public string Name { get; set; }  
  11.         [Required]  
  12.         [Display (Name="Release Date")]  
  13.         public DateTime ReleaseDate { get; set; }  
  14.         [Required]  
  15.         public string Category { get; set; }  
  16.     }  
  17. }  

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:

Entity Framework Package

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

Adding New Scaffolded Item in MVC

Step 2

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

Adding Scaffold Using EntityFramework in MVC

Step 3

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

Adding Model in Scaffolded Controller

Step 4

Enter the Data Context class as in the following,

Defining Data Context in Scaffolding

Step 5

Now Add the Controller by clicking the Add button

Adding Controller in Scaffolding

Step 6

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

MovieDbContext class,

  1. using System.Data.Entity;  
  2.    
  3. namespace MvcStoredProcedureSample.Models  
  4. {  
  5.     public class MovieDbContext : DbContext  
  6.     {  
  7.         public MovieDbContext() : base( "name = MovieDbContext" )  
  8.         {  
  9.         }  
  10.    
  11.         public DbSet<Movie> Movies { get; set; }  
  12.       
  13.     }  
  14. }  

MoviesController class

  1. using System.Data.Entity;  
  2. using System.Linq;  
  3. using System.Net;  
  4. using System.Web.Mvc;  
  5. using MvcStoredProcedureSample.Models;  
  6.    
  7. namespace MvcStoredProcedureSample.Controllers  
  8. {  
  9.     public class MoviesController : Controller  
  10.     {  
  11.         private MovieDbContext db = new MovieDbContext();  
  12.    
  13.         // GET: Movies  
  14.         public ActionResult Index()  
  15.         {  
  16.             return View(db.Movies.ToList());  
  17.         }  
  18.    
  19.         // GET: Movies/Details/5  
  20.         public ActionResult Details(int? id)  
  21.         {  
  22.             if (id == null)  
  23.             {  
  24.                 return new HttpStatusCodeResult(HttpStatusCode.BadRequest);  
  25.             }  
  26.             Movie movie = db.Movies.Find(id);  
  27.             if (movie == null)  
  28.             {  
  29.                 return HttpNotFound();  
  30.             }  
  31.             return View(movie);  
  32.         }  
  33.    
  34.         // GET: Movies/Create  
  35.         public ActionResult Create()  
  36.         {  
  37.             return View();  
  38.         }  
  39.    
  40.         // POST: Movies/Create  
  41.         // To protect from overposting attacks, please enable the specific properties you want to bind to, for   
  42.         // more details see http://go.microsoft.com/fwlink/?LinkId=317598.  
  43.         [HttpPost]  
  44.         [ValidateAntiForgeryToken]  
  45.         public ActionResult Create([Bind(Include = "ID,Name,ReleaseDate,Category")] Movie movie)  
  46.         {  
  47.             if (ModelState.IsValid)  
  48.             {  
  49.                 db.Movies.Add(movie);  
  50.                 db.SaveChanges();  
  51.                 return RedirectToAction("Index");  
  52.             }  
  53.    
  54.             return View(movie);  
  55.         }  
  56.    
  57.         // GET: Movies/Edit/5  
  58.         public ActionResult Edit(int? id)  
  59.         {  
  60.             if (id == null)  
  61.             {  
  62.                 return new HttpStatusCodeResult(HttpStatusCode.BadRequest);  
  63.             }  
  64.             Movie movie = db.Movies.Find(id);  
  65.             if (movie == null)  
  66.             {  
  67.                 return HttpNotFound();  
  68.             }  
  69.             return View(movie);  
  70.         }  
  71.    
  72.         // POST: Movies/Edit/5  
  73.         // To protect from overposting attacks, please enable the specific properties you want to bind to, for   
  74.         // more details see http://go.microsoft.com/fwlink/?LinkId=317598.  
  75.         [HttpPost]  
  76.         [ValidateAntiForgeryToken]  
  77.         public ActionResult Edit([Bind(Include = "ID,Name,ReleaseDate,Category")] Movie movie)  
  78.         {  
  79.             if (ModelState.IsValid)  
  80.             {  
  81.                 db.Entry(movie).State = EntityState.Modified;  
  82.                 db.SaveChanges();  
  83.                 return RedirectToAction("Index");  
  84.             }  
  85.             return View(movie);  
  86.         }  
  87.    
  88.         // GET: Movies/Delete/5  
  89.         public ActionResult Delete(int? id)  
  90.         {  
  91.             if (id == null)  
  92.             {  
  93.                 return new HttpStatusCodeResult(HttpStatusCode.BadRequest);  
  94.             }  
  95.             Movie movie = db.Movies.Find(id);  
  96.             if (movie == null)  
  97.             {  
  98.                 return HttpNotFound();  
  99.             }  
  100.             return View(movie);  
  101.         }  
  102.    
  103.         // POST: Movies/Delete/5  
  104.         [HttpPost, ActionName("Delete")]  
  105.         [ValidateAntiForgeryToken]  
  106.         public ActionResult DeleteConfirmed(int id)  
  107.         {  
  108.             Movie movie = db.Movies.Find(id);  
  109.             db.Movies.Remove(movie);  
  110.             db.SaveChanges();  
  111.             return RedirectToAction("Index");  
  112.         }  
  113.    
  114.         protected override void Dispose(bool disposing)  
  115.         {  
  116.             if (disposing)  
  117.             {  
  118.                 db.Dispose();  
  119.             }  
  120.             base.Dispose(disposing);  
  121.         }  
  122.     }  
  123. }  

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:

Views Folder in MVC 5

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:

  1. <div class="navbar-collapse collapse">  
  2.     <ul class="nav navbar-nav">  
  3.         <li>@Html.ActionLink("Home", "Index", "Home")</li>  
  4.         <li>@Html.ActionLink("About", "About", "Home")</li>  
  5.         <li>@Html.ActionLink("Movies","Index", "Movies")</li>  
  6.         <li>@Html.ActionLink("Contact", "Contact", "Home")</li>                      
  7.     </ul>  
  8.     @Html.Partial("_LoginPartial")  
  9. </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:

Controller in MVc

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,

  1. using System.Diagnostics;  
  2.    
  3. namespace MvcStoredProcedureSample.Controllers  
  4. {  
  5.     public class MoviesController : Controller  
  6.     {  
  7.         private MovieDbContext db = new MovieDbContext();  
  8.    
  9.         public MoviesController()  
  10.         {  
  11.             db.Database.Log = l => Debug.Write(l);  
  12.         }  
  13.    
  14.         // GET: Movies  
  15.         public ActionResult Index()  
  16.         {  
  17.             return View(db.Movies.ToList());  
  18.         }  

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,

Select Statement in Output Window

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

Enable Migration in Package Manager Console

Step 2

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

  1. namespace MvcStoredProcedureSample.Models  
  2. {  
  3.     public class MovieDbContext : DbContext  
  4.     {  
  5.         public MovieDbContext() : base( "name = MovieDbContext" )  
  6.         {  
  7.         }  
  8.    
  9.         public DbSet<Movie> Movies { get; set; }  
  10.    
  11.         protected override void OnModelCreating(DbModelBuilder modelBuilder)  
  12.         {  
  13.             modelBuilder.Entity<Movie>().MapToStoredProcedures();  
  14.         }  
  15.       
  16.     }  
  17. }  

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:

  1. namespace MvcStoredProcedureSample.Migrations  
  2. {  
  3.     using System;  
  4.     using System.Data.Entity.Migrations;  
  5.       
  6.     public partial class MyMovieSP : DbMigration  
  7.     {  
  8.         public override void Up()  
  9.         {  
  10.             CreateStoredProcedure(  
  11.                 "dbo.Movie_Insert",  
  12.                 p => new  
  13.                     {  
  14.                         Name = p.String(),  
  15.                         ReleaseDate = p.DateTime(),  
  16.                         Category = p.String(),  
  17.                     },  
  18.                 body:  
  19.                     @"INSERT [dbo].[Movies]([Name], [ReleaseDate], [Category])  
  20.                       VALUES (@Name, @ReleaseDate, @Category)  
  21.                         
  22.                       DECLARE @ID int  
  23.                       SELECT @ID = [ID]  
  24.                       FROM [dbo].[Movies]  
  25.                       WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()  
  26.                         
  27.                       SELECT t0.[ID]  
  28.                       FROM [dbo].[Movies] AS t0  
  29.                       WHERE @@ROWCOUNT > 0 AND t0.[ID] = @ID"  
  30.             );  
  31.               
  32.             CreateStoredProcedure(  
  33.                 "dbo.Movie_Update",  
  34.                 p => new  
  35.                     {  
  36.                         ID = p.Int(),  
  37.                         Name = p.String(),  
  38.                         ReleaseDate = p.DateTime(),  
  39.                         Category = p.String(),  
  40.                     },  
  41.                 body:  
  42.                     @"UPDATE [dbo].[Movies]  
  43.                       SET [Name] = @Name, [ReleaseDate] = @ReleaseDate, [Category] = @Category  
  44.                       WHERE ([ID] = @ID)"  
  45.             );  
  46.               
  47.             CreateStoredProcedure(  
  48.                 "dbo.Movie_Delete",  
  49.                 p => new  
  50.                     {  
  51.                         ID = p.Int(),  
  52.                     },  
  53.                 body:  
  54.                     @"DELETE [dbo].[Movies]  
  55.                       WHERE ([ID] = @ID)"  
  56.             );  
  57.               
  58.         }  
  59.           
  60.         public override void Down()  
  61.         {  
  62.             DropStoredProcedure("dbo.Movie_Delete");  
  63.             DropStoredProcedure("dbo.Movie_Update");  
  64.             DropStoredProcedure("dbo.Movie_Insert");  
  65.         }  
  66.     }  
  67. }  

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

Update Database in Package Manager Console

Step 5

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

Stored Procedure in Entity Framework

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,

Stored Procedure in 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.


Similar Articles