Display Data using a Stored Procedure in an ASP.Net MVC 5.0 application with 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 and more particularly how to display data using a stored procedure. I have posted something similar in this post https://weblogs.asp.net/dotnetstories/using-stored-procedures-in-an-asp-net-mvc-5-0-application-using-entity-framework-6-0 but this post described in detail how to insert, update and delete data in an ASP.Net MVC 6.0 application using stored procedures but not how to display data using a stored procedure.
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 2015,C# 5.0 and LocalDb in this demo.
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.
We will create the ASP.NET Web Application with the MVC 5 Project Template.
1) Launch Visual Studio 2015 and click on the "New Project". Select 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 and then click OK.
3) Visual Studio 2105 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. We will use Data Annotations and not the Fluent API. Have a look at the code below.
using System.ComponentModel.DataAnnotations;
namespace EF6StoredProcMVC.Models
{
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) 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
6) In the Add Scaffold wizard, select the MVC 5 Controller with views,using Entity Framework and click Add.
Have a look at the picture below
7) In the next Add Controller wizard window, select the Model Class (Footballer) and add the Data Context class.Create a Data Context class with a suitable name and select a name for the controller.Finally click Add.
I will use the async controller actions option.
Have a look at the picture below
8) Have a look at the generated FootballerDBContext.cs
using System.Data.Entity;
namespace EF6StoredProcMVC.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 System.Data.Entity.DbSet<EF6StoredProcMVC.Models.Footballer> Footballers { get; set; }
}
}
9) Have a look at the FootballersController.cs that was also generated. Υou will see methods for displaing, adding, editing and deleting data. All this code was generated through the magic of scaffolding. Take your time to study the code below.
using System.Data.Entity;
using System.Threading.Tasks;
using System.Net;
using System.Web.Mvc;
using EF6StoredProcMVC.Models;
namespace EF6StoredProcMVC.Controllers
{
public class FootballersController : Controller
{
private FootballerDBContext db = new FootballerDBContext();
// GET: Footballers
public async Task<ActionResult> Index()
{
return View(await db.Footballers.ToListAsync());
}
// GET: Footballers/Details/5
public async Task<ActionResult> Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = await db.Footballers.FindAsync(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 async Task<ActionResult> Create([Bind(Include = "FootballerID,FirstName,LastName,Weight,Height")] Footballer footballer)
{
if (ModelState.IsValid)
{
db.Footballers.Add(footballer);
await db.SaveChangesAsync();
return RedirectToAction("Index");
}
return View(footballer);
}
// GET: Footballers/Edit/5
public async Task<ActionResult> Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = await db.Footballers.FindAsync(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 async Task<ActionResult> Edit([Bind(Include = "FootballerID,FirstName,LastName,Weight,Height")] Footballer footballer)
{
if (ModelState.IsValid)
{
db.Entry(footballer).State = EntityState.Modified;
await db.SaveChangesAsync();
return RedirectToAction("Index");
}
return View(footballer);
}
// GET: Footballers/Delete/5
public async Task<ActionResult> Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = await db.Footballers.FindAsync(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// POST: Footballers/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public async Task<ActionResult> DeleteConfirmed(int id)
{
Footballer footballer = await db.Footballers.FindAsync(id);
db.Footballers.Remove(footballer);
await db.SaveChangesAsync();
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}
10) 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.
Have a look at the picture below to see the created database.
When you open the database, you will see the table with the appropriate columns created. Have a look at the picture below.
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.
11) After you insert those records in the database then you would wonder what is the T-SQL that EF DBContext sends 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.Threading.Tasks;
using System.Net;
using System.Web.Mvc;
using EF6StoredProcMVC.Models;
using System.Diagnostics;
namespace EF6StoredProcMVC.Controllers
{
public class FootballersController : Controller
{
private FootballerDBContext db = new FootballerDBContext();
public FootballersController()
{
db.Database.Log = T => Debug.Write();
}
// GET: Footballers
public async Task<ActionResult> Index()
{
return View(await db.Footballers.ToListAsync());
}
// More code follows
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.
12) Now we need to display the Footballers with a stored procedure.
First we need to add the stored procedure to the database. Open the SQLServer Object Explorer. Select and expand the database and in the Programmability node, select Stored Procedures and then add a new stored procedure.
The stored procedure is really easy to code.
Create Proc DisplayFootballers
AS
BEGIN
/*selecting all records from the table Footballers*/
Select * From dbo.Footballers
END
After you type it , then click Update. The stored procedure will be created and will be part of the database.
Have a look at the picture below.
13) Now we need to make changes to the FootballersController.cs and more particularly in the Index() method.
I have commented out the code that was created automatically by the scaffolding mechanism.
// GET: Footballers
//public async Task<ActionResult> Index()
//{
// return View(await db.Footballers.ToListAsync());
//}
and replaced it with the code below.
// GET: Footballers
public async Task<ActionResult> Index()
{
string commandText = "DisplayFootballers";
return View(await db.Database.SqlQuery<Footballer>(commandText).ToListAsync());
}
As you can see, I just create a string variable with the name of the stored procedure. Then I call the SqlQuery (https://msdn.microsoft.com/en-us/library/system.data.entity.database.sqlquery(v=vs.113).aspx ) method passing the name of the stored procedure. The SqlQuery method will return elements of the given type (Footballer) after the execution of the stored procedure.
Build and run your application. Now when you add data to the application and click Save, this data is displayed on the screen through the stored procedure.
Have a look at the Output window below
As you can see the data (the list of the Footballers) is displayed now through the stored procedure. More posts will follow regarding ASP.Net MVC 6 (ASP.Net MVC Core 1.0) applications and Entity Framework 7 (EF Core 1.0).
Hope it helps!!!