UPDATE (8 Jun 2021): The sample project and code in this post have been upgraded to use .NET 5.0.
My team is finally embarking on a new project that we can use ASP.NET 5.0 for. I've also been waiting a long time to use the "micro-ORM" Dapper in one of our projects, and this new one fits the bill: we need incredible performance and minimal coding.
So what happens when the winds of change meet the waves of hope? You get mixed-up nautical imagery! And, hopefully, some insight into ASP.NET 5.0 .
In this post, we're going to create a very simple ASP.NET 5.0 application which uses Dapper to access data.
With all of that said, let's climb aboard the tutorial Titanic!
Step 0: The Sample Project
As with many of my posts, there is a sample project over on GitHub. You might want to use that to follow along with this post.
Step 1: Get the NuGet Package
First things first, we need to grab the NuGet package for Dapper. In Visual Studio, you can do this by right-clicking on your project file and selecting Manage NuGet Packages and then search for the Dapper package, like so:
With that installed, let's try creating a repository.
Step 2: Create an Employee Class and Repository
For this demo, I am not going to go over how to create a database or show a demo database with sample data; I don't have one available and it's a pain to make one. So let's assume we have a table Employee with columns for FirstName, LastName, ID, and DateOfBirth.
We can make a corresponding C# class Employee for this table, like so:
public class Employee
{
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime DateOfBirth { get; set; }
}
Now we need a sample repository. Let's call it EmployeeRepository and give it an interface so we can use ASP.NET 5.0's Dependency Injection setup.
Here's the interface, named IEmployeeRepository:
public interface IEmployeeRepository
{
Task<Employee> GetByID(int id);
Task<List<Employee>> GetByDateOfBirth(DateTime dateOfBirth);
}
Since this is an async implementation, note the use of the Task<> class (and refer to an earlier article of mine for a refresher on how async and await keywords work in ASP.NET.
Matthew Jones
Now we can work up a skeleton implementation of this repository, which we will call, unsurprisingly, EmployeeRepository. Here's what we're starting with:
public class EmployeeRepository : IEmployeeRepository
{
public async Task<Employee> GetByID(int id) { }
public async Task<List<Employee>> GetByDateOfBirth(DateTime dateOfBirth) { }
}
We also need to update our project's Startup class to include our new repository in the services layer:
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
public void ConfigureServices(IServiceCollection services)
{
services.AddTransient<IEmployeeRepository, EmployeeRepository>();
services.AddControllers();
}
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
//...
}
}
Next, we need to enable this repository to use Dapper. Before we can do that, however, we need it to be aware of what connection string we are using.
Step 3: Injecting IConfiguration
Very often in ASP.NET Core projects, Connection Strings are defined in the appSettings.json file:
{
"Logging": {
"LogLevel": {
"Default": "Debug",
"System": "Information",
"Microsoft": "Information"
}
},
"ConnectionStrings": {
"MyConnectionString": "MyConnectionString"
}
}
The problem is: how do we pass that connection string to the repository so it can create a SqlConnection object for Dapper to use?
ASP.NET 5.0 includes a IConfiguration object which can be injected into other classes. That injected instance will contain a method called GetConnectionString which we can use to obtain our connection string from the appSettings.json files. So, let's inject IConfiguration into our EmployeeRepository like so:
public class EmployeeRepository : IEmployeeRepository
{
private readonly IConfiguration _config;
public EmployeeRepository(IConfiguration config)
{
_config = config;
}
//Remainder of file is unchanged
}
Step 4: Creating a SqlConnection
With the injected IConfiguration now available to our repository, we can create a Dapper-enabled SqlConnection object that all of our repository's methods can utilize.
public class EmployeeRepository : IEmployeeRepository
{
private readonly IConfiguration _config;
public EmployeeRepository(IConfiguration config)
{
_config = config;
}
public IDbConnection Connection
{
get
{
return new SqlConnection(_config.GetConnectionString("MyConnectionString"));
}
}
//Remainder of file is unchanged
}
Step 5: Employee by ID
Let's first create a method to return employees by their ID.
To start, let's remember that the way Dapper works is by processing raw SQL and then mapping it to an object. Because our table columns and object properties share the same names, we don't need to do any special mapping here.
Here's the implementation of our GetByID method:
public class EmployeeRepository : IEmployeeRepository
{
//...
public async Task<Employee> GetByID(int id)
{
using (IDbConnection conn = Connection)
{
string sQuery = "SELECT ID, FirstName, LastName, DateOfBirth FROM Employee WHERE ID = @ID";
conn.Open();
var result = await conn.QueryAsync<Employee>(sQuery, new { ID = id });
return result.FirstOrDefault();
}
}
}
Step 6: Employees by Date of Birth
We also need to get all employees born on a particular date. Since we are now returning a collection of employees rather than a single one, the implementation of EmployeeRepository changes very slightly.
public class EmployeeRepository : IEmployeeRepository
{
//...
public async Task<List<Employee>> GetByDateOfBirth(DateTime dateOfBirth)
{
using (IDbConnection conn = Connection)
{
string sQuery = "SELECT ID, FirstName, LastName, DateOfBirth FROM Employee WHERE DateOfBirth = @DateOfBirth";
conn.Open();
var result = await conn.QueryAsync<Employee>(sQuery, new { DateOfBirth = dateOfBirth });
return result.ToList();
}
}
}
Step 7: Implement the Controller
The final step is creating a controller to which our EmployeeRepository can be injected. Here it is:
[Route("api/[controller]")]
[ApiController]
public class EmployeeController : ControllerBase
{
private readonly IEmployeeRepository _employeeRepo;
public EmployeeController(IEmployeeRepository employeeRepo)
{
_employeeRepo = employeeRepo;
}
[HttpGet]
[Route("{id}")]
public async Task<ActionResult<Employee>> GetByID(int id)
{
return await _employeeRepo.GetByID(id);
}
[HttpGet]
[Route("dob/{dateOfBirth}")]
public async Task<ActionResult<List<Employee>>> GetByID(DateTime dateOfBirth)
{
return await _employeeRepo.GetByDateOfBirth(dateOfBirth);
}
}
Summary
That's it! We've implemented Dapper into our ASP.NET 5.0 application! Take a look at the sample project over on GitHub if you want to see the whole thing.
With this infrastructure in place, using Dapper throughout the rest of my team's project should be a breeze. Or, at least, we hopefully won't encounter any project-sinking icebergs. All aboooooard!
Did I miss something about ASP.NET 5.0, Dapper, or anything else in this tutorial? Did you take my sample project and make it better? I want to hear about it! Sound off in the comments!
Happy Coding!
