阅读本文你的收获
- 写一个MySQL的分页存储过程
- 学会使用Dapper去调用存储过程
在《用轻量级ORM–Dapper实现泛型仓储》中分享了轻量级ORM框架的基本使用,并实现了一个泛型仓储。本文继续分享用Dapper执行存储过程,区别于上文用的SQL Server数据库,本次我们用MySQL。闲话少絮,直接上案例。
一、场景描述
分页是系统中常用的功能,首先我们在MySQL中创建一个通用的分页存储过程,然后用Dapper去调用分页存储过程,实现用户列表的分页查询。
开发环境:
二、在MySQL中创建存储过程
1.创建存储过程
写一个通用分页存储过程,可以进行任何单表的分页查询。
-- 0. 如果存在该存储过程,则删除
DROP PROCEDURE IF EXISTS p_pageList;
-- 1. 创建存储过程
CREATE PROCEDURE p_pageList
(
IN _pageIndex INT, -- 页码 IN是输入参数
IN _pageSize INT, -- 页的容量
IN _tableName varchar(30), -- 表的名字
IN _condition varchar(500), -- 查询条件
OUT _totalCount INT -- 总条数 OUT是输出参数
)
BEGIN
-- 用Declare关键词 定义一个局部变量
DECLARE skip int;
-- 给skip赋值, 跳过的条数
set skip = (_pageIndex-1)*_pageSize;
-- mysql的分页怎么写,用什么关键字??
-- SELECT SQL_CALC_FOUND_ROWS * FROM users where xxxx='' LIMIT skip ,_pageSize;
-- 用Concat函数,拼接SQL语句,存入全局变量@sqlStr
set @sqlStr = CONCAT("SELECT SQL_CALC_FOUND_ROWS "
," * From "
, _tableName
, " where 1=1 "
, _condition
, " LIMIT "
, skip
,","
, _pageSize);
-- 打印拼接的SQL语句
-- select @sqlStr;
-- 执行SQL字符串语句
PREPARE stmt FROM @sqlStr; -- 对sql语句进行预处理
EXECUTE stmt; -- 执行sql语句
DEALLOCATE PREPARE stmt; -- 释放预处理的资源
-- 在mysql中如何获取总条数,比SQL Server要简单
SET _totalCount = FOUND_ROWS(); -- FOUND_ROWS()获取总条数
END;
2.测试存储过程
-- 调用存储过程
CALL p_pageList(1,3,"users", "and userName like '%张三%'", @total); -- @开头变量,类似于全局变量
select @total;
三、使用Dapper调用存储过程
1.定义分页数据响应模型
/// <summary>
/// 分页返回模型
/// </summary>
/// <typeparam name="T"></typeparam>
public class PagedList<T>
{
public List<T> PagedData { get; set; } //分页结果集
public int TotalCount { get; set; } //总条数
}
2.定义用户模块的数据库访问接口
//IUserDal.cs
/// <summary>
/// 用户模块的数据库访问接口
/// </summary>
public interface IUserDal
{
PagedList<User> GetPage(int pageIndex, int pageSize, string name);
}
3.实现IUserDal接口
//UserDal.cs代码如下
using Dapper; //引用Dapper命名空间
using System.Data;
/// <summary>
/// 用户的仓储接口实现类
/// </summary>
public class UserDal: IUserDal
{
//获取连接字符串(本例中是硬编码的,最好写在appsettings.json中)
private const string CONNECT_STRING = "Server=localhost;Database=testdb1;User=root;Password=123abc!;Port=3306";
/// <summary>
/// 用户分页查询
/// </summary>
/// <param name="pageIndex">页索引</param>
/// <param name="pageSize">页容量</param>
/// <param name="name">姓名查询条件</param>
/// <returns></returns>
public PagedList<User> GetUserPage(int pageIndex, int pageSize, string name)
{
//定义并添加参数
string strWhere = string.Empty;
if (!string.IsNullOrEmpty(name))
{
strWhere += $" and Username like '{name}%'";
}
//1. Dapper动态参数赋值:定义DynamicParameters对象
DynamicParameters paras = new DynamicParameters();
paras.Add("_tableName", "Users");
paras.Add("_condition", strWhere);
paras.Add("_pageIndex", pageIndex);
paras.Add("_pageSize", pageSize);
paras.Add("_totalCount", dbType: DbType.Int32,
direction: ParameterDirection.Output);
//2. 实例化MySql数据库连接对象
using var dbConnection = new MySqlConnection(CONNECT_STRING );
PagedList<User> result = new PagedList<User>();
//3. 用Dapper扩展方法来执行存储过程
result.PagedData = dbConnection.Query<User>("p_pageList",
commandType: CommandType.StoredProcedure, //存储过程
param: paras
).ToList();
//获取总条数
result.TotalCount = paras.Get<int>("_totalCount");
return result;
}
}
剖析以上案例,要点就是:
- 创建
MySqlConnection
连接对象dbConnection,注意要用using结构来确保资源释放; - 用dbConnection对象调用Dapper扩展的方法
Query
来执行存储过程。其中"p_pageList"是存储过程的名称,commandType
请设置为CommandType.StoredProcedure
枚举值。给Query方法传参使用DynamicParameters
动态参数对象
怎么样?用Dapper执行存储过程,是不是挺简单的?
那今天就分享到这里,如果本文对你有帮助的话,请点赞+评论+关注,或者转发给需要的朋友。