天下醉丶的个人博客
  • 首页
  • GPT
  • 随笔
  • 笔记
相顾无相识，长歌怀采薇
  1. 首页
  2. 未分类
  3. 正文

C#基于 Dapper 的SQL 帮助类

2026年2月4日 6点热度 0条评论

声明


using Dapper;
using System.Collections.Concurrent;
using System.ComponentModel.DataAnnotations.Schema;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

基础配置与缓存

        
#region 基础配置与缓存

        /// <summary>
        /// 基础排除名单。
        /// <para>这些属性通常属于分页基类，用于程序控制而非数据库物理列，构建 SQL 时将自动跳过。</para>
        /// <summary>
        public static readonly string[] BaseExcludes = { "pageNumber", "pageSize", "totalCount", "sortField", "sortOrder" };

        /// <summary>
        /// 线程安全的反射属性缓存。
        /// <para>Key: DTO 类型; Value: 属性信息数组。用于降低高频反射带来的性能开销。</para>
        /// <summary>
        private static readonly ConcurrentDictionary _propCache = new();

#endregion

查询

        
#region 查询构建 (SELECT / WHERE / QUERY)

        /// <summary>
        /// 自动根据 DTO 属性生成 SELECT 字段部分。
        /// <para>逻辑：优先识别 [SqlFunction] 生成函数列；其次识别 [Column] 生成物理列；否则使用属性名。</para>
        /// </summary>
        /// <typeparam name="T">DTO 实体类型。</typeparam>
        /// <param name="sbSql">StringBuilder 实例。</param>
        /// <param name="tableAlias">表别名（可选，如 "t"）。</param>
        /// <param name="addNewLine">字段间是否换行（默认 true）。</param>
        /// <param name="excludeFields">手动指定额外排除的属性名（可选）。</param>
        public static void BuildSelect<T>(StringBuilder sbSql, string tableAlias = "", bool addNewLine = true, string[]? excludeFields = null)
        {
            string aliasPrefix = string.IsNullOrWhiteSpace(tableAlias) ? "" : $"{tableAlias}.";
            var props = GetFilteredProperties<T>(excludeFields);

            var fieldList = props.Select(prop =>
            {
                // 1. 获取SqlFunctionAttribute特性
                string? functionSql = GetDynamicAttributeValue(prop, "SqlFunctionAttribute", "FunctionSql");
                if (!string.IsNullOrEmpty(functionSql))
                {
                    return $"{functionSql} AS {prop.Name}";
                }
                // 2. 处理物理映射列
                var colAttr = prop.GetCustomAttribute<ColumnAttribute>();
                string columnName = colAttr?.Name ?? prop.Name;
                return $"{aliasPrefix}{columnName} AS {prop.Name}";
            }).ToList();

            string separator = addNewLine ? ",\n " : ", ";
            sbSql.Append(string.Join(separator, fieldList));
        }


        /// <summary>
        /// 构建参数化 WHERE 子句片段。
        /// <para>功能：自动识别空值跳过、模糊查询 (LIKE)、集合查询 (IN) 及基础相等匹配。</para>
        /// </summary>
        /// <typeparam name="T">参数 DTO 类型。</typeparam>
        /// <param name="paramDto">包含条件的实例。</param>
        /// <param name="tableAlias">表别名（如 "t"）。</param>
        /// <param name="likeFields">需执行模糊查询的属性名数组。</param>
        /// <param name="inFields">需执行 IN 查询的属性名数组（新增）。</param>
        /// <param name="excludeFields">需排除构建条件的属性名数组。</param>
        /// <returns>返回包含参数化 SQL、Dapper 参数对象及还原后的调试 SQL。</returns>
        public static (string Sql, DynamicParameters Params, string DebugSql) BuildWhere<T>(
            T paramDto,
            string tableAlias = "",
            string[]? likeFields = null,
            string[]? inFields = null,  // 新增参数
            string[]? excludeFields = null)
        {
            if (paramDto == null) return (string.Empty, new DynamicParameters(), string.Empty);

            var dynamicParams = new DynamicParameters();
            var sbWhere = new StringBuilder();
            string aliasPrefix = string.IsNullOrWhiteSpace(tableAlias) ? "" : $"{tableAlias}.";
            var props = GetFilteredProperties<T>(excludeFields);

            foreach (var prop in props)
            {
                var val = prop.GetValue(paramDto);
                // 过滤空值及日期类型
                if (val == null || string.IsNullOrWhiteSpace(val.ToString())) continue;
                if (prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(DateTime?)) continue;

                var attr = prop.GetCustomAttribute<ColumnAttribute>();
                string columnName = attr?.Name ?? prop.Name;

                // 1. 优先处理显式指定的 IN 查询
                if (inFields != null && inFields.Contains(prop.Name))
                {
                    // 如果是集合类型
                    if (val is System.Collections.IEnumerable list && !(val is string))
                    {
                        sbWhere.Append($" AND {aliasPrefix}{columnName} IN :{prop.Name}");
                        dynamicParams.Add(prop.Name, val);
                    }
                    // 如果是单个值，也转为 IN 查询
                    else
                    {
                        var items = val.ToString()?
                            .Split(',')
                            .Select(x => x.Trim())
                            .Where(x => !string.IsNullOrEmpty(x))  // 过滤空字符串
                            .ToList() ?? new List<string>();

                        if (!items.Any())
                        {
                            throw new ArgumentException($"字段 {prop.Name} IN 查询的值不能为空");
                        }

                        sbWhere.Append($" AND {aliasPrefix}{columnName} IN :{prop.Name}");
                        dynamicParams.Add(prop.Name, items);
                    }
                }
                // 2. 处理模糊查询
                else if (likeFields != null && likeFields.Contains(prop.Name))
                {
                    sbWhere.Append($" AND {aliasPrefix}{columnName} LIKE '%' || :{prop.Name} || '%'");
                    dynamicParams.Add(prop.Name, val.ToString().Trim());
                }
                // 3. 自动识别集合类型为 IN 查询
                else if (val is System.Collections.IEnumerable enumerable && !(val is string))
                {
                    sbWhere.Append($" AND {aliasPrefix}{columnName} IN :{prop.Name}");
                    dynamicParams.Add(prop.Name, val);
                }
                // 4. 默认相等匹配
                else
                {
                    sbWhere.Append($" AND {aliasPrefix}{columnName} = :{prop.Name}");
                    dynamicParams.Add(prop.Name, val);
                }
            }

            string whereSql = sbWhere.ToString();
            return (whereSql, dynamicParams, ToDebugSql(whereSql, dynamicParams));
        }

        /// <summary>
        /// 构建完整的单表查询 SQL 语句（包含 SELECT、FROM、WHERE）。
        /// </summary>
        /// <typeparam name="T">DTO 类型。</typeparam>
        /// <param name="paramDto">条件实例。</param>
        /// <param name="tableName">物理表名。</param>
        /// <param name="tableAlias">表别名。</param>
        /// <param name="likeFields">模糊查询字段。</param>
        /// <param name="inFields">IN 查询字段。</param>
        /// <param name="excludeFields">排除字段。</param>
        /// <param name="customWhere">手动补充的 SQL 片段（如 "AND STATUS != 9"）。</param>
        /// <returns>返回完整 SQL 结果元组。</returns>
        public static (string Sql, DynamicParameters Params, string DebugSql) BuildQuery<T>(
            T paramDto,
            string tableName,
            string tableAlias = "",
            string[]? likeFields = null,
            string[]? inFields = null,  // 新增参数
            string[]? excludeFields = null,
            string? customWhere = null)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                throw new ArgumentException("表名不能为空", nameof(tableName));
            }
            var sbSql = new StringBuilder("SELECT ");
            BuildSelect<T>(sbSql, tableAlias);
            sbSql.Append($"\nFROM {tableName} {tableAlias}\nWHERE 1=1 ");

            var whereRes = BuildWhere(paramDto, tableAlias, likeFields, inFields, excludeFields);
            string fullSql = sbSql.ToString() + whereRes.Sql;
            string fullDebugSql = sbSql.ToString() + whereRes.DebugSql;

            if (!string.IsNullOrWhiteSpace(customWhere))
            {
                // 只允许 AND 和 OR 开头
                if (!Regex.IsMatch(customWhere.Trim(), @"^(AND|OR)\s", RegexOptions.IgnoreCase))
                {
                    throw new ArgumentException("customWhere 必须以 AND 或 OR 开头");
                }

                // 检查是否包含危险关键字
                var dangerousKeywords = new[] { "DROP", "DELETE", "TRUNCATE", "CREATE", "ALTER" };
                if (dangerousKeywords.Any(kw => customWhere.Contains(kw, StringComparison.OrdinalIgnoreCase)))
                {
                    throw new ArgumentException("customWhere 包含不允许的操作关键字");
                }

                fullSql += "\n" + customWhere.Trim();
            }

            return (fullSql, whereRes.Params, fullDebugSql);
        }

        #endregion

保存

        
#region 保存构建 (INSERT / UPDATE)

        /// <summary>
        /// 构建保存（新增或更新）SQL 语句及参数。
        /// <para>注意：会自动过滤标记了 [SqlFunction] 的属性，使其不参与入库操作。</para>
        /// <para>支持 Oracle 序列：若主键值为 "xxx.NEXTVAL"，则直接拼入 SQL 而不作为参数绑定。</para>
        /// </summary>
        /// <typeparam name="T">模型类型。</typeparam>
        /// <param name="model">待保存的数据对象。</param>
        /// <param name="saveType">保存类型：0-新增 (INSERT), 1-更新 (UPDATE)。</param>
        /// <param name="tableName">目标物理表名。</param>
        /// <param name="pkFields">主键属性名数组（用于 WHERE 条件及序列判定）。</param>
        /// <returns>返回包含 SQL 和 Dapper 参数的元组</returns>
        public static (string Sql, DynamicParameters Params, string DebugSql) BuildSave<T>(
    T model,
    int saveType,
    string tableName,
    string[] pkFields)
        {
            var props = GetFilteredProperties<T>();
            var pkList = pkFields.Select(x => x.ToLower()).ToList();
            var dynamicParams = new DynamicParameters();
            string sql;

            if (saveType == 0) // INSERT 模式
            {
                var sbCols = new StringBuilder();
                var sbVals = new StringBuilder();

                foreach (var prop in props)
                {
                    if (HasAttribute(prop, "SqlFunctionAttribute"))
                        continue;

                    var val = prop.GetValue(model);
                    bool isPk = pkList.Contains(prop.Name.ToLower());

                    if (!isPk && (val == null || (val is string str && string.IsNullOrWhiteSpace(str))))
                        continue;

                    var attr = prop.GetCustomAttribute<ColumnAttribute>();
                    string colName = attr?.Name ?? prop.Name;

                    // 特殊处理：Oracle 序列字符串直接拼接
                    if (isPk && val is string seqStr && seqStr.ToUpper().EndsWith(".NEXTVAL"))
                    {
                        sbCols.Append($"{colName}, ");
                        sbVals.Append($"{seqStr}, ");
                        continue;
                    }

                    sbCols.Append($"{colName}, ");
                    sbVals.Append($":{prop.Name}, ");
                    dynamicParams.Add(prop.Name, val);  // 直接添加原始值
                }

                sql = $"INSERT INTO {tableName} ({sbCols.ToString().TrimEnd(' ', ',')}) " +
                      $"\nVALUES ({sbVals.ToString().TrimEnd(' ', ',')})";
            }
            else // UPDATE 模式
            {
                var sbSets = new StringBuilder();
                var sbWheres = new StringBuilder();

                foreach (var prop in props)
                {
                    if (HasAttribute(prop, "SqlFunctionAttribute"))
                        continue;

                    var val = prop.GetValue(model);
                    var attr = prop.GetCustomAttribute<ColumnAttribute>();
                    string colName = attr?.Name ?? prop.Name;
                    bool isPk = pkList.Contains(prop.Name.ToLower());

                    if (isPk)
                    {
                        if (val == null)
                            throw new InvalidOperationException(
                                $"更新失败：主键字段 '{prop.Name}' 的值不能为空");

                        if (sbWheres.Length > 0) sbWheres.Append(" AND ");
                        sbWheres.Append($"{colName} = :{prop.Name}");
                        dynamicParams.Add(prop.Name, val);
                    }
                    else
                    {
                        if (val == null || (val is string str && string.IsNullOrWhiteSpace(str)))
                            continue;

                        sbSets.Append($"{colName} = :{prop.Name}, ");
                        dynamicParams.Add(prop.Name, val);  // 直接添加原始值
                    }
                }

                if (sbSets.Length == 0)
                    throw new InvalidOperationException("更新失败：没有可更新的有效字段值。");

                if (sbWheres.Length == 0)
                    throw new InvalidOperationException("更新失败：未指定主键字段条件。");

                sql = $"UPDATE {tableName} SET {sbSets.ToString().TrimEnd(' ', ',')} " +
                      $"\nWHERE {sbWheres.ToString()}";
            }

            return (sql, dynamicParams, ToDebugSql(sql, dynamicParams));
        }


        /// <summary>
        /// 构建计数查询 SQL（用于获取总记录数）
        /// </summary>
        /// <typeparam name="T">查询参数 DTO 类型</typeparam>
        /// <param name="paramDto">包含查询条件的实例</param>
        /// <param name="tableName">目标表名</param>
        /// <param name="tableAlias">表别名（可选，如 "t"）</param>
        /// <param name="likeFields">需执行模糊查询的字段名数组（可选）</param>
        /// <param name="excludeFields">需排除的字段名数组（可选）</param>
        /// <param name="customWhere">自定义 WHERE 条件（可选，如 "AND CREATE_TIME > SYSDATE-30"）</param>
        /// <param name="countExpression">自定义计数表达式（可选，默认 "COUNT(1)"，可传入 "COUNT(DISTINCT XXXX)" 等）</param>
        /// <returns>返回包含 SQL 和 Dapper 参数的元组</returns>
        public static (string Sql, DynamicParameters Params, string DebugSql) BuildCountQuery<T>(
            T paramDto,
            string tableName,
            string tableAlias = "",
            string[]? likeFields = null,
            string[]? excludeFields = null,
            string? customWhere = null,
            string countExpression = "COUNT(1)")
        {
            if (string.IsNullOrWhiteSpace(tableName)) throw new ArgumentException("表名不能为空", nameof(tableName));

            if (string.IsNullOrWhiteSpace(countExpression)) countExpression = "COUNT(1)";

            // 构建基础 SELECT COUNT 语句
            var sbSql = new StringBuilder();
            sbSql.Append($"SELECT {countExpression}");
            sbSql.Append($"\nFROM {tableName}");

            // 添加表别名
            if (!string.IsNullOrWhiteSpace(tableAlias))
            {
                sbSql.Append($" {tableAlias}");
            }

            sbSql.Append("\nWHERE 1=1");

            // 构建 WHERE 条件
            var whereRes = BuildWhere(paramDto, tableAlias, likeFields, excludeFields);
            string fullSql = sbSql.ToString() + whereRes.Sql;
            string fullDebugSql = sbSql.ToString() + whereRes.DebugSql;

            // 添加自定义 WHERE 条件
            if (!string.IsNullOrWhiteSpace(customWhere))
            {
                // 只允许 AND 和 OR 开头
                if (!Regex.IsMatch(customWhere.Trim(), @"^(AND|OR)\s", RegexOptions.IgnoreCase))
                {
                    throw new ArgumentException("customWhere 必须以 AND 或 OR 开头");
                }

                // 检查是否包含危险关键字
                var dangerousKeywords = new[] { "DROP", "DELETE", "TRUNCATE", "CREATE", "ALTER" };
                if (dangerousKeywords.Any(kw => customWhere.Contains(kw, StringComparison.OrdinalIgnoreCase)))
                {
                    throw new ArgumentException("customWhere 包含不允许的操作关键字");
                }

                fullSql += "\n" + customWhere.Trim();
            }

            return (fullSql, whereRes.Params, fullDebugSql);
        }

        /// <summary>
        /// 构建删除 SQL 语句及参数。
        /// <para>支持单条记录删除或基于主键的批量删除。</para>
        /// </summary>
        /// <typeparam name="T">模型类型。</typeparam>
        /// <param name="tableName">目标物理表名。</param>
        /// <param name="pkField">主键属性名（如 "poId"）。</param>
        /// <param name="pkValues">主键值列表。若传入多个值，则生成 IN 语句；若为一个值，则生成 = 语句。</param>
        /// <returns>返回包含 SQL 和 Dapper 参数的元组</returns>
        public static (string Sql, DynamicParameters Params, string DebugSql) BuildDelete(
            string tableName,
            string pkField,
            List<string> pkValues)
        {
            #region 1. 参数校验
            if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException(nameof(tableName));
            if (string.IsNullOrEmpty(pkField)) throw new ArgumentNullException(nameof(pkField));
            if (pkValues == null || !pkValues.Any()) throw new InvalidOperationException("删除失败：未指定主键值");


            var dynamicParams = new DynamicParameters();
            string sql;
            string whereCondition;

            #endregion

            #region 2. 构造 WHERE 条件
            if (pkValues.Count == 1)
            {
                // 单条删除：WHERE PK = :PK
                whereCondition = $"{pkField} = :pkValue";
                dynamicParams.Add("pkValue", pkValues[0]);
            }
            else
            {
                // 批量删除：WHERE PK IN (:pkVal0, :pkVal1, ...)
                // Dapper 支持直接传递集合给 IN 子句，但为了生成的 DebugSql 更精确，这里手动处理占位符
                var paramNames = new List<string>();
                for (int i = 0; i < pkValues.Count; i++)
                {
                    string pName = $"pkVal{i}";
                    paramNames.Add($":{pName}");
                    dynamicParams.Add(pName, pkValues[i]);
                }
                whereCondition = $"{pkField} IN ({string.Join(", ", paramNames)})";
            }
            #endregion

            #region 3. 组装 SQL
            sql = $"DELETE FROM {tableName} WHERE {whereCondition}";
            #endregion

            return (sql, dynamicParams, ToDebugSql(sql, dynamicParams));
        }

        /// <summary>
        /// 构建获取 Oracle 序列值的查询 SQL
        /// </summary>
        /// <param name="sequenceName">序列名称（由外部传入）</param>
        /// <returns>返回包含 SQL 和 Dapper 参数的元组</returns>
        public static (string Sql, DynamicParameters Params, string DebugSql) BuildSequenceQuery(string sequenceName)
        {
            if (string.IsNullOrWhiteSpace(sequenceName))
            {
                throw new ArgumentException("序列名不能为空", nameof(sequenceName));
            }

            var sql = $"SELECT {sequenceName}.NEXTVAL FROM DUAL";
            var parameters = new DynamicParameters();
            var debugSql = $"-- 序列号获取\n{sql};";

            return (sql, parameters, debugSql);
        }
        #endregion

内部辅助方法


        #region 内部辅助方法
        /// 
        /// Dapper 参数还原工具。
        /// 逻辑：将 SQL 中的参数占位符（如 :name）替换为实际值的字符串表示，便于开发人员拷贝到数据库工具中直接运行调试。
        /// 
        /// 带占位符的参数化 SQL。
        /// Dapper 的 DynamicParameters 容器。
        /// 替换后的原生预览 SQL。
        public static string ToDebugSql(string sql, DynamicParameters? parameters)
        {
            if (parameters == null || string.IsNullOrEmpty(sql)) return sql;

            string debugSql = sql;
            var paramNames = parameters.ParameterNames.OrderByDescending(x => x.Length);

            foreach (var name in paramNames)
            {
                var pVal = parameters.Get(name);
                string formattedVal = "NULL";

                if (pVal != null)
                {
                    if (pVal is string str)
                        formattedVal = $"'{str.Replace("'", "''")}'"; // SQL 转义
                    else if (pVal is DateTime dt)
                        formattedVal = $"to_date('{dt:yyyy-MM-dd HH:mm:ss}', 'yyyy-mm-dd hh24:mi:ss')"; // Oracle 时间格式
                    else if (pVal is bool b)
                        formattedVal = b ? "1" : "0";
                    else if (pVal is System.Collections.IEnumerable list && !(pVal is string))
                    {
                        var items = new List();
                        foreach (var item in list)
                        {
                            if (item is string sItem) items.Add($"'{sItem.Replace("'", "''")}'");
                            else items.Add(item.ToString());
                        }
                        formattedVal = items.Count > 0 ? string.Join(", ", items) : "NULL";
                    }
                    else
                        formattedVal = pVal.ToString();
                }

                debugSql = Regex.Replace(
                    debugSql,
                    Regex.Escape(":" + name) + @"\b",
                    formattedVal.Replace("$", "$$"),
                    RegexOptions.IgnoreCase
                );
            }
            return debugSql;
        }

        /// 
        /// 获取经筛选后的属性集合。
        /// 过滤项：BaseExcludes、NotMapped 特性、以及调用方指定的 extraExcludes。
        /// 
        private static IEnumerable GetFilteredProperties(string[]? extraExcludes = null)
        {
            var allProps = _propCache.GetOrAdd(typeof(T), t => t.GetProperties());
            return allProps.Where(p =>
                !BaseExcludes.Contains(p.Name) &&
                (extraExcludes == null || !extraExcludes.Contains(p.Name)) &&
                p.GetCustomAttribute() == null);
        }

        /// 
        /// 动态获取指定特性的属性值（解决同名特性在不同命名空间下的冲突问题）。
        /// 
        /// 属性信息。
        /// 特性类名（如 "SqlFunctionAttribute"）。
        /// 特性中的属性名（如 "FunctionSql"）。
        /// 返回属性值，若未匹配到则返回 null。
        private static string? GetDynamicAttributeValue(PropertyInfo prop, string attrName, string propertyName)
        {
            // 1. 匹配特性名称
            var attr = prop.GetCustomAttributes()
                           .FirstOrDefault(a => a.GetType().Name == attrName);

            if (attr == null) return null;

            // 2. 动态读取该特性对象中的属性值
            return attr.GetType().GetProperty(propertyName)?.GetValue(attr)?.ToString();
        }

        /// 
        /// 判断属性是否标注了指定的特性（基于类名字符串匹配）。
        /// 
        private static bool HasAttribute(PropertyInfo prop, string attrName)
        {
            return prop.GetCustomAttributes().Any(a => a.GetType().Name == attrName);
        }

        /// 
        /// Dapper 参数还原工具。
        /// 
        /// 带占位符的参数化 SQL
        /// Dapper 的 DynamicParameters 容器
        /// 
        public static string GetDebugSql(string sql, DynamicParameters? parameters)
        {
            return ToDebugSql(sql, parameters);
        }

        #endregion


特性


    
     ///
    /// 函数控制
    /// 
    [AttributeUsage(AttributeTargets.Property)]
    public class SqlFunctionAttribute : Attribute
    {
        ///
        /// 函数
        /// 
        public string FunctionSql { get; }

        ///
        /// 函数
        /// 
        ///
        public SqlFunctionAttribute(string functionSql)
        {
            FunctionSql = functionSql;
        }
    }


 


 

                        
                        
本作品采用 知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议 进行许可
                        

                            

                                标签：
                                暂无                            

                            

                                最后更新：2026年2月4日
                            

                        

                    
                                

	

		
		
tianxiazui

		
Believe Meet Will Meeting
相信一期一会

	

	

				点赞
	


                

                    
< 上一篇
                

                	

		
文章评论

		

					

		

								

		

							

					

													

								

									

										

											
										

										
									

								

								

									

										

											
										

										
									

								

								

									

										

											
										

										
									

								

							

												

							
							

								

									
									

										

											razz
evil
exclaim
smile
redface
biggrin
eek
confused
idea
lol
mad
twisted
rolleyes
wink
cool
arrow
neutral
cry
mrgreen
drooling
persevering										

									

								

								

																		取消回复									
								

							

						

					

					

														

					

	

            
                    
    



    

        

            

                
            

        

                

            
            

                
            

        

    

    

        

            

                

                                    

                
COPYRIGHT © 2024 天下醉丶. ALL RIGHTS RESERVED.
Theme Kratos Made By Seaton Jiang
琼ICP备19000584号-1