In this tutorial, we are going to make dotnet core web api application and perform all the CRUD (create, read, update and delete). I have tried to keep it simple and avoided any complexities like repository pattern.
Tech and tools used
- Dotnet 10
- MySql 8+ (in docker container. Click here, if you want to create a MySql container in docker)
- Dapper (ORM)
- .NET CLI and VS Code (alternate Visual Studio 2025)
Create database
Let’s start with creating a database and a table. You need to execute this script.
create database PersonDb;
use PersonDb;
create table People
(
Id int primary key auto_increment,
FirstName varchar(30) not null,
LastName varchar(30) not null
);
Id is a primary key and it will be generated automatically by the increment of 1 (eg. 1, 2, 3, 4).
Let’s add few rows for testing (which is optional).
insert into People(FirstName, LastName)
values ('John','Doe'),
('Ravindra','Devrani'),
('Jane', 'Doe');
Create a new dotnet api project
Execute these commands in a sequence
dotnet new sln -o DapperMysql
cd DapperMysql/
dotnet new webapi --use-controllers -n DapperMysql.Api
dotnet sln add DapperMysql.Api/DapperMysql.Api.csproj
#To open project in vs code
code .
Open the integrated terminal of vs code by pressing ctrl + `.
# vs code integrated terminal
# visit to the directory of the project where csproj is located
cd ./DapperMysql.Api/
Now, You need to install two nuget packages. Commands will vary for CLI and Visual Studio 2025.
In dotnet cli/vscode:
dotnet add package Dapper
dotnet add package MySql.Data
In visual studio 2025:
Install-Package Dapper
Install-Package MySql.Data
Connection string
appsettings.json:
"ConnectionStrings": {
"default": "Server=localhost;database=PersonDb;Uid=root;Pwd=p@55w0rd;Charset=utf8;Port=3306"
}
Use port, uid and password accordingly.
Defining models
We need to define classes to which takes data from client or return data to client. These are basically DTOs (data transfer object). Create a directory named Models.
PersonCreate class:
// Models/PersonCreate.cs
using System.ComponentModel.DataAnnotations;
namespace DapperMysql.Api.Models;
public class PersonCreate
{
[Required]
[MaxLength(30)]
public string FirstName { get; set; } = null!;
[Required]
[MaxLength(30)]
public string LastName { get; set; } = null!;
}
We are opting Id out. Because it is generated by database, so client do not need to pass Id.
PersonUpdate class:
// Models/PersonUpdate.cs
using System.ComponentModel.DataAnnotations;
namespace DapperMysql.Api.Models;
public class PersonUpdate
{
public int Id { get; set; }
[Required]
[MaxLength(30)]
public string FirstName { get; set; } = null!;
[Required]
[MaxLength(30)]
public string LastName { get; set; } = null!;
}
PersonRead class:
using System;
// Models/PersonRead
namespace DapperMysql.Api.Models;
public class PersonRead
{
public int Id { get; set; }
public string FirstName { get; set; } = null!;
public string LastName { get; set; } = null!;
}
Controller
Create a new Class or ApiController inside the Controllers folder. It’s boiler plate code should look like this (or add this boiler plate if you are creating a plain class).
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
namespace DapperMysql.Api.Controllers;
[Route("api/[controller]")]
[ApiController]
public class PeopleController : ControllerBase
{
}
Inject IConfiguration and retrieve ConnectionString which is defined in appsettings.js.
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
namespace DapperMysql.Api.Controllers;
[Route("api/[controller]")]
[ApiController]
public class PeopleController : ControllerBase
{
private readonly IConfiguration _config;
private readonly string _connectionString;
public PeopleController(IConfiguration config)
{
_config = config;
_connectionString = _config.GetConnectionString("default") ?? throw new InvalidOperationException("Connection string does not found");
}
}
Now we will create our endpoints.
Get all:
[HttpGet()]
public async Task<IActionResult> GetPeople()
{
try
{
using IDbConnection connection = new MySqlConnection(_connectionString);
// Note, using statement take care of disposing the connection
string query = "select Id, FirstName, LastName from People;";
var people = await connection.QueryAsync<PersonRead>(query);
return Ok(people);
}
catch (Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
}
}
Get one person:
[HttpGet("{id}", Name = "GetPerson")]
public async Task<IActionResult> GetPerson(int id)
{
try
{
using IDbConnection connection = new MySqlConnection(_connectionString);
string query = "select Id, FirstName, LastName from People where Id=@id";
PersonRead? person = await connection.QuerySingleOrDefaultAsync<PersonRead>(query, new { id });
// check for not found and return status code accordingly
if (person is null)
{
return NotFound($"Person with id: {id} does not found.");
}
return Ok(person);
}
catch (Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
}
}
Create a person :
[HttpPost]
public async Task<IActionResult> CreatePerson(PersonCreate personCreate)
{
try
{
using IDbConnection connection = new MySqlConnection(_connectionString);
string query = @"insert into People(FirstName,LastName)
values(@FirstName,@LastName); select last_insert_id();";
int createdId = connection.ExecuteScalar<int>(query, personCreate);
PersonRead createdPerson = new()
{
Id = createdId,
FirstName = personCreate.FirstName,
LastName = personCreate.LastName
};
return CreatedAtRoute("GetPerson", new { id = createdId }, createdPerson); // GetPerson is a Name property defined in GetPerson() method's HttpGet() Attribute
}
catch (Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
}
}
Update a person:
[HttpPut("{id}")]
public async Task<IActionResult> UpdatePerson(int id, [FromBody] PersonUpdate personUpdate)
{
if (id != personUpdate.Id)
{
return BadRequest("Ids mismatch");
}
try
{
using IDbConnection connection = new MySqlConnection(_connectionString);
// confirm person with this exists
int personCount = await connection.ExecuteScalarAsync<int>("select count(1) from People where Id=@id", new { id });
// check for not found and return status code accordingly
if (personCount == 0)
{
return NotFound($"Person with id: {id} does not found.");
}
string updateQuery = @"
update People
set FirstName=@FirstName,
LastName=@LastName
where Id=@Id;
";
await connection.ExecuteAsync(updateQuery, personUpdate);
return NoContent(); // returns 204 NoContent status code
}
catch (Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
}
}
Delete a person:
[HttpDelete("{id}")]
public async Task<IActionResult> DeletePerson(int id)
{
try
{
using IDbConnection connection = new MySqlConnection(_connectionString);
// confirm person with this exists
int personCount = await connection.ExecuteScalarAsync<int>("select count(1) from People where Id=@id", new { id });
// check for not found and return status code accordingly
if (personCount == 0)
{
return NotFound($"Person with id: {id} does not found.");
}
string deleteQuery = "delete from People where Id=@Id;";
await connection.ExecuteAsync(deleteQuery, new { id });
return NoContent(); // returns 204 NoContent status code
}
catch (Exception ex)
{
return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
}
}
Testing APIs
I am using VS Code Rest Client extension, which is also kind of built in Visual Studio. You can use other api testing client like postman or insomnia.
For rest client, you need to create a file name person.http in DapperMysql.Api directory (however you can create it anywhere).
person.http:
@base_address = http://localhost:5149/api/people
GET {{base_address}}
###
GET {{base_address}}/4
###
POST {{base_address}}
Content-Type: application/json
{
"firstName": "Tim",
"lastName": "Storm"
}
###
PUT {{base_address}}/5
Content-Type: application/json
{
"id":5,
"firstName": "Sue",
"lastName": "Storm"
}
###
DELETE {{base_address}}/4
To execute an Api, you will see SendRequest link about http verb like GET, POST, DELETE, PUT.
