用轻量级ORM--Dapper调用MySQL存储过程

阅读本文你的收获

1. 写一个MySQL的分页存储过程
2. 学会使用Dapper去调用存储过程

---

在《用轻量级ORM–Dapper实现泛型仓储》中分享了轻量级ORM框架的基本使用，并实现了一个泛型仓储。本文继续分享用Dapper执行存储过程，区别于上文用的SQL Server数据库，本次我们用MySQL。闲话少絮，直接上案例。

一、场景描述

分页是系统中常用的功能，首先我们在MySQL中创建一个通用的分页存储过程，然后用Dapper去调用分页存储过程，实现用户列表的分页查询。

开发环境：

平台版本是：.NET6
开发框架：ASP.NET Core WebApi
开发工具：Visual Studio 2022
数据库版本：MySQL 5.7

二、在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; 
    }
}

剖析以上案例，要点就是：

1. 创建MySqlConnection连接对象dbConnection，注意要用using结构来确保资源释放；
2. 用dbConnection对象调用Dapper扩展的方法Query来执行存储过程。其中"p_pageList"是存储过程的名称，commandType请设置为CommandType.StoredProcedure枚举值。给Query方法传参使用DynamicParameters动态参数对象

---

怎么样？用Dapper执行存储过程，是不是挺简单的？
那今天就分享到这里，如果本文对你有帮助的话，请点赞+评论+关注，或者转发给需要的朋友。