REST API with ASP.NET and MySQL
Tags: webdev, dotnet, api, mysql
Published: 2023-01-05 16:51:58

In this tutorial article, we will learn how to build a web API from ASP.NET  to handle CRUD operations with a database in MySQL. 

**_Source code:_** [ASP.NET Web API](https://github.com/esdanielgomez/ASP.NET6_WebAPI). 

**Resources required:**

To follow this article step by step or run the included demo, it is necessary to have the following tools in operation: 

- MySQL. 
- .NET SDK. 
- Visual Studio 2019/2022.
- The web development workload and ASP.NET for Visual Studio 2019/2022.

**Process to follow:**

In the tutorial, we will have three important parts: 

1. Review the database we are going to use. 
2. Establish database access from ASP.NET through the Entity Framework. 
3. Set the handlers and their methods for the web service. 

As a case study for this tutorial, user data will be handled through CRUD (Create, Read, Update, and Delete) operations.

##**1. The database for the application domain**.

The database that we will use in this example is made up of a single table called: `User`, with the attributes: `Id`, `FirstName`, `LastName`, `Username`, `Password`, and `EnrrollmentDate`; in MySQL.

![User Entity](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b8kvmlcgczhqgeqaj94v.png) 

The SQL statements for the creation of the `User` table is as follows:

```sql
CREATE TABLE `user` (
  `Id` INT NOT NULL PRIMARY KEY,
  `FirstName` VARCHAR(45) NOT NULL,
  `LastName` VARCHAR(45) NOT NULL,
  `Username` VARCHAR(45) NOT NULL,
  `Password` VARCHAR(45) NOT NULL,
  `EnrollmentDate` datetime NOT NULL 
);
```

Very well, with the database established, we can already start with the implementation of our first project for the development of API Rest services. 

##**2. Establish database access from ASP.NET through Entity Framework.**

**ASP.NET Web API project.**

In Visual Studio, the first thing we'll do is create a new project of type ASP.NET Core Web API:

![ASP.NET Core Web API project.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pzlmfrbmyr1sthgm2cb1.png)

Then, in the following steps we can specify the Framework.

![Select .NET version in the new project.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9w3vfss1jzkobu297ks2.png)

With this project, we'll create access to the database and implement a corresponding controller to work with that data and provide the web API.

**Database access with Entity Framework.**  

To establish the entities through classes and the connection of the database, we can use the _Database First_ approach of the Entity Framework, which allows us to _scaffolding_ from the database to the project, that is, generate classes automatically according to the entities established in the database and the connection in the project. 

For this purpose, it's necessary to install three NuGet packages: 

- `Microsoft.EntityFrameworkCore.Design`
- `Microsoft.EntityFrameworkCore.Tools`
- `MySql.EntityFrameworkCore`

In case you are working with SQL Server, the NuGet package to install will be: `Microsoft.EntityFrameworkCore.SQLServer`. 

**_Note:_** to find the admin center of nuGet packages, we can go to the option: menu -> project -> Manage NuGet packages.

![Go To Manage NuGet Packages option](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jonzng4xltlh7oubpgpr.png)

With the installation of these NuGet packages, we'll now open the package management console to write a command that will allow us to perform _scaffolding_ from the database:

![Option to open Package Manager Console](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q6d0cgklv0pxhj04kg4y.png)
 
**Command:**
```
Scaffold-DbContext "server=servername;port=portnumber;user=username;password=pass;database=databasename" MySql.EntityFrameworkCore -OutputDir Entities -f
```

The result is as follows:

![C# Entities](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7svp3b6w51eyzkhycagg.png)

Here, the `User` class is defined as follows:

```csharp
public partial class User
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public DateTime EnrollmentDate { get; set; }
}
```

And the `DBContext`, which has the configuration with the database, whose main method `OnConfiguring` will look something like this:

```csharp
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
  if (!optionsBuilder.IsConfigured)
  { optionsBuilder.UseMySQL("server=localhost;port=3306;user=root;password=;database=database");
  }
}
```

Now, it's not the most appropriate that the connection string to the database is specified in the `OnConfiguring` method. For this, within our project we can find the `appsettings.json` file, in which we can define this configuration:

```json
"AllowedHosts": "*",
"ConnectionStrings": {
  "DefaultConnection": "server=servername;port=portnumber;user=username;password=pass;database=databasename;"
}
```

Then, in the `Program` class we'll add as a service to the `DBContext`, and then we must reference the `DefaultConnection` property specified in the `appsettings.json` file:


```csharp
builder.Services.AddEntityFrameworkMySQL()
    .AddDbContext<DBContext>(options =>
    {
        options.UseMySQL(builder.Configuration.GetConnectionString("DefaultConnection"));
    });
                });
```

In this case, returning to the class of the `DBContext`, we delete the connection string specified in the `OnConfiguring` method. At the end we would have the empty method:

```csharp
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{}
```

With these steps, we have already ready the connection and the necessary configurations to work with the database in ASP.NET with the help of Entity Framework. 

##**3. Set the controllers and their methods for the web service.**
 
In order to transport the data between the processes for the management of the database and the processes for working with web services, it's advisable to establish DTO classes for each entity of the project, in this case, a DTO for the entity `User`.
 
To do this, we'll create a new folder within the project called DTO and create a class called `UserDTO`, whose attributes will be the same as the `User` class defined in the `Entities` section above:

```csharp
public class UserDTO
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public DateTime EnrollmentDate { get; set; }
}
```

**Controllers for the Web API.** 

Now what we'll do is add the controllers, in this case, the controller for the user, which will allow to establish methods to perform CRUD operations on the tables of the database and expose them through the Web API. On the `Controllers` folder, we'll add a controller called `UserController`:

![Create new Controller option](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t6pdro84c2wu3fyko875.png)
La definición de la clase y su constructor se verá así: 

```csharp
[ApiController]
[Route("api/[controller]")]
public class UserController : ControllerBase
{
    private readonly DBContext DBContext;

    public UserController( DBContext DBContext)
    {
        this.DBContext = DBContext;
    }
    ...
}
```

Now, the goal is to perform CRUD operations. In this sense, we'll use methods to access the information (Get), to insert data (Post), to modify (Put) and to delete a record (Delete).

The following is the final code for each of the methods:

**_A. Get the list of all users registrados._**


```csharp
[HttpGet("GetUsers")]
public async Task<ActionResult<List<UserDTO>>> Get()
{
    var List = await DBContext.User.Select(
        s => new UserDTO
        {
            Id = s.Id,
            FirstName = s.FirstName,
            LastName = s.LastName,
            Username = s.Username,
            Password = s.Password,
            EnrollmentDate = s.EnrollmentDate
        }
    ).ToListAsync();

    if (List.Count < 0)
    {
        return NotFound();
    }
    else
    {
        return List;
    }
}
```

**_B. Obtain the data of a specific user according to their Id._**

```csharp
[HttpGet("GetUserById")]
public async Task<ActionResult<UserDTO>> GetUserById(int Id)
{
    UserDTO User = await DBContext.User.Select(
            s => new UserDTO
            {
                Id = s.Id,
                FirstName = s.FirstName,
                LastName = s.LastName,
                Username = s.Username,
                Password = s.Password,
                EnrollmentDate = s.EnrollmentDate
            })
        .FirstOrDefaultAsync(s => s.Id == Id);

    if (User == null)
    {
        return NotFound();
    }
    else
    {
        return User;
    }
}
```

_**C. Insert a new user.**_

```csharp
[HttpPost("InsertUser")]
public async Task<HttpStatusCode> InsertUser(UserDTO User)
{
    var entity = new User()
    {
        FirstName = User.FirstName,
        LastName = User.LastName,
        Username = User.Username,
        Password = User.Password,
        EnrollmentDate = User.EnrollmentDate
    };

    DBContext.User.Add(entity);
    await DBContext.SaveChangesAsync();

    return HttpStatusCode.Created;
}
```

_**D. Update the data of a specific user.**_  

```csharp
[HttpPut ("UpdateUser")]
public async Task<HttpStatusCode> UpdateUser(UserDTO User)
{
    var entity = await DBContext.User.FirstOrDefaultAsync(s => s.Id == User.Id);

    entity.FirstName = User.FirstName;
    entity.LastName = User.LastName;
    entity.Username = User.Username;
    entity.Password = User.Password;
    entity.EnrollmentDate = User.EnrollmentDate;

    await DBContext.SaveChangesAsync();
    return HttpStatusCode.OK;
}
```

**_E. Delete a user based on their Id._** 

```csharp
[HttpDelete("DeleteUser/{Id}")]
public async Task<HttpStatusCode> DeleteUser(int Id)
{
    var entity = new User()
    {
        Id = Id
    };
    DBContext.User.Attach(entity);
    DBContext.User.Remove(entity);
    await DBContext.SaveChangesAsync();
    return HttpStatusCode.OK;
}
```

With these methods and the steps followed up to this point, the web service is ready to run.
 
**Test the implemented web API**

To test the implemented API we can use `Swagger UI`, a visual tool that allows us to interact with the methods of our service, and that in turn is already integrated into our ASP.NET project.

For testing, we need to build and run the application:

![Run app](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mep9qhxdtseayya37622.png)

Next, we can see the Swagger interface so that we can perform the corresponding tests according to the methods defined in our controller and in an interactive way:

![Swagger UI](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q905w5n6w1shfpf6ky4t.png)

As this is a RestFul service, we may use any other program or application to consume these services. For example, here we can see a call to the `GetUsers` method from the Postman tool:

![HTTP Get example](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wx44l75nddfqwjpwp79l.png)

##**What next?**

With this tutorial we have learned step by step how to implement HTTP services that handle user data from ASP.NET, and how to test with these functionalities.

The source code for this example can be viewed from the following repository on GitHub: [ASP.NET Web API](https://github.com/esdanielgomez/ASP.NET6_WebAPI).

**Thanks for reading:**

I hope you liked the article. If you have any questions or ideas in mind, it'll be a pleasure to be able to communicate with you and together exchange knowledge with each other.

See you on [Twitter](https://twitter.com/esDanielGomez) / [esDanielGomez.com](https://esdanielgomez.com)!

Regards!