Using Stored Procedures in an ASP.Net MVC 5.0 application using Entity Framework 6.0
In this post I will be looking into EF 6.0 using the Code First Workflow and its support for stored procedures.
I will be building a simple ASP.Net MVC 5.0 application that will be the client application that will consume the Entity Framework data access layer.
I will be using Visual Studio 2013,C# 5.0 and LocalDb in this demo.
While I am building this simple ASP.Net application I will point out to various tools and option in VS 2013 that make the completion of an application faster.
I will be writing as less code as possible. I will be leveraging the scaffolding functionality as much as possible.
I will also show you how to log queries sent to the database by Entity Framework 6.0.
With EF 6.0 we do have a mechanism to trace/profile everything EF sents to the data store.
I will also demonstrate how you can install Entity Framework Power Tools that enables developers to visualise the POCO classes in an entity designer.
We will create the ASP.NET Web Application with the MVC 5 Project Template.
1) Launch Visual Studio 2013 and click on the "New Project". Select the Web from the left pane and create the ASP.NET Web Application. Have a look at the picture below
2) Select the MVC Project Template as shown below.
3) Visual Studio 2103 will automatically create the ASP.Net MVC 5.0 application.In the Models folder we will add a new class file, Footballer.cs
Right-click on the Models folder and Add a new Class, Footballer.cs.
public class Footballer
{
public int FootballerID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public double Weight { get; set; }
public double Height { get; set; }
}
4) We will add declarative code in this simple class in order to define that we need the properties (columns in the database) to be required - not null fields in the database.
We need to reference the System.ComponentModel.DataAnnotations assembly. Have a look at the code below.
using System.ComponentModel.DataAnnotations;
namespace EF6StoreProcMVC5.Models
{
public class Footballer
{
public class Footballer
{
public int FootballerID { get; set; }
[Required]
public string FirstName { get; set; }
[Required]
public string LastName { get; set; }
[Required]
public double Weight { get; set; }
[Required]
public double Height { get; set; }
}
}
}
5) Entity Framework is already installed in our application. Looking into packages.config I see that i have the
<package id="EntityFramework" version="6.1.1" targetFramework="net451" />
I need to update to the latest version of the Entity Framework. YI update the package by entering the following command in the Package Manager Console:
Update-Package EntityFramework
Have a look at the picture
Now Entity Framework in my solution is updated to 6.1.3.
6) Now I am going to build the Controller. I am going to the Controllers folder and click Add --> New Scaffolded Item.
Have a look at the picture below
7) In the Add Scaffold wizard, select the MVC 5 Controller with views.
Have a look at the picture below
8) In the next Add Controller wizard, select the Model Class and add the Data Context class, select a suitable name for that and select a name for the controller and click Add
Have a look at the picture below
9) Have a look at the generated FootballerDBContext.cs
using System.Data.Entity;
namespace EF6StoreProcMVC5.Models
{
public class FootballerDBContext : DbContext
{
// You can add custom code to this file. Changes will not be overwritten.
//
// If you want Entity Framework to drop and regenerate your database
// automatically whenever you change your model schema, please use data migrations.
// For more information refer to the documentation:
// http://msdn.microsoft.com/en-us/data/jj591621.aspx
public FootballerDBContext() : base("name=FootballerDBContext")
{
}
public DbSet<Footballer> Footballers { get; set; }
}
}
At this point if you want to visualise the Footballer entity then you need to install the EF Power Tools. You must go to Tools and then Extensions and Updates.
Then do a search for Entity Framework Power Tools and then install them.
Have a look at the picture below
You can then select the FootballerDBContext.cs and then Entity Framework --> View Entity Data Model. Have a look at the picture below.
Have a look at the picture below to see the entity in the designer. This is very handy tool if you want to visualise your entities.
10) Have a look at the FootballersController.cs that was also generated.
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using EF6StoreProcMVC5.Models;
namespace EF6StoreProcMVC5.Controllers
{
public class FootballersController : Controller
{
private FootballerDBContext db = new FootballerDBContext();
// GET: Footballers
public ActionResult Index()
{
return View(db.Footballers.ToList());
}
// GET: Footballers/Details/5
public ActionResult Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = db.Footballers.Find(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// GET: Footballers/Create
public ActionResult Create()
{
return View();
}
// POST: Footballers/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 = "FootballerID,FirstName,LastName,Weight,Height")] Footballer footballer)
{
if (ModelState.IsValid)
{
db.Footballers.Add(footballer);
db.SaveChanges();
return RedirectToAction("Index");
}
return View(footballer);
}
// GET: Footballers/Edit/5
public ActionResult Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = db.Footballers.Find(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// POST: Footballers/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 = "FootballerID,FirstName,LastName,Weight,Height")] Footballer footballer)
{
if (ModelState.IsValid)
{
db.Entry(footballer).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
return View(footballer);
}
// GET: Footballers/Delete/5
public ActionResult Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = db.Footballers.Find(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// POST: Footballers/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
Footballer footballer = db.Footballers.Find(id);
db.Footballers.Remove(footballer);
db.SaveChanges();
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}
11) You can also have a look at the generated views that were created by the Scaffold wizzard
We choose to open the _Layout.cshtml view and add the following line of code in the navigation pane
<li>@Html.ActionLink("Footballers", "Index", "Footballers")</li>
the full code is
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Footballers", "Index", "Footballers")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
Now we can navigate to our page through the menu.
Build and run your application.It will take some time to appear in the first time since the database is created.The database is under the App_Data special folder.
When you will see you application, click on Footballers from the menu and then add some sample data. Have a look at the picture below.
12) After you insert those records in the database then you would wonder what is the T-SQL that EF DBContext sent to the SQL Server LocalDb to execute.
There are various ways to profile the T-SQL statements that EF sends to the data store. Have a look here, here and here for some ways of profiling the data in earlier versions of EF.
You can always use the SQL Server Profiler. In EF 6.0 we can trace the T-SQL statements using the the Log property of DbContext.
I am going to add some code in the FootballersController.cs class file in order to intercept the T-SQL statements.
I am going to add inside the FootballersController constructor the following code.
public FootballersController()
{
db.Database.Log = T => Debug.Write(T);
}
Make sure you add a reference to the System.Diagnostics assembly.
Have a look below to see where above statement fits with everything.
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using EF6StoreProcMVC5.Models;
using System.Diagnostics;
namespace EF6StoreProcMVC5.Controllers
{
public class FootballersController : Controller
{
private FootballerDBContext db = new FootballerDBContext();
public FootballersController()
{
db.Database.Log = T => Debug.Write(T);
}
// GET: Footballers
public ActionResult Index()
{
return View(db.Footballers.ToList());
}
Now If I build and run my application again and try to insert more records and I have my Output window open i can see wha is sent to the database.
Have a look at the picture below. Αs you can see a transaction is opened and values are inserted using an Insert statement. Then the transaction is commited. So we can see the complete T-SQL code. Note that when we have an Insert statement EF engine starts a transaction.
13) We want to use stored procedures instead of Insert,Update,Delete statements. We can have the EF engine generate them. We need to enable Code First Migrations
Go to Tools-> NuGet Package Manager->Package Manager Console and enter the following command and hit Enter:
Enable-Migrations -ContextTypeName EF6StoreProcMVC5.Models.FootballerDBContext
You have to put your own DBContext class where I put EF6StoreProcMVC5.Models.FootballerDBContext.
Have a look at the files generated under the Migrations folder.
Code first Migrations were enabled for the project.
Now we must tell the DbContext class that must use the Stored Procedures. Do open the FootballerDBContext class and update the code as shown below:
We just add this method and keep everything else.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Footballer>().MapToStoredProcedures();
}
We do override the OnModelCreating method.
14) Now we need to generate the stored procedures
Go to Tools-> NuGet Package Manager->Package Manager Console and enter the following command and hit Enter:
Add-Migration MyfootballSPs
The 201503282306498_MyfootballSPs.cs is generated. Have a look at the code below to see the Stored Procedures.
namespace EF6StoreProcMVC5.Migrations
{
using System;
using System.Data.Entity.Migrations;
public partial class MyfootballSPs : DbMigration
{
public override void Up()
{
CreateStoredProcedure(
"dbo.Footballer_Insert",
p => new
{
FirstName = p.String(),
LastName = p.String(),
Weight = p.Double(),
Height = p.Double(),
},
body:
@"INSERT [dbo].[Footballers]([FirstName], [LastName], [Weight], [Height])
VALUES (@FirstName, @LastName, @Weight, @Height)
DECLARE @FootballerID int
SELECT @FootballerID = [FootballerID]
FROM [dbo].[Footballers]
WHERE @@ROWCOUNT > 0 AND [FootballerID] = scope_identity()
SELECT t0.[FootballerID]
FROM [dbo].[Footballers] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[FootballerID] = @FootballerID"
);
CreateStoredProcedure(
"dbo.Footballer_Update",
p => new
{
FootballerID = p.Int(),
FirstName = p.String(),
LastName = p.String(),
Weight = p.Double(),
Height = p.Double(),
},
body:
@"UPDATE [dbo].[Footballers]
SET [FirstName] = @FirstName, [LastName] = @LastName, [Weight] = @Weight, [Height] = @Height
WHERE ([FootballerID] = @FootballerID)"
);
CreateStoredProcedure(
"dbo.Footballer_Delete",
p => new
{
FootballerID = p.Int(),
},
body:
@"DELETE [dbo].[Footballers]
WHERE ([FootballerID] = @FootballerID)"
);
}
public override void Down()
{
DropStoredProcedure("dbo.Footballer_Delete");
DropStoredProcedure("dbo.Footballer_Update");
DropStoredProcedure("dbo.Footballer_Insert");
}
}
}
15) We need to tell the database to create the MyfootballSPs. Enter the following command in the Package Manager Console:
Update-Database
and hit Enter. Go to Server Explorer and open the database. Have a look under Stored Procedures. The stored procedures are created.
Have a look at the picture below to see what I got when I did update the database.
16) We want to check if Entity Framework is now using the stored procedures. Do run the application again and add some footballers in the web page.
Have a look at the picture below to see what I got in the Output window:
I can see clearly that the Insert statements are not used and instead of that the dbo.Footballer_Insert is used.
In this post we did create a small ASP.Net MVC 5.0 application and we did go through on how to use stored proecedures with Code Firsr EF workflow.
Hope it helps!!!