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 to you the 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 the Database First Approach?

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

• Database First
• Model First
• Code First

Today, we will learn about the Database First Approach. So, the first question that comes to mind is “ What is Database First&rdquo? So, Database First is nothing but only an approach to creating a web application where the database is available first and can interact with a database. In this database, a 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 the database first, like all tables, primary key, and foreign key relationships, and then on the basis of the database, we generate our code. So, this makes our life easy when we use the Scaffolding feature with a 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 in the Object Explorer.

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

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

Use TestDemo
go

CREATE TABLE [dbo].[Department](
    [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
      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, the 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 an approach to creating a web application where the database is available first and can interact with the database. In this database, the 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 the database first like all tables, primary key, and foreign key relationship and then on the basis of the database, we generate our code.

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

Thanks for reading this article, Hope you enjoyed this.