42岁大龄程序员的迷茫，看我最新尝鲜.net 5+Dapper搭建的WebAPI框架

合集 - .net core随笔(13)
1.netcore3.0 webapi集成Swagger 5.02.DingTalk钉钉消息推送（.net core 3 WebApi尝鲜记）3..net core 3.1 webapi后端接收钉钉小程序post的文件/图片4.asp.net core 3.1 webapi FileContentResult 5.一次asp.net core3.1打造webapi开发框架的实践6..net core3.1项目在centos7.6上部署经验7.ZR.Admin小改和VUE3版本体验8.我的微服务之路，看我搭建dapr趟过的坑
9.42岁大龄程序员的迷茫，看我最新尝鲜.net 5+Dapper搭建的WebAPI框架
10.Kestrel服务器ASP.NetCore 3.1程序启用SSL11.Asp.Net Core 下 Newtonsoft.Json 转换字符串 null 替换成string.Empty(转)12.ApiResult-WebAPI开发统一返回值对象的演化（.net core版） 13.体验.net core 3.1 socket
收起

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、与钉钉开放平台交互

项目中使用到的包清单：

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<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
复制代码
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#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();
            }
        }
    }
View Code
复制代码

基于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
    }
复制代码

请原谅我没有把这大段的代码收缩，如果你只是想跑起来看看，请忽略这些代码。后边有下载链接。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
/// <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 开发的业务模块化快速开发解决方案，最终目的是能够让中小团队快速搭建公司内部开发平台。

 

posted @   数据酷软件  阅读(4225)  评论(45)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
历史上的今天：
2016-08-21 不在同一程序内，如何在光标处（当前有焦点的窗体输入框）输入字符
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛