42岁大龄程序员的迷茫，看我最新尝鲜.net 5+Dapper搭建的WebAPI框架
42岁大龄程序员的迷茫
我真傻,真的。我单知道雪天是野兽在深山里没有食吃,会到村里来;我不知道春天也会有……
我真傻,真的。我单知道程序员要活到老学到老,年龄大了要失业;我不知道码农（新型农民工）也会有……
上周回老家有点无聊就去小破站看了点视频，是讲Dapr的实践（朝夕教育某讲师的公开课录屏），看完非常之震撼：原来微服务离我那么近！
虽然有失业的风险，但是我还是觉得技术人嘛，养家糊口应该没问题的，压力是有点大，但是“办法总比困难多”。所以其实我也不迷茫......
好长时间没有更新博客了，因为我觉得“Show me the Code!”比较重要，最近用.net 5+Dapper搭建了一个WebAPI的开发框架，今天分享给大伙。
几年前有一篇类似的文章大伙可以回顾一下：一次asp.net core3.1打造webapi开发框架的实践
开始show you the code
实践技术看点
- 1、Swagger管理API说明文档
- 2、JwtBearer token验证
- 3、Swagger UI增加Authentication
- 4、Dapper实现的Repository
- 5、在.net 5下使用Log4net
- 6、与钉钉开放平台交互
项目中使用到的包清单：
<ItemGroup>
<PackageReference Include="Hangfire.AspNetCore" Version="1.7.24" />
<PackageReference Include="Hangfire.HttpJob.Agent" Version="1.4.2" />
<PackageReference Include="Hangfire.HttpJob.Agent.MssqlConsole" Version="1.4.2" />
<PackageReference Include="Hangfire.MemoryStorage" Version="1.7.0" />
<PackageReference Include="Hangfire.SqlServer" Version="1.7.24" />
<PackageReference Include="log4net" Version="2.0.12" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.JwtBearer" Version="5.0.9" />
<PackageReference Include="Microsoft.AspNetCore.Authorization" Version="5.0.9" />
<PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
<PackageReference Include="Swashbuckle.AspNetCore.Swagger" Version="6.1.5" />
<PackageReference Include="Swashbuckle.AspNetCore.SwaggerGen" Version="6.1.5" />
<PackageReference Include="Swashbuckle.AspNetCore.SwaggerUI" Version="6.1.5" />
</ItemGroup>
关键代码展示：
1）StartUP
这个只贴图吧，教程大伙都看吐了，司空见惯：
用的是标准的套路熟悉的代码。
#region JWT services.Configure<TokenManagement>(Configuration.GetSection("tokenManagement")); var token = Configuration.GetSection("tokenManagement").Get<TokenManagement>(); services.AddAuthentication(x => { x.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme; x.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme; }).AddJwtBearer(x => { x.RequireHttpsMetadata = false; x.SaveToken = true; x.TokenValidationParameters = new TokenValidationParameters { ValidateIssuerSigningKey = true, IssuerSigningKey = new SymmetricSecurityKey(Encoding.ASCII.GetBytes(token.Secret)), ValidIssuer = token.Issuer, ValidAudience = token.Audience, ValidateIssuer = false, ValidateAudience = false }; }); #endregion
#region Swagger services.AddSwaggerGen(c => { c.SwaggerDoc("v1", new OpenApiInfo { Title = "TSP车载MES接口文档", Version = "v1", Contact = new OpenApiContact { Email = "tsjg@ts-precision.com", Name = "MES团队", Url = new Uri("http://www.ts-precision.com/") } }); // 为 Swagger 设置xml文档注释路径 var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml"; var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile); c.IncludeXmlComments(xmlPath); c.AddSecurityDefinition("Bearer", new OpenApiSecurityScheme { Description = "请输入OAuth接口返回的Token，前置Bearer。示例：Bearer {Roken}", Name = "Authorization", In = ParameterLocation.Header, Type = SecuritySchemeType.ApiKey }); c.AddSecurityRequirement(new OpenApiSecurityRequirement { { new OpenApiSecurityScheme { Reference = new OpenApiReference() { Id = "Bearer", Type = ReferenceType.SecurityScheme } }, Array.Empty<string>() } }); }); #endregion
#region Hangfire
string HangfireConn = Configuration.GetConnectionString("HangfireDB");
//SqlServer持久性
services.AddHangfire(x => x.UseStorage(new SqlServerStorage(
HangfireConn,
new SqlServerStorageOptions
{
QueuePollInterval = TimeSpan.FromSeconds(15),//- 作业队列轮询间隔。默认值为15秒。
JobExpirationCheckInterval = TimeSpan.FromHours(1),//- 作业到期检查间隔（管理过期记录）。默认值为1小时。
CountersAggregateInterval = TimeSpan.FromMinutes(5),//- 聚合计数器的间隔。默认为5分钟。
PrepareSchemaIfNecessary = true,//- 如果设置为true，则创建数据库表。默认是true。
DashboardJobListLimit = 50000,//- 仪表板作业列表限制。默认值为50000。
TransactionTimeout = TimeSpan.FromMinutes(1),//- 交易超时。默认为1分钟。
}))
);
services.AddHangfireHttpJobAgent();
//Hangfire非持久性
//services.AddHangfire(x => x.UseStorage(new MemoryStorage()));
#endregion
2）Dapper相关：
DynamicQuery实现半自动sql编写：
public static class DynamicQuery { public static string GetUpdateQuery(string tableName, dynamic item) { PropertyInfo[] props = item.GetType().GetProperties(); string[] columns = props.Select(p => p.Name).ToArray(); List<string> parameters = columns.Select(name => name + "=@" + name).ToList(); return string.Format("UPDATE {0} SET {1} WHERE ID=@ID", tableName, string.Join(",", parameters)); } public static string GetInsertQuery(string tableName, dynamic item) { PropertyInfo[] props = item.GetType().GetProperties(); string[] columns = props.Select(p => p.Name).Where(s => s != "ID").ToArray(); return string.Format("INSERT INTO {0} ({1}) OUTPUT inserted.ID VALUES (@{2})", tableName,string.Join(",", columns),string.Join(",@", columns)); } public static QueryResult GetDynamicQuery<T>(string tableName, Expression<Func<T, bool>> expression) { List<QueryParameter> queryProperties = new List<QueryParameter>(); try { BinaryExpression body = (BinaryExpression)expression.Body; WalkTree(body, ExpressionType.Default, ref queryProperties); } catch (Exception) { WalkTree(expression.Body, ExpressionType.Default, ref queryProperties); } IDictionary<string, object> expando = new ExpandoObject(); StringBuilder builder = new StringBuilder(); builder.Append("SELECT * FROM "); builder.Append($"{tableName} WITH(NOLOCK)"); builder.Append(" WHERE "); for (int i = 0; i < queryProperties.Count; i++) { QueryParameter item = queryProperties[i]; if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0) { builder.Append(string.Format("{0} {1} {2} @{1} ", item.LinkingOperator, item.PropertyName, item.QueryOperator)); } else { builder.Append(string.Format("{0} {1} @{0} ", item.PropertyName, item.QueryOperator)); } expando[item.PropertyName] = item.PropertyValue; } return new QueryResult(builder.ToString().TrimEnd(), expando); } private static void WalkTree(Expression body, ExpressionType linkingType, ref List<QueryParameter> queryProperties) { if (body is BinaryExpression) { var body2 = body as BinaryExpression; if (body2.NodeType != ExpressionType.AndAlso && body2.NodeType != ExpressionType.OrElse) { string propertyName = GetPropertyName(body2); object propertyValue = GetPropertyValue(body2.Right); string opr = GetOperator(body.NodeType); string link = GetOperator(linkingType); queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr)); } else { WalkTree(body2.Left, body.NodeType, ref queryProperties); WalkTree(body2.Right, body.NodeType, ref queryProperties); } } if (body is MethodCallExpression) { var body2 = body as MethodCallExpression; string propertyName = body2.Object.ToString().Split(".").LastOrDefault(); object propertyValue = body2.Arguments.FirstOrDefault(); string link = GetOperator(linkingType); if (body2.Method.Name.Equals("Contains")) { string val = propertyValue.ToString().Replace(((char)34).ToString(), ""); if (!val.Contains("%")) queryProperties.Add(new QueryParameter(link, propertyName, $"%{val}%", "LIKE")); else queryProperties.Add(new QueryParameter(link, propertyName, $"{val}", "LIKE")); } if (body2.Method.Name.Equals("Equals")) { string val = propertyValue.ToString().Replace(((char)34).ToString(), ""); queryProperties.Add(new QueryParameter(link, propertyName, val, "=")); } } } private static object GetPropertyValue(Expression source) { ConstantExpression constantExpression = source as ConstantExpression; if (constantExpression != null) { return constantExpression.Value; } Expression<Func<object>> evalExpr = Expression.Lambda<Func<object>>(Expression.Convert(source, typeof(object))); Func<object> evalFunc = evalExpr.Compile(); object value = evalFunc(); return value; } private static string GetPropertyName(BinaryExpression body) { string propertyName = body.Left.ToString().Split(new char[] { '.' })[1]; if (body.Left.NodeType == ExpressionType.Convert) { // hack to remove the trailing ) when convering. propertyName = propertyName.Replace(")", string.Empty); } return propertyName; } private static string GetOperator(ExpressionType type) { switch (type) { case ExpressionType.Equal: return "="; case ExpressionType.NotEqual: return "!="; case ExpressionType.LessThan: return "<"; case ExpressionType.GreaterThan: return ">"; case ExpressionType.AndAlso: case ExpressionType.And: return "AND"; case ExpressionType.Or: case ExpressionType.OrElse: return "OR"; case ExpressionType.Default: return string.Empty; case ExpressionType.GreaterThanOrEqual: return ">="; case ExpressionType.LessThanOrEqual: return "<="; default: throw new NotImplementedException(); } } }
基于Dapper的DbContext :DapperDbContextBase
public abstract class DapperDbContextBase : IDbContext { #region Constructors /// <summary> /// 构造函数 /// </summary> /// <param name="connectString">连接字符串</param> protected DapperDbContextBase(string connectString) { ConnectString = connectString; } #endregion Constructors #region Properties /// <summary> ///获取 是否开启事务提交 /// </summary> public IDbTransaction CurrentTransaction { get; private set; } #endregion Properties #region Fields /// <summary> /// 当前数据库连接 /// </summary> public IDbConnection CurrentConnection => TransactionEnabled ? CurrentTransaction.Connection : CreateConnection(); /// <summary> /// 获取 是否开启事务提交 /// </summary> public bool TransactionEnabled => CurrentTransaction != null; /// <summary> /// 连接字符串 /// </summary> protected readonly string ConnectString; #endregion Fields #region Methods /// <summary> /// 显式开启数据上下文事务 /// </summary> /// <param name="isolationLevel">指定连接的事务锁定行为</param> public void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.Unspecified) { if (!TransactionEnabled) CurrentTransaction = CreateConnection().BeginTransaction(isolationLevel); } /// <summary> /// 提交当前上下文的事务更改 /// </summary> /// <exception cref="DataAccessException">提交数据更新时发生异常：" + msg</exception> public void Commit() { if (TransactionEnabled) try { CurrentTransaction.Commit(); } catch (Exception ex) { if (ex.InnerException?.InnerException is SqlException sqlEx) { var msg = DataBaseHelper.GetSqlExceptionMessage(sqlEx.Number); throw new DataAccessException("提交数据更新时发生异常：" + msg, sqlEx); } throw; } } /// <summary> /// 创建记录 /// </summary> /// <param name="entity">需要操作的实体类</param> /// <returns>操作是否成功</returns> public bool Create<T>(T entity) where T : ModelBase { return CurrentConnection.Insert(new List<T> { entity }, CurrentTransaction) > 0; } /// <summary> ///异步创建记录 /// </summary> /// <param name="entity">需要操作的实体类</param> /// <returns>操作是否成功</returns> public async Task<bool> CreateAsync<T>(T entity) where T : ModelBase { var result = await CurrentConnection.InsertAsync( new List<T> { entity }, CurrentTransaction); bool b = result > 0; return b; } /// <summary> ///创建数据库连接IDbConnection /// </summary> /// <returns></returns> public abstract IDbConnection CreateConnection(); /// <summary> /// 删除记录 /// </summary> /// <returns>操作是否成功</returns> /// <param name="entity">需要操作的实体类.</param> public bool Delete<T>(T entity) where T : ModelBase { return CurrentConnection.Delete(entity); } /// <summary> /// 异步删除记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <returns></returns> public async Task<bool> DeleteAsync<T>(T entity) where T : ModelBase { var result = await CurrentConnection.DeleteAsync(entity); return result; } /// <summary> ///条件判断是否存在 /// </summary> /// <returns>是否存在</returns> /// <param name="predicate">判断条件委托</param> public bool Exist<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); var result = CurrentConnection.ExecuteScalar(queryResult.Sql, (object)queryResult.Param, CurrentTransaction); return result != null; } /// <summary> /// 异步判断符合条件的实体是否存在 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <returns></returns> public async Task<bool> ExistAsync<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); var result = await CurrentConnection.ExecuteScalarAsync(queryResult.Sql, (object)queryResult.Param, CurrentTransaction); return result != null; } /// <summary> /// 根据id获取记录 /// </summary> /// <returns>记录</returns> /// <param name="id">id.</param> public T GetByKeyId<T>(object id) where T : ModelBase { return CurrentConnection.Get<T>(id, CurrentTransaction); } /// <summary> /// 异步根据id获取记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="id"></param> /// <returns></returns> public async Task<T> GetByKeyIdAsync<T>(object id) where T : ModelBase { var result = await CurrentConnection.GetAsync<T>(id, CurrentTransaction); return result; } /// <summary> ///条件获取记录集合 /// </summary> /// <returns>集合</returns> /// <param name="predicate">筛选条件.</param> public List<T> GetList<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); return CurrentConnection.Query<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction).ToList(); } /// <summary> /// 条件获取记录集合(异步) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <returns></returns> public async Task<List<T>> GetListAsync<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); var result = await CurrentConnection.QueryAsync<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction); return result.ToList(); } /// <summary> ///条件获取记录第一条或者默认 /// </summary> /// <returns>记录</returns> /// <param name="predicate">筛选条件.</param> public T GetFirstOrDefault<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); return CurrentConnection.QueryFirstOrDefault<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction); } /// <summary> /// 条件获取记录第一条或者默认(异步) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <returns></returns> public async Task<T> GetFirstOrDefaultAsync<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); var entity = await CurrentConnection.QueryFirstOrDefaultAsync<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction); return entity; } /// <summary> /// 条件查询 /// </summary> /// <returns>IQueryable</returns> /// <param name="predicate">筛选条件.</param> public IQueryable<T> Query<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); var result = CurrentConnection.Query<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction).ToList(); return result.AsQueryable(); } /// <summary> /// 条件查询(异步) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate"></param> /// <returns></returns> public async Task<IQueryable<T>> QueryAsync<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); var result = await CurrentConnection.QueryAsync<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction); return result.AsQueryable(); } /// <summary> ///显式回滚事务，仅在显式开启事务后有用 /// </summary> public void Rollback() { if (TransactionEnabled) CurrentTransaction.Rollback(); } /// <summary> ///执行Sql 脚本查询 /// </summary> /// <param name="sql">Sql语句</param> /// <param name="parameters">参数</param> /// <returns>集合</returns> public IEnumerable<T> SqlQuery<T>(string sql, IDbDataParameter[] parameters) { var dataParameters = CreateParameter(parameters); return CurrentConnection.Query<T>(sql, dataParameters, CurrentTransaction); } /// <summary> ///执行Sql 脚本查询(异步) /// </summary> /// <param name="sql">Sql语句</param> /// <param name="parameters">参数</param> /// <returns>集合</returns> public async Task<IEnumerable<T>> SqlQueryAsync<T>(string sql, IDbDataParameter[] parameters) { var dataParameters = CreateParameter(parameters); var list = await CurrentConnection.QueryAsync<T>(sql, dataParameters, CurrentTransaction); return list; } /// <summary> /// 执行Sql 脚本查询带分页(linq分页) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="parameters"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public PagedList<T> SqlQueryAndPagedList<T>(string sql, IDbDataParameter[] parameters, int pageIndex, int pageSize) { var dataParameters = CreateParameter(parameters); var result = CurrentConnection.Query<T>(sql, dataParameters, CurrentTransaction); return PageHelper.ToPagedList(result.AsQueryable(), pageIndex, pageSize); } /// <summary> /// 带分页（服务器端分页）的自定义查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="orderField"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="total"></param> /// <returns></returns> public IEnumerable<T> SqlQueryPage<T>(string sql, string orderField, int pageSize, int pageIndex, out int total) where T : class, new() { int num = (pageIndex - 1) * pageSize; int num1 = (pageIndex) * pageSize; orderField = "order by " + orderField; StringBuilder sb = new StringBuilder(); sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderField + ")"); sb.Append(" As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + ""); total = Convert.ToInt32(SqlQuery<int>("Select Count(1) From (" + sql + ") As t", null).FirstOrDefault()); string last_sql = sb.ToString(); var dataQuery = CurrentConnection.Query<T>(last_sql).ToList(); return dataQuery; } /// <summary> /// FindObjectBase对象通用查询带分页 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <param name="orderField"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="total"></param> /// <returns></returns> public IEnumerable<T> SqlQueryPage<T>(T t, string orderField, int pageSize, int pageIndex, out int total) where T : FindObjectBase, new() { string tableName = GetQueryTableName<T>(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM "); sb.Append($"{tableName} WITH(NOLOCK) "); sb.Append(" WHERE 1=1 AND "); var props = typeof(T).GetProperties().Where(p => !p.Name.StartsWith("Chk_")); foreach (var prop in props) { object obj = prop.GetValue(t, null); if (obj != null) { if (prop.Name.ToUpper().StartsWith("LIKE_")) { sb.Append($" {prop.Name.Replace("LIKE_", "")} LIKE {obj} AND "); } if (prop.Name.ToUpper().StartsWith("GT_")) { sb.Append($" {prop.Name.Replace("GT_", "")} > {obj} AND "); } if (prop.Name.ToUpper().StartsWith("ST_")) { sb.Append($" {prop.Name.Replace("ST_", "")} < {obj} AND "); } if (prop.Name.ToUpper().StartsWith("ST_Eq_")) { sb.Append($" {prop.Name.Replace("ST_Eq_", "")} <= {obj} AND "); } if (prop.Name.ToUpper().StartsWith("GT_Eq_")) { sb.Append($" {prop.Name.Replace("GT_Eq_", "")} >= {obj} AND "); } if (prop.Name.ToUpper().StartsWith("BETWEEN_")) { string[] array = obj.ToString().Split("|"); sb.Append($" {prop.Name.Replace("GT_Eq_", "")} BETWEEN {array.FirstOrDefault()} AND {array.LastOrDefault()} AND"); } else sb.Append($" {prop.Name}={obj} AND "); } } string sql = sb.ToString().ToUpper().TrimEnd("AND ".ToCharArray()); int num = (pageIndex - 1) * pageSize; int num1 = (pageIndex) * pageSize; orderField = "order by " + orderField; StringBuilder builder = new(); builder.Append("Select * From (Select ROW_NUMBER() Over (" + orderField + ")"); builder.Append(" As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + ""); total = Convert.ToInt32(SqlQuery<int>("Select Count(1) From (" + sql + ") As t", null).FirstOrDefault()); string last_sql = builder.ToString(); var dataQuery = CurrentConnection.Query<T>(last_sql).ToList(); return dataQuery; } /// <summary> /// FindObjectBase对象通用查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <returns></returns> public IEnumerable<T> SqlQuery<T>(T t) where T : FindObjectBase, new() { string tableName = GetQueryTableName<T>(); StringBuilder builder = new(); builder.Append("SELECT * FROM "); builder.Append($"{tableName} WITH(NOLOCK) "); builder.Append(" WHERE 1=1 AND "); var props = typeof(T).GetProperties(); foreach (var prop in props) { object obj = prop.GetValue(t, null); if (obj != null) { builder.Append($" {prop.Name}={obj} AND "); } } string sql = builder.ToString().ToUpper().TrimEnd("AND".ToCharArray()); var dataQuery = CurrentConnection.Query<T>(sql).ToList(); return dataQuery; } /// <summary> /// 自定义查询返回DataTable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public DataTable SqlQueryReturnDataTable<T>(string sql, IDbDataParameter[] parameters) { var dataParameters = CreateParameter(parameters); var list = CurrentConnection.Query<T>(sql, dataParameters, CurrentTransaction).ToList(); return ConvertExtension.ToDataTable(list); } /// <summary> /// 带分页（服务器端分页）的自定义查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="orderField"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="total"></param> /// <returns></returns> public DataTable SqlQueryReturnDataTable<T>(string sql, string orderField, int pageSize, int pageIndex, out int total) where T : class, new() { int num = (pageIndex - 1) * pageSize; int num1 = (pageIndex) * pageSize; orderField = "order by " + orderField; StringBuilder sb = new StringBuilder(); sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderField + ")"); sb.Append(" As rowNum, * From (" + sql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + ""); total = Convert.ToInt32(SqlQuery<int>("Select Count(1) From (" + sql + ") As t", null).FirstOrDefault()); string last_sql = sb.ToString(); var list = CurrentConnection.Query<T>(last_sql).ToList(); return ConvertExtension.ToDataTable(list); } /// <summary> ///更新实体类记录 /// </summary> /// <returns>操作是否成功.</returns> /// <param name="entity">实体类记录.</param> public bool Update<T>(T entity) where T : ModelBase { return CurrentConnection.Update(entity, CurrentTransaction); } /// <summary> /// 更新实体类记录(异步) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entity"></param> /// <returns></returns> public async Task<bool> UpdateAsync<T>(T entity) where T : ModelBase { return await CurrentConnection.UpdateAsync(entity, CurrentTransaction); } /// <summary> /// 构建Sql Parameter /// </summary> /// <param name="parameters"></param> /// <returns></returns> private DapperParameter CreateParameter(IDbDataParameter[] parameters) { if (!(parameters?.Any() ?? false)) return null; var dataParameters = new DapperParameter(); foreach (var parameter in parameters) dataParameters.Add(parameter); return dataParameters; } /// <summary> /// 获取实体的TableName /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> private string GetTableName<T>() where T : ModelBase { var tableCfgInfo = AttributeHelper.Get<T,TableAttribute>(); return tableCfgInfo != null ? tableCfgInfo.Name.Trim() : typeof(T).Name; } /// <summary> /// 获取实体的TableName /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> private string GetQueryTableName<T>() where T : FindObjectBase { var tableCfgInfo = AttributeHelper.Get<T,TableAttribute>(); return tableCfgInfo != null ? tableCfgInfo.Name.Trim() : typeof(T).Name; } /// <summary> ///执行与释放或重置非托管资源关联的应用程序定义的任务。 /// </summary> public void Dispose() { if (CurrentTransaction != null) { CurrentTransaction.Dispose(); CurrentTransaction = null; } CurrentConnection?.Dispose(); } public List<T> GetList<T>(T t) where T : FindObjectBase { string tableName = GetQueryTableName<T>(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM "); sb.Append($"{tableName} WITH(NOLOCK) "); sb.Append(" WHERE 1=1 AND "); var props = typeof(T).GetProperties().Where(p=>!p.Name.StartsWith("Chk_")); foreach (var prop in props) { object obj = prop.GetValue(t, null); if (obj != null) { if (prop.Name.ToUpper().StartsWith("LIKE_")) { sb.Append($" {prop.Name.Replace("LIKE_","")} LIKE {obj} AND "); } if (prop.Name.ToUpper().StartsWith("GT_")) { sb.Append($" {prop.Name.Replace("GT_", "")} > {obj} AND "); } if (prop.Name.ToUpper().StartsWith("ST_")) { sb.Append($" {prop.Name.Replace("ST_", "")} < {obj} AND "); } if (prop.Name.ToUpper().StartsWith("ST_Eq_")) { sb.Append($" {prop.Name.Replace("ST_Eq_", "")} <= {obj} AND "); } if (prop.Name.ToUpper().StartsWith("GT_Eq_")) { sb.Append($" {prop.Name.Replace("GT_Eq_", "")} >= {obj} AND "); } if (prop.Name.ToUpper().StartsWith("BETWEEN_")) { string[] array = obj.ToString().Split("|"); sb.Append($" {prop.Name.Replace("GT_Eq_", "")} BETWEEN {array.FirstOrDefault()} AND {array.LastOrDefault()} AND"); } else sb.Append($" {prop.Name}={obj} AND "); } } string sql = sb.ToString().ToUpper().TrimEnd("AND ".ToCharArray()); return SqlQuery<T>(sql, null).ToList(); } public DataTable SqlQueryReturnDataTable(string sql, IDbDataParameter[] parameters) { var list = CurrentConnection.Query(sql, parameters); return ConvertExtension.ToDataTable(list); } #endregion Methods }
请原谅我没有把这大段的代码收缩，如果你只是想跑起来看看，请忽略这些代码。后边有下载链接。
/// <summary>
/// 泛型仓储
/// </summary>
/// <typeparam name="T"></typeparam>
public class DapperRepository<T> : IRepository
where T : ModelBase
{
protected readonly DapperDbContextBase _dapperDbContext = null;
protected readonly string _tableName = null;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dbContext"></param>
public DapperRepository(IDbContext dbContext)
{
_dapperDbContext = (DapperDbContextBase)dbContext;
TableAttribute tableCfgInfo = AttributeHelper.Get<T, TableAttribute>();
_tableName = tableCfgInfo != null ? tableCfgInfo.Name.Trim() : typeof(T).Name;
}
/// <summary>
/// 插入实体
/// </summary>
/// <typeparam name="T1"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public bool Create<T1>(T1 entity) where T1 : ModelBase
{
return _dapperDbContext.Create(entity);
}
/// <summary>
/// 插入多个实体
/// </summary>
/// <param name="entities"></param>
/// <returns></returns>
public bool Create(IEnumerable<T> entities)
{
bool result = false;
using (IDbConnection connection = _dapperDbContext.CreateConnection())
{
using (IDbTransaction transaction = connection.BeginTransaction())
{
try
{
foreach (T item in entities)
{
connection.Insert(item, transaction);
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
}
return result;
}
/// <summary>
/// 删除实体
/// </summary>
/// <typeparam name="T1"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public bool Delete<T1>(T1 entity) where T1 : ModelBase
{
return _dapperDbContext.Delete(entity);
}
/// <summary>
/// 删除多个实体
/// </summary>
/// <param name="entities"></param>
/// <returns></returns>
public bool Delete(IEnumerable<T> entities)
{
bool result = false;
using (IDbConnection connection = _dapperDbContext.CreateConnection())
{
using (IDbTransaction transaction = connection.BeginTransaction())
{
try
{
foreach (T item in entities)
{
connection.Delete(item, transaction);
}
transaction.Commit();
result = true;
}
catch (Exception)
{
result = false;
transaction.Rollback();
}
}
}
return result;
}
/// <summary>
/// 检测实体是否存在
/// </summary>
/// <typeparam name="T1"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public bool Exist<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase
{
return _dapperDbContext.Exist(predicate);
}
/// <summary>
/// 用主键ID获取实体
/// </summary>
/// <typeparam name="T1"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public T1 GetByKeyId<T1>(object id) where T1 : ModelBase
{
return _dapperDbContext.GetByKeyId<T1>(id);
}
/// <summary>
/// 根据实体ID获取实体 id可能不是主键
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public T Get(object id)
{
using (IDbConnection connection = _dapperDbContext.CreateConnection())
{
return connection.Get<T>(id);
}
}
/// <summary>
/// 按条件获取实体
/// </summary>
/// <param name="predicate"></param>
/// <returns></returns>
public List<T> Get(Expression<Func<T, bool>> predicate = null)
{
QueryResult queryResult = DynamicQuery.GetDynamicQuery(_tableName, predicate);
using (IDbConnection connection = _dapperDbContext.CreateConnection())
{
return connection.Query<T>(queryResult.Sql, (T)queryResult.Param).ToList();
}
}
/// <summary>
/// 获取符合条件的默认实体
/// </summary>
/// <typeparam name="T1"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public T1 GetFirstOrDefault<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase
{
return _dapperDbContext.GetFirstOrDefault(predicate);
}
/// <summary>
/// 获取符合条件的集合
/// </summary>
/// <typeparam name="T1"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public List<T1> GetList<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase
{
return _dapperDbContext.GetList(predicate);
}
/// <summary>
/// 执行自定义查询
/// </summary>
/// <typeparam name="T1"></typeparam>
/// <param name="predicate"></param>
/// <returns></returns>
public IQueryable<T1> Query<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase
{
return _dapperDbContext.Query(predicate);
}
/// <summary>
/// 修改实体
/// </summary>
/// <typeparam name="T1"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public bool Update<T1>(T1 entity) where T1 : ModelBase
{
return _dapperDbContext.Update(entity);
}
public async Task<bool> DeleteAsync<T1>(T1 entity) where T1 : ModelBase
{
return await _dapperDbContext.DeleteAsync(entity);
}
public async Task<bool> ExistAsync<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase
{
return await _dapperDbContext.ExistAsync(predicate);
}
public async Task<T1> GetByKeyIdAsync<T1>(object id) where T1 : ModelBase
{
return await _dapperDbContext.GetByKeyIdAsync<T1>(id);
}
public async Task<List<T1>> GetListAsync<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase
{
return await _dapperDbContext.GetListAsync(predicate);
}
public async Task<T1> GetFirstOrDefaultAsync<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase
{
return await _dapperDbContext.GetFirstOrDefaultAsync(predicate);
}
public async Task<bool> CreateAsync<T1>(T1 entity) where T1 : ModelBase
{
return await _dapperDbContext.CreateAsync(entity);
}
public async Task<IQueryable<T1>> QueryAsync<T1>(Expression<Func<T1, bool>> predicate = default) where T1 : ModelBase
{
return await _dapperDbContext.QueryAsync(predicate);
}
public async Task<bool> UpdateAsync<T1>(T1 entity) where T1 : ModelBase
{
return await _dapperDbContext.UpdateAsync(entity);
}
public List<T1> GetList<T1>(T1 t) where T1 : FindObjectBase
{
return _dapperDbContext.GetList(t);
}
}
为什么要用Dapper呢？因为我们工厂的MES生产数据库单表有十几亿记录。如果你说这不是大数据，我就无语了。据我自己测试系统里的Redis缓存没有生效的情况下，数据库也能硬抗！
我不相信别人吹上天的某某ORM组件，我只相信自己见过的！
成品截图留念：
收获与感想
- 1、妥妥的吃了次螃蟹，收获了经验
- 2、正在“为自己挖一口井”的路上
- 3、动手写一回持久层，收获良多，终于搞清楚ORM的原理
- 4、源码我是没自信放到github的，后面会加上下载链接
- 5、伙计们分享起来吧，这个生态建设任重而道远啊。
附加一段小广告：
闲着无事看了一下一个开源框架：NetModular，这个框架也是Dapper做的持久层，相比之下我就是班门弄斧了。忏愧，要学习去下载作者源码并且Star吧。在此感谢作者让我享受到阅读优秀代码的快感。谢谢，在此广而告之，聊表谢意！
https://gitee.com/laoli/NetModular
NetModular 是什么
NetModular 是一款为中小型企业而生的基于.Net Core 3.1 开发的业务模块化快速开发解决方案，最终目的是能够让中小团队快速搭建公司内部开发平台。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
"作者：" 数据酷软件工作室
"出处：" http://datacool.cnblogs.com
"专注于CMS(综合赋码系统)，MES，WCS（智能仓储设备控制系统），WMS，商超，桑拿、餐饮、客房、足浴等行业收银系统的开发，15年+从业经验。因为专业，所以出色。"
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
"作者：" 数据酷软件工作室
"出处：" http://datacool.cnblogs.com
"专注于CMS(综合赋码系统)，MES，WCS（智能仓储设备控制系统），WMS，商超，桑拿、餐饮、客房、足浴等行业收银系统的开发，15年+从业经验。因为专业，所以出色。"
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
