EF Database First with ASP.NET MVC: Setting up the Database

Introduction

This topic shows how to start with an existing database and quickly create a web application that enables users to interact with the data. It uses the Entity Framework 6 and MVC 5 to build the web application. The ASP.NET Scaffolding feature enables you to automatically generate code for displaying, updating, creating and deleting data. The topic also demonstrates how to customize the generated code to fulfill the requirements of your project.

This topic addresses the situation where you have a database and want to generate code for a web application based on the fields of that database. This approach is called Database First development. If you do not already have an existing database, you can instead use an approach called Code First development which involves defining data classes and generating the database from the class properties.

For an introductory example of Code First development, see Getting Started with ASP.NET MVC 5. For a more advanced example, see Creating an Entity Framework Data Model for an ASP.NET MVC 4 App.

For guidance on selecting which Entity Framework approach to use, see Entity Framework Development Approaches.

Prerequisites

Visual Studio 2013 RC or Visual Studio Express 2013 RC for Web

Set up the database

To mimic the environment of having an existing database, you will first create a database with some pre-filled data, and then create your web application that connects to the database.

Launch Visual Studio and open the Server Explorer pane. To add a new database, right-click Data Connections and select Add Connection.

If this is the first data connection you have added, you will be prompted to choose a Data source. Select Microsoft SQL Server. If you have added a data connection in the past, you might not see this dialog window.

If you have a server that you prefer to use, you can specify the name of that server; otherwise, to use the local database that comes with Visual Studio, specify (localdb)\v11.0 for the server name. Name the database ContosoUniversity.

Click OK. When prompted to create a new database, select Yes.

Your new database now appears under the Data Connections node. The database name may be prefixed with some additional identifiers. Right-click the database, and select New Query.

In the query window, you will add T-SQL commands to create 3 tables (Course, Student, and Enrollment), and add some example data to those tables. Add the following code to the query window:

CREATE TABLE [dbo].[Course] (
    [CourseID] INT           IDENTITY (1, 1) NOT NULL,
    [Title]    NVARCHAR (50) NULL,
    [Credits]  INT           NULL,
    PRIMARY KEY CLUSTERED ([CourseID] ASC)
);

CREATE TABLE [dbo].[Student] (
    [StudentID]      INT           IDENTITY (1, 1) NOT NULL,
    [LastName]       NVARCHAR (50) NULL,
    [FirstName]      NVARCHAR (50) NULL,
    [EnrollmentDate] DATETIME      NULL,
    PRIMARY KEY CLUSTERED ([StudentID] ASC)
);

CREATE TABLE [dbo].[Enrollment] (
    [EnrollmentID] INT IDENTITY (1, 1) NOT NULL,
    [Grade]        DECIMAL(3, 2) NULL,
    [CourseID]     INT NOT NULL,
    [StudentID]    INT NOT NULL,
    PRIMARY KEY CLUSTERED ([EnrollmentID] ASC),
    CONSTRAINT [FK_dbo.Enrollment_dbo.Course_CourseID] FOREIGN KEY ([CourseID]) 
        REFERENCES [dbo].[Course] ([CourseID]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Enrollment_dbo.Student_StudentID] FOREIGN KEY ([StudentID]) 
        REFERENCES [dbo].[Student] ([StudentID]) ON DELETE CASCADE
);

INSERT INTO Course
(Title, Credits) 
Values ('Economics', 3), ('Literature', 3), ('Chemistry', 4);

INSERT INTO Student
(LastName, FirstName, EnrollmentDate)
VALUES 
('Tibbetts', 'Donnie', '2013-09-01'), 
('Guzman', 'Liza', '2012-01-13'), 
('Catlett', 'Phil', '2011-09-03');

INSERT INTO Enrollment
(Grade, CourseID, StudentID)
VALUES 
(2.00, 1, 1),
(3.50, 1, 2),
(4.00, 2, 3),
(1.80, 2, 1),
(3.20, 3, 1),
(4.00, 3, 2);

Execute the query (Ctrl + Shift + E). The tables now exist in the database.

After executing the query, you can close the query window without saving the file.

To see that the tables are populated with data, right-click a table, and select Show Table Data.

An editable view of the table data is displayed.

Your database is now set up and populated with data. In the next tutorial, you will create a web application for the database.

By Tom FitzMacken, Tom FitzMacken is a Senior Programming Writer on the Web Platform & Tools Content team.

1.  
2.  You're ViewingSetting up the Database
3. → NextCreating the Web Application and Data Models