MVC3 ASP .Net DBContext Connection
Simple DBContext to Connect to SQL Server Database Table
This article describes how to create a simple MVC3 Project in Visual Web Developer 2010 Express and use a simple connection string, Model class, and DBContext class to populate the Controller and View with data from SQL Server.
Create a Table in SQL Server
CREATE TABLE [dbo].[AdvItems]
(
[Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
[AType] [nvarchar](50) NULL,
[AName] [nvarchar](250) NULL
)
Populate it with some data
Creating the MVC3 ASP .Net Application
In Visual Studio 2010, add a new ASP.Net MVC 3 Web Application.
Select Internet Application and click OK.
Adding MVC3 Models to the Project
Click Add New Item to add the Model to the Models folder.
Type in the name of the class and click OK.
Add the code for the Model, AdvItem. The names of collection should be the same as the names of the columns in the table.
namespace MvcSimpleDB.Models
{
public class AdvItems
{
public virtual int ID { get; set; }
public virtual string AType { get; set; }
public virtual string AName { get; set; }
}
}
Adding the MVC3 DbContext Class to the Data Layer
Add a new folder named DAL (Data Access Layer) and click Add New Item to add a Context class to it.
In the code for the DBContext Model in the DAL folder, add the following using statements to the top:
using MvcSimpleDB.Models;
using System.Data.Entity;
Add this code to the class so that it inherits from the DbContext Class.
public class AdvItemsContext : DbContext
{
public DbSet<AdvItems> AdvItemPkg { get; set; }
}
In the Web.config file, add the Connection String. Name it the same as the class created in the DBContext file just created.
<add name="AdvItemsContext" connectionString="Server=YourSQLServer\SQLEXPRESS;Database=Database1;Integrated Security=True;" providerName="System.Data.SqlClient" />
Adding the MVC3 Controller
Build the Mvc Project to ensure that the classes will be known to the controller when it is created next.
Right click on the Controller folder and click Add Controller. Name it <ClassName>Controller. In this case, AdvItemsController.
Add these using statements to the top.
using MvcSimpleDB.DAL;
using MvcSimpleDB.Models;
In the Controller code file, add this code to create an instance of the DbContext class and return the collection in the ActionResult Index method to the View.
namespace MvcSimpleDB.Controllers
{
public class AdvItemsController : Controller
{
private AdvItemsContext db = new AdvItemsContext();
public ActionResult Index()
{
var advItems = db.AdvItemPkg;
return View(advItems.ToList());
}
}
}
Adding the MVC3 View
Right click in the Controller next to the ActionResult Index to Add View.
Select the Model Class and the Layout page. The Model will be the 1st one created with the field names, not the Context class.
Add code to the View - in Index.cshtml. It adds a table to the rendered page and iterates through the collection to build the rows using Razor syntax and Linq Expressions with the data fields.
@model IEnumerable<MvcSimpleDB.Models.AdvItems>
@{
ViewBag.Title = "Adventure Items";
Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>Adventure Items</h2>
<table>
<tr>
<th>ID</th>
<th>
Type
</th>
<th>
Name
</th>
</tr>
@foreach (MvcSimpleDB.Models.AdvItems item in Model)
{
<tr>
<td>
@item.ID:
</td>
<td>
@Html.DisplayFor(modelItem => item.AType)
</td>
<td>
@Html.DisplayFor(modelItem => item.AName)
</td>
</tr>
}
</table>
Add to the code in the _Layout.cshtml file to include a new tab that will link to the Adventure items page.
<li>@Html.ActionLink("Adventures", "Index", "AdvItems")</li>
Build the Project and then run the application. Click on the Adventures tab when the Index page opens.
The data from the database table appears on the page!
