In this article, we will learn how to use Dapper in an ASP.NET Core Web API project and how to use Dapper in the project for different SQL queries and executions, how to execute stored procedures, and how to create multiple queries within a transaction. In this project, we will use the repository mode Repository to build and encapsulate operations on the database.
About Dapper
Dapper is a very good ORM (Object Relational Mapper), or more precisely a Micro ORM, which we can use in our projects to communicate with the database. By using Dapper, we can write SQL statements just like in SQL Server. Dapper has great performance because it does not convert the queries we write in .NET into SQL. It’s important to know that Dapper is SQL injection safe because we can use parameterized queries, which is something we should always do. What’s more, Dapper supports multiple database providers. It extends ADO.NET’s IDbConnection and provides many extension methods to query our database. Of course, when we want to operate different databases, we need to introduce different database operation library files through the NuGet package manager. Dapper not only supports synchronous but also asynchronous method execution. In this article, I will use asynchronous methods to demonstrate teaching.
About Dapper’s extension methods
Dapper operates the database by extending the IDbConnection interface. Let’s take a look at some of the following methods:
- Execute An extension method that executes a command one or more times and returns the number of affected rows, via Query – using this extension method, we can execute the query and map the results
- QueryFirst It executes the query and maps the first result
- QueryFirstOrDefault We use this method to execute the query and map the first result and if the sequence does not contain an element, map the default value
- QuerySingle executes a query and returns a single result. If the query result is empty, an exception is thrown
- QuerySingleOrDefault Execute a query and map the results, or a default value if the sequence is empty. Throws an exception if there are more than one element in the sequence
- QueryMultiple Extension method for executing multiple queries in the same command and mapping results
Likewise, these methods also provide Async asynchronous corresponding methods.
Create a database and introduce DAPPER
Before starting the project, we need to create the required example database BookDapper in MSSQL, and create two data tables, one for the Book table and one for the Publisher table.
CREATE TABLE [dbo].[Book]( [Id] [int] IDENTITY(1,1) NOT NULL, [BookName] [nvarchar](50) NULL, [BookAuthor] [nvarchar](50) NULL, [BookPrice] [decimal](8, 2) NULL, [BookPublishDate] [date] NULL, [BookType] [nvarchar](50) NULL, [BookPublisherId] [int] NULL, CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED ( [Id]ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Publisher]( [Id] [int] IDENTITY(1,1) NOT NULL, [PublisherName] [nvarchar](50) NULL, [PublisherAddr] [nvarchar](150) NULL, [PublisherPhone] [nvarchar](50) NULL, CONSTRAINT [PK_Publisher] PRIMARY KEY CLUSTERED ( [Id]ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO
Now, we start VS2022. If you use VS2019 or VS2017, you can also create an asp.net core web api project with the project name BookDapper.
After the project is established, we can install Dapper and SQL client. We can also search for them through the NuGet package manager and install them.
- Dapper – PM> Install-Package Dapper
- SQL Client – PM> Install-Package Microsoft.Data.SqlClient
Establish a warehousing model
Create two new folders, Repository and Entities, in the project. Then create the book and publisher classes in the Entities folder.
public class Book
{
public int Id { get; set; }
public string BookName { get; set; }
public string BookAuthor { get; set; }
public decimal BookPrice { get; set; } = 0;
public string BookType { get; set; }
public int BookPublisherId { get; set; }
}
public class Publisher
{
public int Id { get; set; }
public string PublisherName { get; set; }
public string PublisherAddr { get; set; }
public string PublisherPhone { get; set; }
public List<Book> Books { get; set; }=new List<Book>();
}
Through the above we established the entity classes of books and publishers.
Then, we modify the appsettings.json configuration file and put our database connection in it. Note that the server name, username and password must be your own.
{
"ConnectionStrings": {
"SqlConnection": "Server=Localhost; Database=BookDapper; User Id=sa; Password=******; Pooling=true;Trusted_Connection=True;Encrypt=false;"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*"
}
Now we are creating a Context folder and DapperContext class, whose contents are as follows
using System.Data;
using Microsoft.Extensions.Configuration;
using Dapper;
using Microsoft.Data.SqlClient;
namespace BookDapper.Context
{
public class DapperContext
{
private readonly IConfiguration _configuration;
private readonly string _connectionString;
public DapperContext(IConfiguration configuration)
{
_configuration = configuration;
_connectionString = _configuration.GetConnectionString("SqlConnection");
}
public IDbConnection CreateConnection()=>new SqlConnection(_connectionString );
}
}
We inject the IConfiguration interface to read the connection string in the configuration file appsettings.json. In addition, we also created the CreateConnection method, which returns a new SQLConnection object.
Next, we need to register this service class in the startup file Startup.cs, as shown below
Now, we have to establish the repository interface and repository class. We create the publisher Publisher interface class and publisher class in the Repository and implement this interface:
namespace BookDapper.Repository
{
public interface IPublisherRepository
{
}
}
public class PublisherRepository:IPublisherRepository
{
private readonly DapperContext _context;
public PublisherRepository(DapperContext context)
{
_context = context;
}
}
In the startup file Startup.cs class, we also need to add this injection:
public void ConfigureServices(IServiceCollection services)
{
services.AddSingleton<DapperContext>();
services.AddScoped<IPublisherRepository, PublisherRepository>();
services.AddControllers();
}
Okay, now, the basic framework structure has been completed.
Use Dapper to add queries and other operations
First, we add a method to obtain data in the IPublisherRepository interface file:
public interface IPublisherRepository
{
public Task<IEnumerable<Publisher>> GetPublishers();
}
Then, we implement it in the PublisherRepository class, use Dapper to get the data and return:
using BookDapper.Context;
using BookDapper.Entities;
using Dapper;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace BookDapper.Repository
{
public class PublisherRepository:IPublisherRepository
{
private readonly DapperContext _context;
public PublisherRepository(DapperContext context)
{
_context = context;
}
public async Task<IEnumerable<Publisher>> GetPublishers()
{
var query = "SELECT * FROM Publisher";
using (var connection = _context.CreateConnection())
{
var publishers = await connection.QueryAsync<Publisher>(query);
return publishers.ToList();
}
}
}
}
Create API Controller
Next, let’s create the PublisherController controller
using System;
using BookDapper.Repository;
namespace BookDapper.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class PublisherController : ControllerBase
{
private readonly IPublisherRepository _publisherRepository;
public PublisherController(IPublisherRepository Repo)
{
_publisherRepository = Repo;
}
[HttpGet]
public async Task<IActionResult> GetPublishers()
{
try
{
var companies = await _publisherRepository.GetPublishers();
return Ok(companies);
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
}
}
Here, we obtain data by injecting into the warehouse. Okay, let’s first enter a few pieces of data into the publisher table of the database, and then we can view the results in the browser.
Query data using parameters
In Dapper’s operation, we can also use parameters to query data in the table. Such as: the following
public interface IPublisherRepository
{
public Task<IEnumerable<Publisher>> GetPublishers();
public Task<Publisher> GetPublisher(int id);
}
Get a single publisher through the GetPublisher method and the parameter id.
using BookDapper.Context;
using BookDapper.Entities;
using Dapper;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace BookDapper.Repository
{
public class PublisherRepository:IPublisherRepository
{
private readonly DapperContext _context;
public PublisherRepository(DapperContext context)
{
_context = context;
}
public async Task<Publisher> GetPublisher(int id)
{
var query = "SELECT * FROM Publisher WHERE Id = @Id";
using (var connection = _context.CreateConnection())
{
var publisher = await connection.QuerySingleOrDefaultAsync<Publisher>(query, new { id });
return publisher;
}
}
public async Task<IEnumerable<Publisher>> GetPublishers()
{
var query = "SELECT * FROM Publisher";
using (var connection = _context.CreateConnection())
{
var publishers = await connection.QueryAsync<Publisher>(query);
return publishers.ToList();
}
}
}
}
Now, we add an asynchronous method in the controller to obtain a single piece of data and return:
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;
using System;
using BookDapper.Repository;
namespace BookDapper.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class PublisherController : ControllerBase
{
private readonly IPublisherRepository _publisherRepository;
public PublisherController(IPublisherRepository Repo)
{
_publisherRepository = Repo;
}
[HttpGet]
public async Task<IActionResult> GetPublishers()
{
try
{
var companies = await _publisherRepository.GetPublishers();
return Ok(companies);
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
[HttpGet( "{id}",Name = "PublisherById")]
public async Task<IActionResult> GetPublisher(int id)
{
try
{
var publisher = await _publisherRepository.GetPublisher(id);
if (publisher == null) return NotFound();
return Ok(publisher);
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
}
}
Next, we access the data in the browser and get the relevant data by entering the ID number:
Next, add asynchronous methods such as adding, deleting and updating
First add the method body in the interface file
public interface IPublisherRepository
{
public Task<IEnumerable<Publisher>> GetPublishers();
public Task<Publisher> GetPublisher(int id);
public Task<Publisher> CreatePublisher(Publisher company);
public Task UpdatePublisher(int id, Publisher company);
public Task DeletePublisher(int id);
}
Then implement the async method in the implemented class:
using BookDapper.Context;
using BookDapper.Entities;
using Dapper;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
namespace BookDapper.Repository
{
public class PublisherRepository:IPublisherRepository
{
private readonly DapperContext _context;
public PublisherRepository(DapperContext context)
{
_context = context;
}
public async Task<Publisher> CreatePublisher(Publisher publisher)
{
var query = "INSERT INTO Publisher (PublisherName, PublisherAddr, PublisherPhone) VALUES (@PublisherName, @PublisherAddr, @PublisherPhone)" + "SELECT CAST(SCOPE_IDENTITY() as int)";
var parameters = new DynamicParameters();
parameters.Add("PublisherName", publisher.PublisherName, DbType.String); parameters.Add("PublisherName", publisher.PublisherName, DbType.String); parameters.Add("PublisherPhone", publisher. PublisherPhone, DbType.String);
using (var connection = _context.CreateConnection())
{
var id = await connection.QuerySingleAsync<int>(query, parameters);
var createdPublisher = new Publisher
{
Id = id,
PublisherName = publisher.PublisherName,
PublisherAddr = publisher.PublisherAddr,
PublisherPhone = publisher.PublisherPhone
};
return createdPublisher;
}
}
public async Task DeletePublisher(int id)
{
var query = "DELETE FROM Publisher WHERE Id = @Id";
using (var connection = _context.CreateConnection())
{
await connection.ExecuteAsync(query, new { id });
}
}
public async Task UpdatePublisher(int id, Publisher publisher)
{
var query = "UPDATE Publisher SET PublisherName = @PublisherName, PublisherAddr = @PublisherAddr, PublisherPhone = PublisherPhone WHERE Id = @Id";
var parameters = new DynamicParameters();
parameters.Add("Id", id, DbType.Int32);
parameters.Add("PublisherName", publisher.PublisherName, DbType.String); parameters.Add("PublisherAddr", publisher.PublisherAddr, DbType.String); parameters.Add("PublisherPhone", publisher. PublisherPhone, DbType.String);
using (var connection = _context.CreateConnection())
{
await connection.ExecuteAsync(query, parameters);
}
}
public async Task<Publisher> GetPublisher(int id)
{
var query = "SELECT * FROM Publisher WHERE Id = @Id";
using (var connection = _context.CreateConnection())
{
var publisher = await connection.QuerySingleOrDefaultAsync<Publisher>(query, new { id });
return publisher;
}
}
public async Task<IEnumerable<Publisher>> GetPublishers()
{
var query = "SELECT * FROM Publisher";
using (var connection = _context.CreateConnection())
{
var publishers = await connection.QueryAsync<Publisher>(query);
return publishers.ToList();
}
}
}
}
Okay, so far, we have implemented the methods of adding, deleting, checking, and modifying publishers. Now, we go back to the controller, add the corresponding method, and then we can see the results in the browser. method in the controller below.
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;
using System;
using BookDapper.Entities;
using BookDapper.Repository;
namespace BookDapper.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class PublisherController : ControllerBase
{
private readonly IPublisherRepository _publisherRepository;
public PublisherController(IPublisherRepository Repo)
{
_publisherRepository = Repo;
}
/// <summary>
/// Get a list of all publishers
/// </summary>
/// <returns></returns>
[HttpGet]
public async Task<IActionResult> GetPublishers()
{
try
{
var companies = await _publisherRepository.GetPublishers();
return Ok(companies);
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
/// <summary>
/// Query publisher information
/// </summary>
/// <param name="id">id number</param>
/// <returns></returns>
[HttpGet( "{id}",Name = "PublisherById")]
public async Task<IActionResult> GetPublisher(int id)
{
try
{
var publisher = await _publisherRepository.GetPublisher(id);
if (publisher == null) return NotFound();
return Ok(publisher);
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
/// <summary>
/// Add new publisher
/// </summary>
/// <param name="publisher">Publisher</param>
/// <returns></returns>
[HttpPost]
public async Task<IActionResult> CreatePublisher(Publisher publisher)
{
try
{
var createdPublisher = await _publisherRepository.CreatePublisher(publisher);
return CreatedAtRoute("PublisherById", new { id = createdPublisher.Id }, createdPublisher);
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
/// <summary>
/// Modify publisher
/// </summary>
/// <param name="id">PublisherId</param>
/// <param name="publisher"></param>
/// <returns></returns>
[HttpPut( "{id}" )]
public async Task<IActionResult> UpdatePublisher(int id, Publisher publisher)
{
try
{
var dbPublisher = await _publisherRepository.GetPublisher(id);
if (dbPublisher == null)
return NotFound();
await _publisherRepository.UpdatePublisher(id, publisher);
return NoContent();
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
/// <summary>
/// Delete publisher
/// </summary>
/// <param name="id">Publisher id</param>
/// <returns></returns>
[HttpDelete("{id}")]
public async Task<IActionResult> DeletePublisher(int id)
{
try
{
var dbPublisher = await _publisherRepository.GetPublisher(id);
if (dbPublisher == null)
return NotFound();
await _publisherRepository.DeletePublisher(id); return NoContent();
}
catch (Exception ex)
{
return StatusCode(500, ex.Message);
}
}
}
}
Now, we have completed all the operations on the publisher table. Isn’t it very simple? The operation method of BOOK books is similar to that of publishers, so I will not demonstrate them one by one here.
Summary:
Dapper is relatively simple to operate and easy to use. You only need to reference a library file, and it is friendly to multiple database supports. If you want it to be simpler, you can also download Dapper’s extension method Dapper.SimpleCRUD for more concise operations. At this point, if you feel eager to give it a try, act quickly!