Database First Approach in MVC 5: Part 1

Introduction

This article shows the development of an ASP.NET Web application in an MVC 5 project template that connects with a database that exists already and allows the users to interact with the data. It is all possible using MVC, Entity Framework, and Scaffolding in Visual Studio 2013. I am describing this in various parts.

In that context, I am describing here the database connectivity with the Entity Framework Database First Approach. Using Scaffolding, we can generate code for Create, Read, Update, Delete (CRUD) Operations automatically. In this part, we will explain the Database composition with the web application. I am using Visual Studio 2013 RC in which MVC 5 and Entity Framework 6 are used.

Prerequisites

There are the following prerequisites to start this.

• Visual Studio 2013
• Database Source

Database Interaction

Before starting, you need to have a database with which you can interact with the web application based on MVC 5. If you don't have the database then create a database first and create some tables in that database. Now, let's start the setup of the database in Visual Studio 2013 using the following procedure.

Step 1. Open Visual Studio 2013 and in it open the Server Explorer to add a new Data Connection.

Note. If you are creating a new data connection for the first time, a Choose Data Source wizard will open and you need to choose Microsoft SQL Server from that menu.

Step 2. In the next wizard enter the server name and database name as entered in the following image.

Step 3. A new data connection is added to your Server Explorer. Now right-click on your connection and click on New Query.

Step 4. In the New Query window, enter the following queries.

CREATE TABLE Cricketers (
    ID int primary key identity (1,1),
    Name varchar(50),
    ODI int,
    Test int
)

CREATE TABLE Cricketer_ODI_Statistics (
    ODI_ID int primary key Identity (1,1),
    Cricketer_ID int foreign key references Cricketers (ID),
    Name varchar(50),
    Half_Century int,
    Century int
)

CREATE TABLE Cricketer_Test_Statistics (
    Test_ID int primary key Identity (1,1),
    Cricketer_ID int foreign key references Cricketers (ID),
    Name varchar(50),
    Half_Century int,
    Century int
)

CREATE TABLE Cricketer_Details (
    Details_ID int primary key identity (1,1),
    Cricketer_ID int foreign key references Cricketers (ID),
    Team varchar(50),
    ODI_Runs int,
    Test_Runs int,
    Wickets int
)

Insert into Cricketers (Name, ODI, Test)
values 
    ('Sachin Tendulkar', 463, 198),
    ('Saurav Ganguly', 311, 113),
    ('Rahul Dravid', 344, 164),
    ('Ricky Ponting', 375, 168),
    ('Wasim Akram', 356, 104),
    ('Jacques Kallis', 321, 162)

Insert into Cricketer_ODI_Statistics (Cricketer_ID, Name, Half_Century, Century)
Values 
    (1,'Sachin Tendulkar', 96, 49 ),
    (2,'Saurav Ganguly',72,22),
    (3,'Rahul Dravid',83,12),
    (4,'Ricky Ponting',82,30),
    (5,'Wasim Akram',6,3),
    (6,'Jacques Kallis',85,17)

Insert into Cricketer_Test_Statistics (Cricketer_ID, Name, Half_Century, Century)
Values 
    (1,'Sachin Tendulkar', 67, 51),
    (2,'Saurav Ganguly',35,16),
    (3,'Rahul Dravid',63,36),
    (4,'Ricky Ponting',62,41),
    (5,'Wasim Akram',7,3),
    (6,'Jacques Kallis',58,44)

Insert into Cricketer_Details (Cricketer_ID, Team, ODI_Runs, Test_Runs, Wickets)
Values 
    (1, 'India', 18426, 15837, 199),
    (2, 'India', 11363, 7212, 132),
    (3, 'India', 10889, 13288, 5),
    (4, 'Australia', 13704, 13378, 8),
    (5, 'Pakistan', 3717, 2898, 916),
    (6, 'South Africa', 11498, 13128, 558)

Step 5. Confirm that all tables now exist in your data connection.

Step 6. Right-click on any table to show the table data.

You can see your table data that can also be edited.

Summary

This article will help you to create a database and work with the existing database in Visual Studio 2013. You have now created a table and a database. In the next part, we will create a web application that interacts with the database.

• Next Article: DB First Approach: Part 2 >>