Dapper.Lite 使用教程

以MySQL數(shù)據(jù)庫為例

一. 安裝

NuGet搜索Dapper.Lite并安裝最新版本。

NuGet搜索MySqlConnector并安裝最新版本。

也可以使用MySql.Data庫，但MySqlConnector庫性能更好。

二. 實現(xiàn)數(shù)據(jù)庫Provider

using Dapper.Lite;
using MySql.Data.MySqlClient;
using System.Data.Common;

namespace DAL
{
    public class MySQLProvider : MySQLProviderBase, IDbProvider
    {
        #region 創(chuàng)建 DbConnection
        public override DbConnection CreateConnection(string connectionString)
        {
            return new MySqlConnection(connectionString);
        }
        #endregion

        #region 生成 DbParameter
        public override DbParameter GetDbParameter(string name, object value)
        {
            return new MySqlParameter(name, value);
        }
        #endregion

    }
}

三. 創(chuàng)建實例

IDapperLite db = new DapperLite(
    "Data Source=localhost;Port=3306;User ID=root;Password=123456;Initial Catalog=litesql_test;Charset=utf8mb4;SslMode=none;Allow User Variables=True;",  
    new MySQLProvider());

四. 查詢示例

SQL查詢

IDbSession session = db.GetSession();
List<SysUser> list = session.Sql("select * from sys_user where id <= @Id and remark like @Remark", 20, "%測試%").ToList<SysUser>();
//或
list = session.Sql("select * from sys_user where id <= @Id and remark like @Remark", new { Id = 20, Remark = "%測試%" }).ToList<SysUser>();

Lambda查詢

IDbSession session = db.GetSession();
List<SysUser> list = session.Queryable<SysUser>().Where(t => t.Id <= 20 && t.Remark.Contains("測試")).ToList();

SQL拼接查詢條件支持Lambda表達式

IDbSession session = db.GetSession();
List<BsOrder> list = session
    .Sql<BsOrder>(@"
        select t.*, u.user_name as OrderUserName 
        from bs_order t
        left join sys_user u on u.id = t.order_userid")
    .Where(t => t.Amount > 100 && t.OrderTime > new DateTime(2022, 1, 1))
    .Where<SysUser>(u => u.RealName == "張三")
    .ToList();