This article provides the basic setup required to use Dapper with .NET's dependency injection. This example uses this table:
|
1 2 3 4 5 6 |
CREATE TABLE [dbo].[tag]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [nvarchar](40) NULL, [dateadded] [datetime] NULL, [dateupdated] [datetime] NULL, ) ON [PRIMARY] |
which has this corresponding C# model in the project's Models folder.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
using System; namespace DapperCrud.Models; public partial class Tag { /* | Model type: table */ public int id { get; set; } public string? name { get; set; } public System.DateTime? dateadded { get; set; } = null; public System.DateTime? dateupdated { get; set; } = null; } |
Step 1. Add the Dapper and Microsoft.Data.SqlClient Nuget packages to your project.
Dapper is a performant and lean alternative to Entity Framework. The basic package works very well and there are a slew of Dapper extensions to expand its capabilities.
The Microsoft.Data.SqlClient is an improved version of the old System.Data.SqlClient. It is mostly call-level compatible with System.Data.SqlClient but there are some differences in "less-used" APIs. See the porting sheet for more information.
Step 2. Add a SQL Server connection string to the appsettings.json file
|
1 2 3 4 5 6 7 |
{ "ConnectionStrings": { "SqlConnection": "Data Source=.;Initial Catalog=... }, "Logging": { "LogLevel": { ... |
Figure 1. Add a connection string to the appsettings.json file.
Step 3. Create a Repository folder in the root of your app. Add this DapperConnectionProvider class to that folder
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
using System.Data; namespace DapperWork; public class DapperConnectionProvider { private readonly IConfiguration _configuration; private readonly string _connectionString; public DapperConnectionProvider(IConfiguration configuration) { _configuration = configuration; _connectionString = _configuration.GetConnectionString("SqlConnection"); } public IDbConnection Connect() => new Microsoft.Data.SqlClient.SqlConnection(_connectionString); } |
Figure 2. The DapperConnectionProvider class.
The DapperConnectionProvider does not provide a database connection, rather it provides access the Microsoft.Data.SqlClient.SqlConnection object. You'll see in a moment how and where the database connection is established.
Step 4. Add a repository interface
Conical use of dependency injection suggests that you inject interfaces so that it's easy to later swap out concrete class implementations. In theory, this would allow you to easily swap out one database for another (eg, swap out a SQL Server-specific repository for one that uses SQLite).
We'll use an interface first here, but will soon revisit this topic. The repository interface defines data access methods. This example has one method, which returns an IEnumerable list of Tag objects. Add this interface to the project's Repository folder.
|
1 2 3 4 5 6 7 8 |
using DapperCrud.Models; namespace DapperWork; public interface IAppRepository { public Task<IEnumerable<Tag>> GetTags(); } |
Figure 3. The IAppRepository interface.
Step 5. Add the concrete implementation of the IAppRepository interface
Add the AppRepository class to your project's Repository folder. It provides the concrete implementation of the IAppInterface. See the Dapper docs for help understanding the code in the GetTags() method.
The AppRepository class's constructor pulls in the DapperConnectionProviderinstance. This is the first of two dependency injections that occur with this example. We'll later see how .NET injects the AppRepository class into a controller.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
using DapperCrud.Models; using Dapper; namespace DapperWork; public class AppRepository : IAppRepository { private readonly DapperConnectionProvider _dapperProvider; public AppRepository(DapperConnectionProvider dapperProvider) { _dapperProvider = dapperProvider; } public async Task<IEnumerable<Tag>> GetTags() { string sql = $""" SELECT name FROM tag ORDER BY name """; using (var connection = _dapperProvider.Connect()) { var tags = await connection.QueryAsync<Tag>(sql); return tags.ToList(); } } } |
Figure 4. The AppRepository class.
The class above uses C#'s new raw literals. This literal type is included in C# 11 but for C# 10 you need to include the line below in the
<PropertyGroup>section of your project's.csprojfile.
|
1 |
LangVersion>preview</LangVersion> |
Step 6. Add the services to the builder instance in the project's Program.cs class.
These lines make the DapperConnectionProvider and the AppRepository classes injectable.
|
1 2 |
builder.Services.AddSingleton<DapperConnectionProvider>(); builder.Services.AddScoped<IAppRepository, AppRepository>(); |
Figure 5. Add the two Dapper-related services.
When used as an injected service, Dapper doesn't have a direct analog to the Entity Framework's DBContext. EF's DBContext is a scoped instance (that is an instance of ER's DBContext exists across the duration of a single request) that encapsulates both the database connection and its operations.
In this example, the DapperConnectionProvider is a singleton instance that provides injectable access to the Microsoft.Data.SqlClient object. During a request, DapperConnectionProvider is injected into the AppRepository, which is a scoped instanced (like the ER's DBContext). Data access methods in the AppRepository use the database object provided by the DapperConnectionProvider to connect to (and dispose) the database connection as needed.
Step 7. Put the code to work
An example controller with Dapper injected is shown below. The controller's constructor pulls in the concrete instance of IAppREpository (as shown in Step 6).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
using Microsoft.AspNetCore.Mvc; using DapperWork; namespace MyApp.Controllers; public class TagController : Controller { private readonly IAppRepository _appRepo; public TagController(IAppRepository appRepo) { this._appRepo = appRepo; } public async Task<IActionResult> Index() { try { var tags = await _appRepo.GetTags(); return View(tags); } catch (Exception ex) { // Log error return StatusCode(500, ex.Message); } } } |
Figure 6. A controller using the injected Dapper service.
Is it worth the effort to create an interface for the repository?
At the risk of invoking purist ire, I don't think it is in many cases. For my money, using a repository interface is an example of premature optimization and YAGNI. The cost of refactoring a concrete class into an interface/implementation scheme after the app is working is much lower than creating the interface and the class as the app is being developed--especially given that Visual Studio can very easily generate an interface from a concrete class for you.
In the real world, how many times have you ever moved a production application to a different database platform?
If you prefer to create the interface as you develop the app, have it at! Otherwise, make these three changes to the code above to do the work above without the repository interface:
Change 1. Remove the IAppRepository implementation from the AppRepository class.
This is easily done by removing : IAppRepository from the AppRepository class declaration.
Change this line:
|
1 |
public class AppRepository : IAppRespository |
To this:
|
1 |
public class AppRepository |
See Step 5's code above for full comparison.
Change 2. Change the program.cs class to inject the AppRepository concrete instance
Change this line:
|
1 |
builder.Services.AddScoped<IAppRepository, AppRepository>(); |
To this:
|
1 |
builder.Services.AddScoped<AppRepository>(); |
See Step 6's code above for full comparison.
Change 3. Change the controller's constructor
Change the controller's constructor to have the AppRepository injected, not the IAppRepository interface.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
... public class TagController : Controller { private readonly AppRepository _appRepo; public TagController(AppRepository appRepo) { this._appRepo = appRepo; } ... See Step 5's code above for full comparison. |
See Step 7's code above for full comparison.
With these changes, you can remove the IAppRepository interface.
