For this chapter, you’ll need Visual Studio and SQL Server Management Studio (SSMS), as well as Postman. Remember that SSMS was installed with Visual Studio.

Note that you can also manage your database from within Visual Studio using the Server Explorer and the SQL Server Object Explorer.

The code files for this book are available in the book’s GitHub repository : https://github.com/PacktPublishing/Programming-APIs-with-C-Sharp-and-.NET/tree/main/Chapter02

The purpose of an API is to decouple the backend of an application (e.g., a database) from the frontend (e.g., a web application or mobile application), as illustrated in Figure 2.1.

Figure 2.1 – Separation of concerns

The client does not talk directly to the database but rather talks to the API, which, in turn, talks to the database. The huge advantage of this approach is that you can modify the database and the client does not have to change. Alternatively, you can modify the front end (for example, a website) without changing the database.

These modifications take place throughout the development of a large project and continue after delivery. It is imperative, especially with a mobile application, that you can modify the backend without forcing the user to update the application. Even with web applications, however, this can be critical, as the team maintaining the database may well not be the team maintaining...

The database we’ll be creating will be used in a simple (fictional) application for buying and selling cars. The database will keep a list of automobiles along with details of their engine, performance, and so on. A short example of this data is shown in Figure 2.2.

Figure 2.2 – A subset of the data table we’ll be using

Creating the Car Table

The data for the Car table is from the free Automobile dataset at https://kaggle.com. Download the data and import it into a database named Cars and a table named Car. The columns should take care of themselves.

The datasets on Kaggle change frequently, so your list of cars may look a bit different. All the datasets are all presented as .csv files, so just download one and open SSMS in order to do the import. Here are the steps:

1. Log into SSMS and create a database.
2. Create a table named CARS.
3. Start the Import Data Wizard. To do so, right-click on the table...

Our database consists of just one table: Car. As shown in Figure 2.2, that table must store various attributes of each car (name, miles per gallon, number of cylinders, and so on). Figure 2.3 shows the table:

Figure 2.3 – Car table columns

Notice the is_deleted column. We’ll be using “soft delete” – that is, rather than removing a row on deletion we’ll just set is_deleted to true (1). That allows us to easily restore that column just by changing that value back to 0 (false).

Other than id, all of the columns are strings, which will make working with them easier.

Car object

Corresponding to the Car table, our code has a Car entity (Cars/Data/Entities/Car.cs):

namespace Cars.Data.Entities;
public class Car
{
    public int Id { get; set; }
    public string name { get; set; } = null!;
    public string mpg { get; set; } = null...

To get started, create a new ASP.NET project using the ASP.NET Core Web API template. Put the files wherever is convenient for you and choose the latest version of .NET (this book was written with .NET 8).

The basic structure of our application will be as follows:

• Controllers with endpoints
• Services
• Repositories

We’ll review endpoints and all the rest of this as we go.

Program.cs

You won’t have to edit Program.cs for this application but it is worth a few minutes of your time to review it:

using Cars.Data;
using Cars.Data.Interfaces;
using Cars.Data.Repositories;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
// Load DB configuration and register the connection factory for 
...

To organize our application, we will create the following folders:

• Controllers
• Data
• Interfaces
• Repositories
• Services

Let’s briefly explain what these are:

• The client is the application calling the API (for example, the website or mobile application). The Controllers folder will hold methods that act as endpoints (an endpoint is what the client connects to via a URL).
• The Data folder will hold the definition of our entities – in our case, the Car object shown earlier.
• The Interfaces folder is just what it sounds like: it will hold the interfaces to our C# objects.
• The Repositories folder will hold the code between our methods and the database calls.
• The Services folder will hold supporting code.

The flow will be as follows:

1. The client calls a method in a controller.
2. The method calls a service to handle the business logic.
3. The service calls a method in the repository, which, in turn,...

In this chapter, you saw what an API is and how it is used to separate the front end (e.g., a web site) from the backend (e.g., a database). We also looked at the simple database and application that we’ll use throughout the book.

In order to demonstrate CRUD operations, we’ll build a simple application focused on a Car object as if we were working with an inventory of cars. We imported it from https://kaggle.com.

This lays the groundwork for the rest of the chapters and for the simple application that we will build. Our focus will be strictly on creating APIs, so we will spend little time on database technology or even on the frontend.

This is a good time to create your application and the database. If you are adventurous, create a similar but different application, database, and data entity. This will ensure that you cement the elements we’ll be using.