.net5 - 创建Web.Api项目（六）Repository 仓储 - Dapper 目前仅支持mysql

Dapper框架 个人的一点体会

• 尽可能的保证数据库【字段名】和类【属性名】的一致性，不然需要多写很多的代码，说多了都是泪泪泪
• 如果因为遵循了mysql数据库的建表规则【阿里的规则】，表名字段名带下划线的，使用As方式，其他方式自行探索

Dapper框架 分页模型

using System;
using System.Collections.Generic;

namespace NetFive.Repository
{
    /// <summary>
    /// 分页模型
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class PageDataView<T>
    {
        /// <summary>
        /// 当前第几页
        /// </summary>
        public int PageIndex { get; set; }

        /// <summary>
        /// 每页多少条数据
        /// </summary>
        public int PageSize { get; set; }

        /// <summary>
        /// 总共多少条数据
        /// </summary>
        public int TotalCount { get; set; }

        /// <summary>
        /// 总页数
        /// </summary>
        public int TotalPages => Convert.ToInt32(Math.Ceiling((double)TotalCount / PageSize));

        /// <summary>
        /// 数据
        /// </summary>
        public IList<T> Items { get; set; }
    }
}

 分页放在Service层 ResponseDto：

using NetFive.Repository;

namespace NetFive.Service.Common
{
    public class PageResponseDto<T> : ResponseDto where T : class
    {
        /// <summary>
        /// 分页数据
        /// </summary>
        public PageDataView<T> Data { get; set; }
    }
}

　　

Dapper框架 数据库上下文【IDbContext】 MySql

NuGet包：

Microsoft.Extensions.Configuration
MySql.Data

 目录结构：

using System;
using System.Data;

namespace NetFive.Repository.interfaces
{
    public interface IDbContext : IDisposable
    {
        /// <summary>
        /// 数据库链接对象
        /// </summary>
        IDbConnection Connection { get; }
    }
}

　

using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using NetFive.Repository.interfaces;
using System.Data;

namespace NetFive.Repository
{
    public class DbContext : IDbContext
    {
        /// <summary>
        /// 链接字符串
        /// </summary>
        private string _connectionString;

        public DbContext(IConfiguration configuration)
        {
            _connectionString = configuration["AppSetting:ConnectionString"];
        }

        /// <summary>
        /// 链接对象
        /// </summary>
        private IDbConnection _connection;

        /// <summary>
        /// 获取链接
        /// </summary>
        public IDbConnection Connection
        {
            get 
            {
                if (_connection == null)
                {
                    _connection = new MySqlConnection(_connectionString);
                }
                if (_connection.State != ConnectionState.Open)
                {
                    _connection.Open();
                }
                return _connection;
            }
        }

        /// <summary>
        /// 释放链接
        /// </summary>
        public void Dispose()
        {
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                _connection.Close();
            }
        }
    }
}

　　

 Dapper框架 仓库【Repository】与UnitOfWorks合并：放弃Dapper原生的增删改方法，采用自己生成sql语句或者直接写语句的方式

 安装NuGet包：

Dapper
Dapper.Contrib

 自定义类： 放在Common层

 字段映射

using System;

namespace CustomAttributes.Repository.Custom
{
    /// <summary>
    /// 字段映射
    /// @Name 当数据库名与字段名不一致时
    /// </summary>
    public class ColumnAttribute : Attribute
    {
        /// <summary>
        /// 字段名不同时的映射
        /// </summary>
        public string Name { get; set; }

        public ColumnAttribute(string name)
        {
            Name = name;
        }
    }
}

 仅查询字段

using System;

namespace CustomAttributes.Repository.Custom
{
    /// <summary>
    /// 仅用作查询字段，不做增删改操作
    /// </summary>
    public class OnlyQueryAttribute : Attribute
    { }
}

 主键，主键名，是否自增长　

using System;

namespace CustomAttributes.Repository.Custom
{
    /// <summary>
    /// 主键特性 
    /// @Name 主键字段名
    /// @IsAutoIncrement 是否自增长主键
    /// </summary>
    public class PrimaryKeyAttribute : Attribute
    {
        /// <summary>
        /// 是否自增主键
        /// </summary>
        public bool IsAutoIncrement { get; }

        /// <summary>
        /// 主键字段
        /// </summary>
        public string Name { get; }

        public PrimaryKeyAttribute(string name, bool isAutoIncrement)
        {
            Name = name;
            IsAutoIncrement = isAutoIncrement;
        }
    }
}

　　

接口：

using CustomAttributes.Repository.Custom;
using System.Collections.Generic;
using System.Data;
using static Dapper.SqlMapper;

namespace CustomAttributes.Repository.interfaces
{
    /// <summary>
    /// 数据库CRUD操作
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public interface IRepository<T> where T : class
    {
        /// <summary>
        /// 上下文
        /// </summary>
        IDbContext Context { get; }
        /// <summary>
        /// 链接
        /// </summary>
        IDbConnection Connection { get; }

        #region Add
        /// <summary>
        /// 添加：根据实体生成sql语句
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        int Add(T entity, IDbTransaction transaction = null);
        /// <summary>
        /// 批量添加：根据实体生成sql语句
        /// </summary>
        /// <param name="list"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        int BatchAdd(IList<T> list, IDbTransaction transaction = null);
        /// <summary>
        /// 添加：自己手写sql语句
        /// </summary>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        int Add(string p_sql, object param = null, IDbTransaction transaction = null);
        /// <summary>
        /// 批量添加：自己手写sql语句
        /// </summary>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        int BatchAdd(string p_sql, object param = null, IDbTransaction transaction = null);
        #endregion

        #region Update
        /// <summary>
        /// 修改：根据实体生成sql语句
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        bool Update(T entity, IDbTransaction transaction = null);
        /// <summary>
        /// 修改：自己手写sql语句
        /// </summary>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        bool Update(string p_sql, object param = null, IDbTransaction transaction = null);
        #endregion

        #region Delete
        /// <summary>
        /// 删除：根据主键删除
        /// </summary>
        /// <param name="key"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        bool DeleteByKey(object key, IDbTransaction transaction = null);
        /// <summary>
        /// 根据By条件
        /// </summary>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        bool Delete(string p_sql, object param = null, IDbTransaction transaction = null);
        #endregion

        #region Get
        /// <summary>
        /// 全部字段：主键
        /// </summary>
        /// <param name="key"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        T GetByKey(object key, IDbTransaction transaction = null);
        /// <summary>
        /// 固定字段sql
        /// </summary>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        T Get(string p_sql, object param = null, IDbTransaction transaction = null);
        /// <summary>
        /// 全部字段where
        /// </summary>
        /// <param name="where"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        T GetBy(string where, object param = null, IDbTransaction transaction = null);
        /// <summary>
        /// 固定字段sql
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        U Get<U>(string p_sql, object param = null, IDbTransaction transaction = null);
        #endregion

        #region List
        /// <summary>
        /// 获取所有数据
        /// </summary>
        /// <param name="transaction"></param>
        /// <returns></returns>
        IList<T> ListAll(IDbTransaction transaction = null);
        /// <summary>
        /// 固定字段：单表数据sql
        /// </summary>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        IList<T> List(string p_sql, object param = null, IDbTransaction transaction = null);
        /// <summary>
        /// 全部字段：单表数据where
        /// </summary>
        /// <param name="where"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        IList<T> ListBy(string where, object param = null, IDbTransaction transaction = null);
        /// <summary>
        /// 固定字段：多表数据sql
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        IList<U> List<U>(string p_sql, object param = null, IDbTransaction transaction = null);
        #endregion

        #region IsExists
        /// <summary>
        /// 是否存在数据
        /// </summary>
        /// <param name="where"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        bool IsExists(string where, object param = null, IDbTransaction transaction = null);
        /// <summary>
        /// 是否存在数据【其他实体类方法调用的时候】
        /// </summary>
        /// <param name="where"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        bool IsExists<U>(string where, object param = null, IDbTransaction transaction = null);
        #endregion

        #region ListPaging
        /// <summary>
        /// 单表或多表分页，固定字段
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="p_sqls"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        PageDataView<U> GetPageList<U>(int pageIndex, int pageSize, IList<string> p_sqls, object param = null, IDbTransaction transaction = null);
        /// <summary>
        /// 单表分页，返回单表全部字段
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="where"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        PageDataView<U> GetPageListBy<U>(int pageIndex, int pageSize, string where, object param = null, IDbTransaction transaction = null);
        #endregion

        #region Excute / ExecuteScalar
        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="p_sql"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        int Excute(string p_sql, object param = null, IDbTransaction transaction = null);
        /// <summary>
        /// 获取单个参数
        /// </summary>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        object ExecuteScalar(string p_sql, object param = null, IDbTransaction transaction = null);
        /// <summary>
        /// 获取单个参数
        /// </summary>
        /// <typeparam name="U"></typeparam>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        U ExecuteScalar<U>(string p_sql, object param = null, IDbTransaction transaction = null);
        #endregion

        #region GridReader
        /// <summary>
        /// 多对象查询 var invoice = multi.Read<Invoice>().First(); var invoiceItems = multi.Read<InvoiceItem>().ToList();
        /// </summary>
        /// <param name="p_sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        GridReader QueryMultiple(string p_sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
        #endregion
    }
}

 实现：

using CustomAttributes.Repository.Custom;
using CustomAttributes.Repository.interfaces;
using Dapper;
using Dapper.Contrib.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;

namespace CustomAttributes.Repository
{
    public class Repository<T> : IRepository<T> where T : class
    {
        public IDbContext Context { get; }

        public Repository(IDbContext context)
        {
            Context = context;
        }

        public IDbConnection Connection => Context.Connection;

        #region 表名、主键字段名
        /// <summary>
        /// 表名
        /// </summary>
        public string TableName
        {
            get
            {
                Type type = typeof(T);
                var tableName = type.Name;
                var at = type.GetCustomAttribute<TableAttribute>();
                if (!string.IsNullOrWhiteSpace(at.Name))
                {
                    tableName = at.Name;
                }
                return tableName;
            }
        }
        /// <summary>
        /// 主键字段
        /// </summary>
        public string PrimaryKeyName
        {
            get
            {
                Type type = typeof(T);
                string keyName = string.Empty;
                var primaryKey = type.GetCustomAttribute<PrimaryKeyAttribute>();
                if (primaryKey != null)
                {
                    keyName = primaryKey.Name;
                }
                if (string.IsNullOrEmpty(keyName))
                {
                    throw new Exception($"请设置实体类{type.Name}的PrimaryKey特性");
                }
                return keyName;
            }
        }
        /// <summary>
        /// 是否自增主键
        /// </summary>
        public bool IsAutoIncrement
        {
            get
            {
                Type type = typeof(T);
                var primaryKey = type.GetCustomAttribute<PrimaryKeyAttribute>();
                if (primaryKey == null)
                {
                    throw new Exception($"请设置实体类{type.Name}的PrimaryKey特性");
                }
                else
                { 
                    return primaryKey.IsAutoIncrement;
                }
            }
        }
        #endregion

        #region 根据反射生成sql语句
        /// <summary>
        /// 生成添加语句
        /// </summary>
        /// <returns></returns>
        private string InsertSql()
        {
            Type t = typeof(T);
            StringBuilder p_sql = new StringBuilder();
            var ps = t.GetProperties();
            IList<string> field = new List<string>();
            IList<string> param = new List<string>();
            foreach (var p in ps)
            {
                //仅查询字段不在其内
                var only = p.GetCustomAttribute<OnlyQueryAttribute>();
                if (only != null)
                {
                    continue;
                }
                string name = p.Name;
                var column = p.GetCustomAttribute<ColumnAttribute>();
                if (column != null)
                {
                    name = column.Name;
                }
                if (name.ToUpper() == PrimaryKeyName.ToUpper() && IsAutoIncrement)
                {
                    continue;
                }
                param.Add("@" + p.Name);
                field.Add(name);
            }
            p_sql.Append($"insert into {TableName}({string.Join(",", field)}) values({string.Join(",", param)});");
            return p_sql.ToString();
        }
        /// <summary>
        /// 生成修改语句
        /// </summary>
        /// <returns></returns>
        private string UpdateSql()
        {
            Type t = typeof(T);
            StringBuilder p_sql = new StringBuilder();
            var ps = t.GetProperties();
            string keyName = string.Empty;
            IList<string> field = new List<string>();
            foreach (var p in ps)
            {
                //仅查询字段不在修改
                var only = p.GetCustomAttribute<OnlyQueryAttribute>();
                if (only != null)
                {
                    continue;
                }
                string name = p.Name;
                var column = p.GetCustomAttribute<ColumnAttribute>();
                if (column != null)
                {
                    name = column.Name;
                }
                if (name.ToUpper() == PrimaryKeyName.ToUpper())
                {
                    keyName = name;
                    continue;
                }
                field.Add($"{name}=@{p.Name}");
            }
            p_sql.Append($"update {TableName} set {string.Join(", ", field)} where {PrimaryKeyName} = @{keyName};");
            return p_sql.ToString();
        }
        #endregion

        #region Add
        public int Add(T entity, IDbTransaction transaction = null)
        {
            string p_sql = InsertSql();
            int result;
            if (IsAutoIncrement)
            {
                p_sql += "SELECT LAST_INSERT_ID();";
                result = Connection.ExecuteScalar<int>(p_sql, entity, transaction);
            }
            else
            {
                result = Connection.Execute(p_sql, entity, transaction);
            }
            return result;
        }

        public int Add(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            int result;
            if (IsAutoIncrement)
            {
                p_sql += "SELECT LAST_INSERT_ID();";
                result = Connection.ExecuteScalar<int>(p_sql, param, transaction);
            }
            else
            {
                result = Connection.Execute(p_sql, param, transaction);
            }
            return result;
        }

        public int BatchAdd(IList<T> list, IDbTransaction transaction = null)
        {
            string p_sql = InsertSql();
            int result = Connection.Execute(p_sql, list, transaction);
            return result;
        }

        public int BatchAdd(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            int result = Connection.Execute(p_sql, param, transaction);
            return result;
        }
        #endregion

        #region Update
        public bool Update(T entity, IDbTransaction transaction = null)
        {
            string p_sql = UpdateSql();
            int result = Connection.Execute(p_sql, entity, transaction);
            return result > 0;
        }

        public bool Update(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            int result = Connection.Execute(p_sql, param, transaction);
            return result > 0;
        }
        #endregion

        #region Delete
        public bool DeleteByKey(object key, IDbTransaction transaction = null)
        {
            string p_sql = $"delete from {TableName} where {PrimaryKeyName} = @key;";
            int result = Connection.Execute(p_sql, new { key }, transaction);
            return result > 0;
        }

        public bool Delete(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            int result = Connection.Execute(p_sql, param, transaction);
            return result > 0;
        }
        #endregion

        #region Excute / ExecuteScalar
        public int Excute(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            return Connection.Execute(p_sql, param, transaction);
        }

        public object ExecuteScalar(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            return Connection.ExecuteScalar(p_sql, param, transaction);
        }

        public U ExecuteScalar<U>(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            return Connection.ExecuteScalar<U>(p_sql, param, transaction);
        }
        #endregion

        #region Get
        public T Get(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            return Connection.QuerySingleOrDefault<T>(p_sql, param, transaction);
        }

        public U Get<U>(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            return Connection.QuerySingleOrDefault<U>(p_sql, param, transaction);
        }

        public T GetBy(string where, object param = null, IDbTransaction transaction = null)
        {
            string p_sql = $"select * from {TableName} {where}";
            return Connection.QuerySingleOrDefault<T>(p_sql, param, transaction);
        }

        public T GetByKey(object key, IDbTransaction transaction = null)
        {
            string p_sql = $"select * from {TableName} where {PrimaryKeyName} = @key;";
            return Connection.QuerySingleOrDefault<T>(p_sql, new { key }, transaction);
        }
        #endregion

        #region Paging
        public PageDataView<U> GetPageList<U>(int pageIndex, int pageSize, IList<string> p_sqls, object param = null, IDbTransaction transaction = null)
        {
            int totalCount = Connection.ExecuteScalar<int>(p_sqls[0] + ";", param, transaction);
            IList<U> items = Connection.Query<U>(p_sqls[1] + $" limit {pageSize} offset {(pageIndex - 1) * pageSize};", param, transaction).ToList();
            PageDataView<U> result = new PageDataView<U>()
            {
                PageIndex = pageIndex,
                PageSize = pageSize,
                TotalCount = totalCount,
                Items = items
            };
            return result;
        }

        public PageDataView<U> GetPageListBy<U>(int pageIndex, int pageSize, string where, object param = null, IDbTransaction transaction = null)
        {
            string p_sql_count = $"select count(*) from {TableName} {where};";
            int totalCount = Connection.ExecuteScalar<int>(p_sql_count, param, transaction);
            string p_sql = $"select * from {TableName} {where} limit {pageSize} offset {(pageIndex - 1) * pageSize};";
            IList<U> items = Connection.Query<U>(p_sql, param, transaction).ToList();
            PageDataView<U> result = new PageDataView<U>() 
            { 
                PageIndex = pageIndex,
                PageSize = pageSize,
                TotalCount = totalCount,
                Items = items
            };
            return result;
        }
        #endregion

        #region IsExists
        public bool IsExists(string where, object param = null, IDbTransaction transaction = null)
        {
            string p_sql = $"select count(*) from {TableName} {where}";
            int result = Connection.ExecuteScalar<int>(p_sql, param, transaction);
            return result > 0;
        }

        public bool IsExists<U>(string where, object param = null, IDbTransaction transaction = null)
        {
            Type type = typeof(U);
            var tableName = type.Name;
            var at = type.GetCustomAttribute<TableAttribute>();
            if (at != null)
            {
                tableName = at.Name;
            }
            string p_sql = $"select count(*) from {tableName} {where}";
            int result = Connection.ExecuteScalar<int>(p_sql, param, transaction);
            return result > 0;
        }
        #endregion

        #region List
        public IList<T> List(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            return Connection.Query<T>(p_sql, transaction: transaction).ToList();
        }

        public IList<U> List<U>(string p_sql, object param = null, IDbTransaction transaction = null)
        {
            return Connection.Query<U>(p_sql, transaction: transaction).ToList();
        }

        public IList<T> ListAll(IDbTransaction transaction = null)
        {
            string p_sql = $"select * from {TableName};";
            return Connection.Query<T>(p_sql, transaction: transaction).ToList();
        }

        public IList<T> ListBy(string where, object param = null, IDbTransaction transaction = null)
        {
            string p_sql = $"select * from {TableName} {where};";
            return Connection.Query<T>(p_sql, transaction: transaction).ToList();
        }
        #endregion

        #region QueryMultiple
        public SqlMapper.GridReader QueryMultiple(string p_sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return Connection.QueryMultiple(p_sql, transaction: transaction);
        }
        #endregion
    }
}

　　

 Dapper参数类型

 匿名参数类型：

执行一次SQL命令：
new {Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1"}
执行多次SQL命令：
new[]
{
    new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_1"},
    new {Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_2"},
    new {Kind = InvoiceKind.StoreInvoice, Code = "Many_Insert_3"}
}

 列表类型参数：

Dapper允许您使用列表在IN子句中指定多个参数：
new {Kind = new[] { InvoiceKind.StoreInvoice, InvoiceKind.WebInvoice }}

 类型参数：

var affectedRows = connection.Execute(sql, employeeInfo);

　　

 Dapper结果

 

 

 

结果匿名：

var invoices = connection.Query(sql).ToList();

 结果强类型：

var invoices = connection.Query<Invoice>(sql).ToList();

 结果多映射（没有使用这种方式，多表我直接强类型或者分成多个方法）：

查询多映射（一对一）：
    var invoices = connection.Query<Invoice, InvoiceDetail, Invoice>(
            sql,
            (invoice, invoiceDetail) =>
            {
                invoice.InvoiceDetail = invoiceDetail;
                return invoice;
            },
            splitOn: "InvoiceID")
        .Distinct()
        .ToList();
查询多映射（一对多）：
    var invoiceDictionary = new Dictionary<int, Invoice>();

    var invoices = connection.Query<Invoice, InvoiceItem, Invoice>(
            sql,
            (invoice, invoiceItem) =>
            {
                Invoice invoiceEntry;

                if (!invoiceDictionary.TryGetValue(invoice.InvoiceID, out invoiceEntry))
                {
                    invoiceEntry = invoice;
                    invoiceEntry.Items = new List<InvoiceItem>();
                    invoiceDictionary.Add(invoiceEntry.InvoiceID, invoiceEntry);
                }

                invoiceEntry.Items.Add(invoiceItem);
                return invoiceEntry;
            },
            splitOn: "InvoiceID")
        .Distinct()
        .ToList();

 结果多结果：

using (var multi = connection.QueryMultiple(sql, new {InvoiceID = 1}))
{
    var invoice = multi.Read<Invoice>().First();
    var invoiceItems = multi.Read<InvoiceItem>().ToList();
}

 结果多类型（没有使用这种方式）：

    var invoices = new List<Invoice>();

    using (var reader = connection.ExecuteReader(sql))
    {
        var storeInvoiceParser = reader.GetRowParser<StoreInvoice>();
        var webInvoiceParser = reader.GetRowParser<WebInvoice>();

        while (reader.Read())
        {
            Invoice invoice;

            switch ((InvoiceKind) reader.GetInt32(reader.GetOrdinal("Kind")))
            {
                case InvoiceKind.StoreInvoice:
                    invoice = storeInvoiceParser(reader);
                    break;
                case InvoiceKind.WebInvoice:
                    invoice = webInvoiceParser(reader);
                    break;
                default:
                    throw new Exception(ExceptionMessage.GeneralException);
            }

            invoices.Add(invoice);
        }
    }

 

 Dapper扩展： 插入时返回自增长id

var sql = @"INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff);";
sql += "SELECT CAST(SCOPE_IDENTITY() as int)";
var mytable = new MyTable();
mytable.Stuff = "test";
var id = connection.QueryFirstOfDefault<int>(sql, mytable);

 

 Dapper扩展： 工厂模式（仅供参考）

利用工厂模式创建仓库：

IFactoryRepository：

    /// <summary>
    /// 创建仓库接口
    /// </summary>
    public interface IFactoryRepository
    {
        /// <summary>
        /// 创建仓库
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <returns></returns>
        IRepository<T> CreateRepository<T>(IDapperContext context) where T : class;
    }

 FactoryRepository：

    /// <summary>
    /// 工厂
    /// </summary>
    public class FactoryRepository : IFactoryRepository
    {
        /// <summary>
        /// 创建Repository
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <returns></returns>
        public IRepository<T> CreateRepository<T>(IDapperContext context) where T : class
        {
            IRepository<T> repository = new Repository<T>(context);
            return repository;
        }
    }

　　

 Dapper扩展： 读写分离（仅供参考）

 IDapperContext ：

    /// <summary>
    /// Dapper上下文
    /// </summary>
    public interface IDapperContext : IDisposable
    {
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        IDbConnection ReadConnection { get; }
 
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        IDbConnection WriteConnection { get; }
    }

 DapperContext：

    public class DapperContext : IDapperContext
    {
        /// <summary>
        /// 读连接字符串
        /// </summary>
        private string _readConnectionString;
 
        /// <summary>
        /// 写连接字符串
        /// </summary>
        private string _writeConnectionString;
 
        private bool _useMiniProfiling;
 
        /// <summary>
        /// 读连接
        /// </summary>
        private IDbConnection _readConnection;
 
        /// <summary>
        /// 写连接
        /// </summary>
        private IDbConnection _wrteConnection;
 
        /// <summary>
        /// 配置
        /// </summary>
        private readonly AppSetting _appSetting;
 
        /// <summary>
        /// 构造函数注入IOptions
        /// </summary>
        /// <param name="appSetting"></param>
        public DapperContext(IOptions<AppSetting> appSetting)
        {
            _appSetting = appSetting.Value;
            _readConnectionString = _appSetting.ReadOnlyConnectionString;
            _writeConnectionString = _appSetting.SetConnectionString;
            _useMiniProfiling = _appSetting.UseMiniProfiling;
        }
 
        /// <summary>
        /// 连接字符串
        /// </summary>
        /// <param name="connectionString"></param>
        public DapperContext(string connectionString)
        {
            _readConnectionString = connectionString;
        }
 
        #region 读
 
        /// <summary>
        /// 获取连接
        /// </summary>
        public IDbConnection ReadConnection
        {
            get
            {
                if (_readConnection == null || _readConnection.State == ConnectionState.Closed)
                {
                    if (_useMiniProfiling)
                    {
                        _readConnection = new ProfiledDbConnection(new MySqlConnection(_readConnectionString), MiniProfiler.Current);
                    }
                    else
                    {
                        _readConnection = new MySqlConnection(_readConnectionString);
                    }
                }
                if (_readConnection.State != ConnectionState.Open)
                {
                    _readConnection.Open();
                }
                return _readConnection;
            }
        }
 
        /// <summary>
        /// 释放连接
        /// </summary>
        public void Dispose()
        {
            if (_readConnection != null && _readConnection.State == ConnectionState.Open)
                _readConnection.Close();
            if (_wrteConnection != null && _wrteConnection.State == ConnectionState.Open)
                _wrteConnection.Close();
        }
 
        #endregion 读
 
        #region 写
 
        /// <summary>
        /// 获取连接
        /// </summary>
        public IDbConnection WriteConnection
        {
            get
            {
                if (_wrteConnection == null || _wrteConnection.State == ConnectionState.Closed)
                {
                    if (_useMiniProfiling)
                    {
                        _wrteConnection = new ProfiledDbConnection(new MySqlConnection(_writeConnectionString), MiniProfiler.Current);
                    }
                    else
                    {
                        _wrteConnection = new MySqlConnection(_writeConnectionString);
                    }
                }
                if (_wrteConnection.State != ConnectionState.Open)
                {
                    _wrteConnection.Open();
                }
                return _wrteConnection;
            }
        }
 
        #endregion 写
    }