Database First Approach With ASP.NET MVC Step By Step Part 1

This is part one of the series of Database First Approach with ASP.NET MVC. In this article, I will demonstrate you about Database First Approach in Entity Framework and will also show how to implement it with ASP.NET MVC. With that I will show you how to make CRUD operation functionality in ASP.NET MVC. We will also use the Scaffolding feature, using this we can generate code for Create, Read, Update and Delete.

What is Database First Approach

There are the following approach which is used to connect with database to application.

• Database First
• Model First
• Code First

Today, we will learn about Database First Approach. So, first question comes in the mind “What is Database First”. So, Database First is nothing but only a approach to create web application where database is available first and can interact with database. In this database, database is created first and after that we manage the code. The Entity Framework is able to generate a business model based on the tables and columns in a relational database.

Basically, there is some scenario where we need to create database first like all table, primary key and foreign key relationship and then on the basis of database we generate our code. So, this makes our life easy when we use Scaffolding feature with database first approach.

So, you need to create your database first in SQL Server. You can also use the MySQL or any other database source.



Create Database and Tables

To create a new database first open “Microsoft SQL Server Management Studio” and right click on Database node and choose New Database.



It will open a New Database Dialog where you can define your database structure. You need to provide the database name “TestDemo” and click OK.



It will add a new database for you. You can check it into the Object Explorer.



After creating the database, we need to create some table which will participate in CRUD operations. In this article, I am going to create two tables “Employee” and “Department” and make relationship between both.

To create these tables on database, we can use the following scripts.

Use TestDemo
go
CREATE TABLE [dbo].[Department](
    [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
    [DepartmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Employees](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Email] [nvarchar](max) NULL,
    [Age] [nvarchar](max) NULL,
    [Address] [nvarchar](max) NULL,
    [DepartmentId] [int] NULL,
 CONSTRAINT [PK_dbo.Employees] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([DepartmentId])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Department]
GO

So, final structure of the database will be like the following.



We can see that here we have created both the tables successfully.

Database First is nothing but only a approach to create web application where database is available first and can interact with the database. In this database, database is created first and after that we manage the code. The Entity Framework is able to generate a business model based on the tables and columns in a relational database.

Basically, there is some scenario where we need to create database first like all tables, primary key and foreign key relationship and then on the basis of database we generate our code.

In the next article, we will learn how to create our Model classes using database first approach step by step.

Thanks for reading this article, Hope you enjoyed this.