在本文中,我们将学习如何在ASP.NET Core Web API项目中使用Dapper,并且如何在项目中使用 Dapper 进行不同的SQL查询和执行,如何执行存储过程,以及如何在事务中创建多个查询。在本项目中,我们会使用仓储模式 Repository 来构建封装对数据库的操作。
关于 Dapper
Dapper是一个非常好的 ORM(对象关系映射器),或者更准确地说是一个Micro ORM,我们可以在项目中使用它与数据库通信。通过使用Dapper,我们可以像在SQL Server中一样编写SQL语句。Dapper具有出色的性能,因为它不会将我们在.NET中编写的查询转换为SQL。重要的是要知道Dapper是SQL注入安全的,因为我们可以使用参数化查询,这是我们应该一直做的事情。更重要的是,Dapper支持多个数据库提供程序。它扩展了ADO.NET的IDbConnection,并提供了很多的扩展方法来查询我们的数据库。当然,要操作不同的数据库时,我们需要通过NuGet包管理器引入不同的数据库操作库文件。Dapper 不但支持同步同时也运行异步方法的执行。在本文中,我会使用异步方法来进行演示教学。
关于Dapper的扩展方法
Dapper是通过扩展IDbConnection接口来操作数据库,我们来看看它有下面的一些方法:
- Execute 一种扩展方法,用于执行一个命令一次或多次,并返回受影响的行数,通过Query–使用此扩展方法,我们可以执行查询并映射结果
- QueryFirst 它执行查询并映射第一个结果
- QueryFirstOrDefault 我们使用此方法执行查询并映射第一个结果,如果序列不包含元素,则映射默认值
- QuerySingle 执行查询,返回单个结果。如果查询结果为空,则抛出异常
- QuerySingleOrDefault 执行查询并映射结果,如果序列为空,则映射默认值。如果序列中有多个元素,则抛出异常
- QueryMultiple 在同一命令和映射结果中执行多个查询的扩展方法
同样的,这些方法也提供了Async异步的相应方法。
建立数据库并引入DAPPER
在开始项目前,我们需要在MSSQL中建立所需要的例子数据库BookDapper,并建立两张数据表,一张为Book表,一张为Publisher表。
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
现在,我们启动VS2022,如果你使用VS2019或VS2017也都是可以的,建立一个asp.net core web api的项目,项目名称为BookDapper。
项目建立完成后,我们再安装Dapper和SQL客户端,也可以通过NuGet包管理器搜索出来并安装完成。
- Dapper – PM> Install-Package Dapper
- SQL Client – PM> Install-Package Microsoft.Data.SqlClient
建立仓储模式
在项目中新建Repository和Entities这两个文件夹。然后在Entities文件夹建立图书和出版商的类。
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>();
}
通过以上我们建立了图书和出版商的实体类。
然后,我们修改appsettings.json配置文件,将我们的数据库连接放在里面。注意,服务器名称、用户名和密码写自己的。
{
"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": "*"
}
现在我们在建立一个 Context 文件夹和 DapperContext 类,其内容如下
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 );
}
}
我们通过注入IConfiguration接口,以此来读取配置文件 appsettings.json 中的连接字符串。另外,我们还创建了CreateConnection方法,该方法返回一个新的SQLConnection对象。
接下来,我们要在启动文件 Startup.cs 中注册这个服务类,如下图
现在,我们要建立仓储接口和仓储类了,我们在Repository中建立出版商Publisher接口类和出版商类并实现这个接口:
namespace BookDapper.Repository
{
public interface IPublisherRepository
{
}
}
public class PublisherRepository:IPublisherRepository
{
private readonly DapperContext _context;
public PublisherRepository(DapperContext context)
{
_context = context;
}
}
在启动文件Startup.cs类中,我们还要把这个注入加进去:
public void ConfigureServices(IServiceCollection services)
{
services.AddSingleton<DapperContext>();
services.AddScoped<IPublisherRepository, PublisherRepository>();
services.AddControllers();
}
好了,现在,基本框架结构已经完成了。
使用Dapper添加查询等操作
首先,我们在IPublisherRepository接口文件中添加获取数据的方法:
public interface IPublisherRepository
{
public Task<IEnumerable<Publisher>> GetPublishers();
}
然后,我们在PublisherRepository类中去实现它,用Dapper获取数据并返回:
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();
}
}
}
}
建立API Controller控制器
接下来,我们来建立 PublisherController 控制器
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);
}
}
}
}
在这里,我们通过注入仓储的方法来获取数据。好了,我们先在数据库的出版商表中手中录入几条数据,然后在浏览器中就可以观看结果了。
使用参数查询数据
在Dapper的操作中,我们还可以使用参数来查询表中的数据。如:以下
public interface IPublisherRepository
{
public Task<IEnumerable<Publisher>> GetPublishers();
public Task<Publisher> GetPublisher(int id);
}
通过GetPublisher方法以及参数 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();
}
}
}
}
现在,我们再控制器Controller中添加一个异步方法获取单条数据并返回:
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);
}
}
}
}
接下来,我们在浏览器中访问数据,通过输入Id号,得到相关数据:
接下来,增加新增和删除和更新等异步方法
首先在接口文件中增加方法体
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);
}
然后在实现的类中实现异步方法:
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();
}
}
}
}
好了,到现在,对出版商的增、删、查、改方法我们都已经实现了。现在,我们回到控制器中,把相应的方法加上,之后在浏览器中就可以看到结果了。下面的控制器中的方法。
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>
/// 获取所有出版商列表
/// </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>
/// 查询出版商信息
/// </summary>
/// <param name="id">id号</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>
/// 新增出版商
/// </summary>
/// <param name="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>
/// 修改出版商
/// </summary>
/// <param name="id">出版商Id</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>
/// 删除出版商
/// </summary>
/// <param name="id">出版商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);
}
}
}
}
现在,我们就完成了对出版商表的所有操作,是不是很简单,对BOOK图书的操作方法和出版商差不多,这里就不一一演示。
总结:
Dapper相对来说,操作比较简单,容易上手,只需要引用一个库文件,而且对多种数据库支持都比较友好,如果希望更简单,还可以下载Dapper的扩展方法Dapper.SimpleCRUD进行更简洁的操作,到这里,你是否有一种跃跃欲试的感觉,赶紧行动起来吧!