In this article, we will explore how to retrieve a database row as JSON using Dapper in the context of an ASP.NET Core Web API project. Our focus will be on data retrieval and serialization. We’ll exclude discussions of the intricacies of Dapper itself and the setup of the ASP.NET Core Web API project.
So, let’s dive in.
Project Setup
We’ll begin by utilizing Dapper to retrieve entities from a database. Subsequently, we’ll use the Newtonsoft.Json library to serialize the acquired data into JSON format.
Let’s install the necessary packages:
- Newtonsoft –
PM> Install-Package Newtonsoft.Json - Dapper –
PM> Install-Package Dapper
We will not go into details of Dapper setup in this article, but if you need a refresher, visit our Using Dapper with ASP.NET Core Web API article.
Interfaces and classes
We are going to adopt the Repository pattern along with a Service layer. To ensure a cohesive structure, we have defined IRepository and IService interfaces, both of which include a common GetById method:
public interface IRepository
{
public Task<dynamic?> GetById(int id);
}
public interface IService
{
public Task<dynamic?> GetById(int id);
}
After setting up our interfaces, let’s proceed to create their corresponding class implementations starting with the Service class:
public class Service : IService
{
private readonly IRepository _repository;
public Service(IRepository repository)
{
_repository = repository;
}
public async Task<dynamic?> GetById(int id)
{
return await _repository.GetById(id);
}
}
Here we simply inject our repository and use its GetById method.
Next, we implement the Repository class:
public class Repository : IRepository
{
private readonly ILogger<Repository> _logger;
private readonly DapperContext _context;
public Repository(ILogger<Repository> logger, DapperContext context)
{
_logger = logger;
_context = context;
}
public async Task<dynamic?> GetById(int id) { // implementation shown below }
}
Here we inject a logger along with the DapperContext, which we will use to query our database.
Finally, let’s register our interfaces and their implementations as a service in the Program class:
builder.Services.AddScoped<IRepository, Repository>(); builder.Services.AddScoped<IService, Service>();
Getting a Database Row as JSON
Next, let’s implement the GetById method in our Repository class:
public async Task<dynamic?> GetById(int id)
{
const string query = "SELECT * FROM Entities WHERE Id = @Id";
using (var connection = _context.CreateConnection())
{
var entity = await connection.QuerySingleOrDefaultAsync(query, new { id });
if (entity != null)
{
string json = JsonConvert.SerializeObject(entity, Formatting.Indented);
_logger.LogInformation("Object as JSON {json}", json);
}
else
{
_logger.LogInformation("Entity not found!");
}
return entity;
}
}
The GetById method takes an id parameter representing the unique identifier of the entity we want to retrieve from the database. We then define a parameterized SQL query in the query variable. The query selects all columns from the Entities table where the Id matches the provided Id.
We then use Dapper’s QuerySingleOrDefaultAsync method that executes a query asynchronously and returns a dynamic type or null. The result is then serialized to JSON by running: JsonConvert.SerializeObject(entity, Formatting.Indented). The JSON result is then logged to the console.
If you want to learn more about JSON and serialization in .NET visit our article How to Turn a C# Object Into a JSON String in .NET
Finally, the method returns the object, allowing the caller to work with the retrieved data.
API Controller
Now, let’s wrap up by creating a new Controller:
[ApiController]
[Route("api/entities")]
public class Controller : ControllerBase
{
private readonly ILogger<Controller> _logger;
private readonly IService _service;
public Controller(ILogger<Controller> logger, IService service)
{
_logger = logger;
_service = service;
}
[HttpGet("{id}")]
public async Task<IActionResult> GetById(int id)
{
try
{
var entity = await _service.GetById(id);
return entity == null ? NotFound() : Ok(entity);
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
}
Here we inject our service via Dependency Injection and use it to invoke the GetById method.
The GetById method handles HTTP GET requests and expects id parameter as part of the URL. We asynchronously call the GetById method in the service class with the expected id parameter. To learn more about ASP.NET Core Web API services, be sure to check out our ASP.NET Core Web API series.
Finally, let’s start our application and use Postman for testing:
We send a get request to our endpoint to retrieve a single entity.
Upon inspecting the Console, we observe the log:
{
"Id": 1,
"Make": "Mercedes Benz",
"Model": "GLC",
"Year": "2014",
"Color": "Grey"
}
Conclusion
This article demonstrates how to retrieve a database row as JSON using Dapper. This approach is particularly helpful when building web APIs or services that must generate JSON responses based on database queries.
