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 DapperConnectionProvider
instance. 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.csproj
file.
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.