之前使用Dapper都是搭配github上一些其他网友开发的扩展库进行的，时间久了，大多都不更新了，也在使用的时候遇到些BUG。最后回到了最开始的地方，使用起了Dapper.Contrib，并对它进行了一些简单扩展。
现如今的.NET Core的库都很成熟，也很方便，简单的ORM不想用EF，可以直接用Dapper满足需求。
第一步是建立一个通用化的接口
public interface IBaseService<T> where T : class
{
/// <summary>
///
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
long Insert(T t);
/// <summary>
///
/// </summary>
/// <param name="t"></param>
long Insert(List<T> t);
/// <summary>
///
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
bool Update(T t);
/// <summary>
///
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
bool Update(List<T> t);
/// <summary>
/// 单行结果
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
T Get(object id);
/// <summary>
/// 所有结果
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
List<T> GetAll();
/// <summary>
/// 带条件查询
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
List<T> GetList(string sql, object parameters = null);
/// <summary>
/// 多结果集返回
/// </summary>
/// <param name="sql"></param>
/// <param name="func">外部自行定义读取方式</param>
/// <param name="parameters"></param>
void GetList(string sql, Action<SqlMapper.GridReader> act, object parameters = null);
/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
bool Delete(T id);
/// <summary>
///
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
bool Delete(List<T> list);
/// <summary>
///
/// </summary>
/// <returns></returns>
bool DeleteAll();
/// <summary>
/// 非多表关联的翻页
/// </summary>
/// <param name="func">结果集处理</param>
/// <param name="page">页码</param>
/// <param name="limit">偏移</param>
/// <param name="count">总数</param>
/// <param name="parameters">查询条件</param>
void Pagination(Action<SqlMapper.GridReader> act, int page, int limit,
string tableName = "", string selectColumns = "*", string sort = "", Func<string, string, string> conditionsFunc = null, object parameters = null);
}
第二步是对应的一些扩展，很多代码都是直接抄Dapper.Contrib的，需要读取表名、键名，主要是用来灵活的分页（狗头保命）。
public class SQLLib
{
private static readonly ConcurrentDictionary<RuntimeTypeHandle, string> TypeTableName = new ConcurrentDictionary<RuntimeTypeHandle, string>();
private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> KeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> TypeProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> ExplicitKeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
/// <summary>
/// sql体系
/// </summary>
public enum SQLDialect
{
SQLServerOld = 0,
PostgreSQL = 1,
SQLite = 2,
MySQL = 3,
SQLServerNew = 4,
}
/// <summary>
/// 返回分页模板
/// </summary>
/// <param name="dialect"></param>
/// <returns></returns>
public static string GetPaginationTemplate(SQLDialect dialect)
{
switch (dialect)
{
case SQLDialect.PostgreSQL:
//_encapsulation = "\"{0}\"";
//_getIdentitySql = string.Format("SELECT LASTVAL() AS id");
return "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage});";
//break;
case SQLDialect.SQLite:
//_encapsulation = "\"{0}\"";
//_getIdentitySql = string.Format("SELECT LAST_INSERT_ROWID() AS id");
return "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage});";
//break;
case SQLDialect.MySQL:
//_encapsulation = "`{0}`";
//_getIdentitySql = string.Format("SELECT LAST_INSERT_ID() AS id");
return "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {Offset},{RowsPerPage};";
//break;
case SQLDialect.SQLServerNew:
return @"SELECT {SelectColumns} FROM {TableName} {WhereClause} ORDER BY {OrderBy} OFFSET {Offset} ROWS FETCH NEXT {RowsPerPage} ROWS ONLY;";
default:
//_encapsulation = "[{0}]";
//_getIdentitySql = string.Format("SELECT CAST(SCOPE_IDENTITY() AS BIGINT) AS [id]");
return "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {OrderBy}) AS PagedNumber, {SelectColumns} FROM {TableName} {WhereClause}) AS u WHERE PagedNumber BETWEEN (({PageNumber}-1) * {RowsPerPage} + 1) AND ({PageNumber} * {RowsPerPage});";
//break;
}
}
/// <summary>
/// 返回分页语句
/// </summary>
/// <param name="dialect"></param>
/// <param name="TableName">表名</param>
/// <param name="SelectColumns">查询列</param>
/// <param name="pageNumber">页码</param>
/// <param name="rowsPerPage">每页行数</param>
/// <param name="conditions">查询条件</param>
/// <param name="orderby">排序条件</param>
/// <returns></returns>
public static string PaginationSQL(SQLDialect dialect, string tableName, string selectColumns, int pageNumber, int rowsPerPage, string conditions, string orderby)
{
string query = GetPaginationTemplate(dialect);
query = query.Replace("{SelectColumns}", selectColumns);//查询列
query = query.Replace("{TableName}", tableName);//表名
query = query.Replace("{PageNumber}", pageNumber.ToString());//页码
query = query.Replace("{RowsPerPage}", rowsPerPage.ToString());//每页行数
query = query.Replace("{OrderBy}", orderby);//排序条件
query = query.Replace("{WhereClause}", conditions);//查询条件
query = query.Replace("{Offset}", ((pageNumber - 1) * rowsPerPage).ToString());//偏移量
return query;
}
/// <summary>
/// 生成取总数的SQL
/// </summary>
/// <param name="tableName"></param>
/// <param name="conditions"></param>
/// <returns></returns>
public static string CountSQL(string tableName, string conditions = "", string countColumn = "*")
{
string query = "SELECT count({CountColumn}) FROM {TableName} {WhereClause};";
query = query.Replace("{CountColumn}", countColumn);//列名
query = query.Replace("{TableName}", tableName);//表名
query = query.Replace("{WhereClause}", conditions);//查询条件
return query;
}
/// <summary>
/// 去类对应的表名
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static string GetTableName(Type type)
{
if (TypeTableName.TryGetValue(type.TypeHandle, out string name)) return name;
//来自Dapper.Contrib源码
//NOTE: This as dynamic trick falls back to handle both our own Table-attribute as well as the one in EntityFramework
var tableAttrName =
type.GetCustomAttribute<Dapper.Contrib.Extensions.TableAttribute>(false)?.Name
?? (type.GetCustomAttributes(false).FirstOrDefault(attr => attr.GetType().Name == "TableAttribute") as dynamic)?.Name;
if (tableAttrName != null)
{
name = tableAttrName;
}
else
{
name = type.Name + "s";
if (type.IsInterface && name.StartsWith("I"))
name = name.Substring(1);
}
TypeTableName[type.TypeHandle] = name;
return name;
}
public static PropertyInfo GetSingleKey<T>(string method)
{
var type = typeof(T);
var keys = KeyPropertiesCache(type);
var explicitKeys = ExplicitKeyPropertiesCache(type);
var keyCount = keys.Count + explicitKeys.Count;
if (keyCount > 1)
throw new DataException($"{method}<T> only supports an entity with a single [Key] or [ExplicitKey] property. [Key] Count: {keys.Count}, [ExplicitKey] Count: {explicitKeys.Count}");
if (keyCount == 0)
throw new DataException($"{method}<T> only supports an entity with a [Key] or an [ExplicitKey] property");
return keys.Count > 0 ? keys[0] : explicitKeys[0];
}
private static List<PropertyInfo> KeyPropertiesCache(Type type)
{
if (KeyProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pi))
{
return pi.ToList();
}
var allProperties = TypePropertiesCache(type);
var keyProperties = allProperties.Where(p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute)).ToList();
if (keyProperties.Count == 0)
{
var idProp = allProperties.Find(p => string.Equals(p.Name, "id", StringComparison.CurrentCultureIgnoreCase));
if (idProp != null && !idProp.GetCustomAttributes(true).Any(a => a is ExplicitKeyAttribute))
{
keyProperties.Add(idProp);
}
}
KeyProperties[type.TypeHandle] = keyProperties;
return keyProperties;
}
private static List<PropertyInfo> TypePropertiesCache(Type type)
{
if (TypeProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pis))
{
return pis.ToList();
}
var properties = type.GetProperties().Where(IsWriteable).ToArray();
TypeProperties[type.TypeHandle] = properties;
return properties.ToList();
}
private static bool IsWriteable(PropertyInfo pi)
{
var attributes = pi.GetCustomAttributes(typeof(WriteAttribute), false).AsList();
if (attributes.Count != 1) return true;
var writeAttribute = (WriteAttribute)attributes[0];
return writeAttribute.Write;
}
private static List<PropertyInfo> ExplicitKeyPropertiesCache(Type type)
{
if (ExplicitKeyProperties.TryGetValue(type.TypeHandle, out IEnumerable<PropertyInfo> pi))
{
return pi.ToList();
}
var explicitKeyProperties = TypePropertiesCache(type).Where(p => p.GetCustomAttributes(true).Any(a => a is ExplicitKeyAttribute)).ToList();
ExplicitKeyProperties[type.TypeHandle] = explicitKeyProperties;
return explicitKeyProperties;
}
}
public static class Extensions
{
/// <summary>
/// 生成取总数的SQL
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="entity"></param>
/// <param name="tableName"></param>
/// <param name="conditions"></param>
/// <param name="countColumn"></param>
/// <returns></returns>
public static int Count<T>(this IDbConnection connection, T entity, string tableName = "", string conditions = "", string countColumn = "*")
{
if (string.IsNullOrEmpty(tableName))
{
var type = typeof(T);
tableName = SQLLib.GetTableName(type);
}
return connection.Query<int>(SQLLib.CountSQL(tableName, conditions, countColumn)).FirstOrDefault();
}
}
第三步，建一个服务测试，DB是在Startup.ConfigureServices进行注入的配置
public interface IMarketService:IBaseService<Market>
{
//有扩展写这里
}
public class MarketService : IMarketService
{
private readonly DB _DB;//注入配置
public MarketService(IOptions<DB> db)
{
_DB = db.Value;
}
public bool Delete(Market id)
{
using (var conn = new SqlConnection(_DB.CRM)) {
return conn.Delete(id);
}
}
public bool Delete(List<Market> list)
{
using (var conn = new SqlConnection(_DB.CRM))
{
return conn.Delete(list);
}
}
public bool DeleteAll()
{
using (var conn = new SqlConnection(_DB.CRM))
{
return conn.DeleteAll<Market>();
}
}
public Market Get(object id)
{
using (var conn = new SqlConnection(_DB.CRM))
{
return conn.Get<Market>(id);
}
}
public List<Market> GetAll()
{
using (var conn = new SqlConnection(_DB.CRM))
{
return conn.GetAll<Market>().ToList();
}
}
public List<Market> GetList(string sql, object parameters = null)
{
using (var conn = new SqlConnection(_DB.CRM))
{
return conn.Query<Market>(sql).ToList();
}
}
public void GetList(string sql, Action<SqlMapper.GridReader> act, object parameters = null)
{
using (var conn = new SqlConnection(_DB.CRM)) {
using (var multi = conn.QueryMultiple(sql, parameters))
{
act(multi);
}
}
}
public long Insert(Market t)
{
using (var conn = new SqlConnection(_DB.CRM))
{
return conn.Insert(t);
}
}
public long Insert(List<Market> t)
{
using (var conn = new SqlConnection(_DB.CRM))
{
return conn.Insert(t);
}
}
/// <summary>
///
/// </summary>
/// <param name="func"></param>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <param name="tableName">默认不填 去类的表名</param>
/// <param name="selectColumns">默认全部*</param>
/// <param name="sort"></param>
/// <param name="conditionsFunc"></param>
/// <param name="parameters"></param>
public void Pagination(Action<SqlMapper.GridReader> act, int page, int limit,
string tableName = "",string selectColumns="*",string sort= "",Func<string,string,string> conditionsFunc=null, object parameters = null)
{
using (var conn = new SqlConnection(_DB.CRM))
{
if (string.IsNullOrEmpty(tableName)) {
tableName = SQLLib.GetTableName(typeof(Market));
}
if (string.IsNullOrEmpty(sort))
{
var key = SQLLib.GetSingleKey<Market>(nameof(Pagination));
sort = $"{key.Name} DESC";
}
string prefix = "where 1=1 ";
string conditions = "";
if (conditionsFunc!=null) {
conditions = conditionsFunc(prefix, tableName);
}
string sql = SQLLib.PaginationSQL(SQLLib.SQLDialect.SQLServerOld, tableName, selectColumns, page, limit, conditions, sort);
string countSql = SQLLib.CountSQL(SQLLib.GetTableName(typeof(Market)), conditions);
Debug.WriteLine(sql + countSql);
using (var multi = conn.QueryMultiple(sql + countSql, parameters))
{
act(multi);
}
}
}
public bool Update(Market t)
{
using (var conn = new SqlConnection(_DB.CRM))
{
return conn.Update(t);
}
}
public bool Update(List<Market> t)
{
using (var conn = new SqlConnection(_DB.CRM))
{
return conn.Update(t);
}
}
}
第四步，在Controller内读取：
var marketList = new List<Market>();
_marketService.Pagination((x) =>
{
marketList = x.Read<Market>().ToList();
}, 1, 10);