Database First Approach in .Net Web API 

Introduction:

The “DbfirstApproach” in Web API development refers to a specific approach where the database is designed and created first, and then the Web API is generated or scaffolded based on that existing database. 

Here’s how the DbfirstApproach works in Web API development: 

Step 1:  Create a database  

If you don’t have an existing database create a database based on your requirement. For Example, Let’s create a Database called HotelBokking.

create Database HotelBooking
use HotelBooking

create table Bookings
( Id int primary key identity(1,1),
[Name] Varchar(40),
StartDate Date,
EndDate date,
HotelId int foreign key references Hotel(Id))

Create table Hotel
(Id int primary key identity(1,1),
HotelName varchar(50))

Select*From Bookings

Select *From Hotel

Step 2: Install Packages 

Open Vs 2022 -> Create a WebAPI Project -> Ensure that the version of project to be .Net6.0 or.Net7.0.Mostly we create .Net6.0 version. 

Before entering into code, we must install some packages. 

• Microsoft.EntityFrameworkCore 
• Microsoft.EntityFrameworkCore.SqlServer 
• Microsoft.EntityFrameworkCore.Tools

Step 3: Scaffolding the Database

In app setting, we must give a connection string for interacting with database and the code.

"Server=(LocalDbservername);Database=DataBaseName;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true" 

After providing the connection string, open Package Manager console for scaffolding our database.

 Scaffold-DbContext "Server= (LocalDbservername);Database=Timesheet;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models 

After scaffolding, the models are created by itself according to our database.

Step 4: Configuration  

In Program.cs we must configure our dbcontext to execute the program.

builder.Services.AddDbContext<Your-DbcontextName>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("ConnectionStringName"))); 

Step 5: CRUD Operations 

That’s all about connecting the database. Now we will perform CRUD Operations for our Database. 

• Select the controller folder from the solution explorer, right click the controller —>Add —> click the Controller
• After clicking it, select the API option —-> select API Controller with actions, using entity framework and click Add. 
• In the Add API Controller with actions using Entity Framework dialog 
  –> Select your Model Class name  . 
  –> Add your DbContext name by drop down and click Add. 
  –> Create a controller name and click Add 

After Clicking the add, the controller will generate the code automatically in the project. 

If you want to implement your own code then click the empty controller. 

Now execute the project.

The API screen will be opened with your controller name where you can see the HTTP method.

Conclusion:

• The DbfirstApproach can be beneficial in scenarios where the database design is already established. 
• It helps maintain consistency between the database and the API models.
• It is important to note that the DbfirstApproach may not be suitable for all scenarios. In such cases, approaches like “code-first” or “model-first” may be more appropriate. 

I hope you have a clear understanding of Dbfirst approach. We will see more interesting concepts in Webapi in upcoming blogs.