Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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

Data in Table

Creating the MVC3 ASP .Net Application

In Visual Studio 2010, add a new ASP.Net MVC 3 Web Application.

New MVC 3 App

Select Internet Application and click OK.

Internet App

Adding MVC3 Models to the Project

Click Add New Item to add the Model to the Models folder.

Add New Item

Type in the name of the class and click OK.

Adding a Class

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.

Add DBContext

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.

Controller

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.

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.

View

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.

MVC 3 Project

The data from the database table appears on the page!

MVC 3 Data