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

摘要：

42岁大龄程序员的迷茫我真傻,真的。比较重要，最近用.net5+Dapper搭建了一个WebAPI的开发框架，今天分享给大伙。几年前有一篇类似的文章大伙可以回顾一下：一次asp.netcore3.1打造webapi开发框架的实践开始showyouthecode实践技术看点1、Swagger管理API说明文档2、JwtBearertoken验证3、SwaggerUI增加Authentication4、Dapper实现的Repository5、在.net5下使用Log4net6、与钉钉开放平台交互项目中使用到的包清单：关键代码展示：1）StartUP这个只贴图吧，教程大伙都看吐了，司空见惯：用的是标准的套路熟悉的代码。

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

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>

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

关键代码展示：

1）StartUP

这个只贴图吧，教程大伙都看吐了，司空见惯：

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

用的是标准的套路熟悉的代码。

     #region JWTservices.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 = newTokenValidationParameters
                {
                    ValidateIssuerSigningKey = true,
                    IssuerSigningKey = newSymmetricSecurityKey(Encoding.ASCII.GetBytes(token.Secret)),
                    ValidIssuer =token.Issuer,
                    ValidAudience =token.Audience,
                    ValidateIssuer = false,
                    ValidateAudience = false};
            });
            #endregion

 #region Swaggerservices.AddSwaggerGen(c =>{
                c.SwaggerDoc("v1",
                    newOpenApiInfo
                    {
                        Title = "TSP车载MES接口文档",
                        Version = "v1",
                        Contact = newOpenApiContact
                        {
                            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",
                    newOpenApiSecurityScheme
                    {
                        Description = "请输入OAuth接口返回的Token，前置Bearer。示例：Bearer {Roken}",
                        Name = "Authorization",
                        In =ParameterLocation.Header,
                        Type =SecuritySchemeType.ApiKey
                    });
                c.AddSecurityRequirement(newOpenApiSecurityRequirement
                {
                   {
                        newOpenApiSecurityScheme
                        {
                            Reference = newOpenApiReference()
                            {
                                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 classDynamicQuery
    {
        public static string GetUpdateQuery(string tableName, dynamicitem)
        {
            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, dynamicitem)
        {
            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, refqueryProperties);
            }
            catch(Exception)
            {
                WalkTree(expression.Body, ExpressionType.Default, refqueryProperties);
            }
            IDictionary<string, object> expando = newExpandoObject();
            StringBuilder builder = newStringBuilder();
            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 newQueryResult(builder.ToString().TrimEnd(), expando);
        }

        private static voidWalkTree(Expression body, ExpressionType linkingType,
                             ref List<QueryParameter>queryProperties)
        {
            if (body isBinaryExpression)
            {
                var body2 = body asBinaryExpression;
                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(newQueryParameter(link, propertyName, propertyValue, opr));
                }
                else{
                    WalkTree(body2.Left, body.NodeType, refqueryProperties);
                    WalkTree(body2.Right, body.NodeType, refqueryProperties);
                }
            }
            if (body isMethodCallExpression)
            {
                var body2 = body asMethodCallExpression;
                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"));
                    elsequeryProperties.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 objectGetPropertyValue(Expression source)
        {
            ConstantExpression constantExpression = source asConstantExpression;
            if (constantExpression != null)
            {
                returnconstantExpression.Value;
            }
            Expression<Func<object>> evalExpr = Expression.Lambda<Func<object>>(Expression.Convert(source, typeof(object)));
            Func<object> evalFunc =evalExpr.Compile();
            object value =evalFunc();
            returnvalue;
        }

        private static stringGetPropertyName(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);
            }
            returnpropertyName;
        }

        private static stringGetOperator(ExpressionType type)
        {
            switch(type)
            {
                caseExpressionType.Equal:
                    return "=";

                caseExpressionType.NotEqual:
                    return "!=";

                caseExpressionType.LessThan:
                    return "<";

                caseExpressionType.GreaterThan:
                    return ">";

                caseExpressionType.AndAlso:
                caseExpressionType.And:
                    return "AND";

                caseExpressionType.Or:
                caseExpressionType.OrElse:
                    return "OR";

                caseExpressionType.Default:
                    return string.Empty;

                caseExpressionType.GreaterThanOrEqual:
                    return ">=";

                caseExpressionType.LessThanOrEqual:

                    return "<=";

                default:
                    throw newNotImplementedException();
            }
        }
    }

View Code

基于Dapper的DbContext :DapperDbContextBase

  public abstract classDapperDbContextBase : IDbContext
    {
        #region Constructors

        /// <summary>
        ///构造函数
        /// </summary>
        /// <param name="connectString">连接字符串</param>
        protected DapperDbContextBase(stringconnectString)
        {
            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 stringConnectString;

        #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 voidCommit()
        {
            if(TransactionEnabled)
                try{
                    CurrentTransaction.Commit();
                }
                catch(Exception ex)
                {
                    if (ex.InnerException?.InnerException isSqlException 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)
            whereT : 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)
            whereT : ModelBase
        {
            var result = awaitCurrentConnection.InsertAsync(
                new List<T>{
                    entity
                }, CurrentTransaction);
            bool b = result > 0;
            returnb;
        }

        /// <summary>
        ///创建数据库连接IDbConnection
        /// </summary>
        /// <returns></returns>
        public abstractIDbConnection CreateConnection();

        /// <summary>
        ///删除记录
        /// </summary>
        /// <returns>操作是否成功</returns>
        /// <param name="entity">需要操作的实体类.</param>
        public bool Delete<T>(T entity)
            whereT : ModelBase
        {
            returnCurrentConnection.Delete(entity);
        }
        /// <summary>
        ///异步删除记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync<T>(T entity)
            whereT : ModelBase
        {
            var result = awaitCurrentConnection.DeleteAsync(entity);
            returnresult;
        }
        /// <summary>
        ///条件判断是否存在
        /// </summary>
        /// <returns>是否存在</returns>
        /// <param name="predicate">判断条件委托</param>
        public bool Exist<T>(Expression<Func<T, bool>> predicate = null)
            whereT : 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)
          whereT : 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>(objectid)
            whereT : 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>(objectid)
            whereT : ModelBase
        {
            var result = await CurrentConnection.GetAsync<T>(id, CurrentTransaction);
            returnresult;
        }

        /// <summary>
        ///条件获取记录集合
        /// </summary>
        /// <returns>集合</returns>
        /// <param name="predicate">筛选条件.</param>
        public List<T> GetList<T>(Expression<Func<T, bool>> predicate = null)
            whereT : 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)
            whereT : ModelBase
        {
            var tableName = GetTableName<T>();
            var queryResult =DynamicQuery.GetDynamicQuery(tableName, predicate);
            var result = await CurrentConnection.QueryAsync<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction);
            returnresult.ToList();
        }

        /// <summary>
        ///条件获取记录第一条或者默认
        /// </summary>
        /// <returns>记录</returns>
        /// <param name="predicate">筛选条件.</param>
        public T GetFirstOrDefault<T>(Expression<Func<T, bool>> predicate = null)
            whereT : 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)
            whereT : ModelBase
        {
            var tableName = GetTableName<T>();
            var queryResult =DynamicQuery.GetDynamicQuery(tableName, predicate);
            var entity = await CurrentConnection.QueryFirstOrDefaultAsync<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction);
            returnentity;
        }

        /// <summary>
        ///条件查询
        /// </summary>
        /// <returns>IQueryable</returns>
        /// <param name="predicate">筛选条件.</param>
        public IQueryable<T> Query<T>(Expression<Func<T, bool>> predicate = null)
            whereT : ModelBase
        {
            var tableName = GetTableName<T>();
            var queryResult =DynamicQuery.GetDynamicQuery(tableName, predicate);
            var result = CurrentConnection.Query<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction).ToList();
            returnresult.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)
            whereT : ModelBase
        {
            var tableName = GetTableName<T>();
            var queryResult =DynamicQuery.GetDynamicQuery(tableName, predicate);
            var result = await CurrentConnection.QueryAsync<T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction);
            returnresult.AsQueryable();
        }

        /// <summary>
        ///显式回滚事务，仅在显式开启事务后有用
        /// </summary>
        public voidRollback()
        {
            if(TransactionEnabled) CurrentTransaction.Rollback();
        }

        /// <summary>
        ///执行Sql 脚本查询
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>集合</returns>
        public IEnumerable<T> SqlQuery<T>(stringsql, 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>(stringsql, IDbDataParameter[] parameters)
        {
            var dataParameters =CreateParameter(parameters);
            var list = await CurrentConnection.QueryAsync<T>(sql, dataParameters, CurrentTransaction);
            returnlist;
        }

        /// <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, intpageSize)
        {
            var dataParameters =CreateParameter(parameters);
            var result = CurrentConnection.Query<T>(sql, dataParameters, CurrentTransaction);
            returnPageHelper.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 = newStringBuilder();
            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();
            returndataQuery;
        }

        /// <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 = newStringBuilder();
            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 inprops)
            {
                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");
                    }
                    elsesb.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();
            returndataQuery;
        }
        /// <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 inprops)
            {
                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();
            returndataQuery;
        }

        /// <summary>
        ///自定义查询返回DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable SqlQueryReturnDataTable<T>(stringsql, IDbDataParameter[] parameters)
        {
            var dataParameters =CreateParameter(parameters);
            var list = CurrentConnection.Query<T>(sql, dataParameters, CurrentTransaction).ToList();
            returnConvertExtension.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 = newStringBuilder();
            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();
            returnConvertExtension.ToDataTable(list);
        }

        /// <summary>
        ///更新实体类记录
        /// </summary>
        /// <returns>操作是否成功.</returns>
        /// <param name="entity">实体类记录.</param>
        public bool Update<T>(T entity)
            whereT : ModelBase
        {
            returnCurrentConnection.Update(entity, CurrentTransaction);
        }

        /// <summary>
        ///更新实体类记录(异步)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<bool> UpdateAsync<T>(T entity)
            whereT : ModelBase
        {
            return awaitCurrentConnection.UpdateAsync(entity, CurrentTransaction);
        }

        /// <summary>
        ///构建Sql Parameter
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        privateDapperParameter CreateParameter(IDbDataParameter[] parameters)
        {
            if (!(parameters?.Any() ?? false)) return null;

            var dataParameters = newDapperParameter();
            foreach (var parameter inparameters) dataParameters.Add(parameter);
            returndataParameters;
        }

        /// <summary>
        ///获取实体的TableName
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private string GetTableName<T>()
            whereT : 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>()
            whereT : FindObjectBase
        { 
            var tableCfgInfo = AttributeHelper.Get<T,TableAttribute>();
            return tableCfgInfo != null ? tableCfgInfo.Name.Trim() : typeof(T).Name;
        }


        /// <summary>
        ///执行与释放或重置非托管资源关联的应用程序定义的任务。
        /// </summary>
        public voidDispose()
        {
            if (CurrentTransaction != null)
            {
                CurrentTransaction.Dispose();
                CurrentTransaction = null;
            }

            CurrentConnection?.Dispose();
        }

        public List<T> GetList<T>(T t) whereT : FindObjectBase
        {
            string tableName = GetQueryTableName<T>();
            StringBuilder sb = newStringBuilder();
            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 inprops)
            {
                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");
                    }
                    elsesb.Append($"{prop.Name}={obj} AND ");
                }
            }
            string sql = sb.ToString().ToUpper().TrimEnd("AND ".ToCharArray()); 
            return SqlQuery<T>(sql, null).ToList();
        }

        public DataTable SqlQueryReturnDataTable(stringsql, IDbDataParameter[] parameters)
        {
            var list =CurrentConnection.Query(sql, parameters);
            returnConvertExtension.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缓存没有生效的情况下，数据库也能硬抗！

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

我不相信别人吹上天的某某ORM组件，我只相信自己见过的！

成品截图留念：

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

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

收获与感想

1、妥妥的吃了次螃蟹，收获了经验
2、正在“为自己挖一口井”的路上
3、动手写一回持久层，收获良多，终于搞清楚ORM的原理
4、源码我是没自信放到github的，后面会加上下载链接
5、伙计们分享起来吧，这个生态建设任重而道远啊。

下载源码请猛击这里！

附加一段小广告：

闲着无事看了一下一个开源框架：NetModular，这个框架也是Dapper做的持久层，相比之下我就是班门弄斧了。忏愧，要学习去下载作者源码并且Star吧。在此感谢作者让我享受到阅读优秀代码的快感。谢谢，在此广而告之，聊表谢意！

https://gitee.com/laoli/NetModular

NetModular 是什么

NetModular 是一款为中小型企业而生的基于.Net Core 3.1 开发的业务模块化快速开发解决方案，最终目的是能够让中小团队快速搭建公司内部开发平台。

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

42岁大龄程序员的迷茫

开始show you the code

实践技术看点

关键代码展示：

收获与感想

NetModular 是什么

相关文章

Innosetup 脚本写注册表实现自定义协议（Url Protocol）

封装hiredis——C++与redis对接（一）（string的SET与GET操作）

解决Android与服务器交互大容量数据问题

springmvc 整合 netty-socketio

C#学习笔记（五）——函数

如何使用SMOD和CMOD进行SD的用户增强

最新文章

随机推荐

思享工具箱导航

JSON工具

格式化转换

加解密编码

文本数字

网络

站长

计算

其他

对照列表