OLSBackend_Common/JiaZhiQuan.Common/Utils/Extend/BuildSqlExtensions.cs

using System.Collections.Generic;
using System.Text;
using System;
using System.Linq;
using System.Text.RegularExpressions;

namespace JiaZhiQuan.Common.Utils.Extend {
    public static class BuildSqlExtensions {
        /// <summary>
        /// 生成更新sql
        /// </summary>
        /// <param name="source">数据</param>
        /// <param name="tableName">表名</param>
        /// <param name="uniqueFields">唯一字段,多个字段逗号隔开</param>
        /// <param name="cover">字段值为null时是否覆盖，默认覆盖</param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static string BuildBatchUpdatesql(this IEnumerable<object> source, string tableName,
            string uniqueFields, bool cover = true) {
            if (source.Count() == 0) return string.Empty;
            List<string> keyFields = uniqueFields?.Split(',').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
            if (keyFields == null || keyFields.Count() == 0) throw new Exception("唯一字段列表不能为空");
            //考虑怎么写更优雅
            IEnumerable<IDictionary<string, object>> check = source.Where(x => x != null).Select(
                x => (IDictionary<string, object>)ExpandoUtils.ConvertToDynamic(x));
            foreach (var item in check) {
                foreach (var key in keyFields) {
                    if (!item.ContainsKey(key)) throw new Exception("唯一字段不在数据字段中，不能创建更新sql");
                    if (item[key] == null) throw new Exception("唯一字段对应的数据不能为空，不能创建更新sql");
                }
            }
            //根据唯一字段去重数据
            var data = check.Distinct(x => string.Join(",", keyFields.Select(y => x[y])));

            var fields = data.First().Keys;
            var cols = fields.Where(x => !keyFields.Contains(x));
            if (0 == cols.Count()) {
                return string.Empty;
            }
            StringBuilder sb = new StringBuilder("update ");
            sb.Append(tableName).Append(" t inner join ");
            sb.Append(BuildForeachStr(data, " union all ", t => {
                return BuildForeachStr(fields, ",",
                    k => (null == t[k] ? "null" : "'" + EscapeSql(t[k]) + "'") + " " + k,
                    "select ", null);
            }, "(", ")"));
            sb.Append(" a on ");
            sb.Append(BuildForeachStr(keyFields, " and ", t => $"t.{t}=a.{t}"));
            sb.Append(" set ");

            //是否覆盖
            if (cover) {
                sb.Append(BuildForeachStr(cols, ",", t => $"t.{t}=a.{t}"));
            } else {
                sb.Append(BuildForeachStr(cols, ",",
                    t => $"t.{t}=case when a.{t} is null then t.{t} else a.{t} end"));
            }
            sb.Append(";select row_count();");
            return sb.ToString();
        }
        /// <summary>
        /// 生成插入sql，如果数据在库中存在，则去掉，并且会根据keyFields去重数据
        /// </summary>
        /// <param name="source">数据</param>
        /// <param name="tableName">表名</param>
        /// <param name="uniqueFields">唯一字段,多个字段逗号隔开</param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static string BuildBatchInsertsql(this IEnumerable<object> source, string tableName,
            string uniqueFields) {
            if (source.Count() == 0) return string.Empty;
            List<string> keyFields=uniqueFields?.Split(',').Where(x=>!string.IsNullOrWhiteSpace(x)).ToList();
            if (keyFields == null || keyFields.Count() == 0) throw new Exception("唯一字段列表不能为空");
            //考虑怎么写更优雅
            IEnumerable<IDictionary<string, object>> check = source.Where(x => x != null).Select(
                x => (IDictionary<string, object>)ExpandoUtils.ConvertToDynamic(x));
            foreach (var item in check) {
                foreach (var key in keyFields) {
                    if (!item.ContainsKey(key)) throw new Exception("唯一字段不在数据字段中，不能创建插入sql");
                    if (item[key] == null) throw new Exception("唯一字段对应的数据不能为空，不能创建插入sql");
                }
            }
            //根据唯一字段去重数据
            var data = check.Distinct(x => string.Join(",", keyFields.Select(y => x[y])));

            var fields = data.First().Keys;

            StringBuilder sb = new StringBuilder("insert into " + tableName);
            sb.Append(BuildForeachStr(fields, ",", open: "(", close: ")"));
            sb.Append(" select a.* from ").Append(BuildForeachStr(data, " union all ", t => {
                return BuildForeachStr(fields, ",",
                    k => (null == t[k] ? "null" : "'" + EscapeSql(t[k]) + "'") + " " + k,
                    "select ", null);
            }, "(", ")a "));
            sb.Append($" left join {tableName} t on ");
            sb.Append(BuildForeachStr(keyFields, " and ", t => $"t.{t}=a.{t} "));
            sb.Append($"where t.{keyFields.First()} is null ;select row_count();");
            return sb.ToString();
        }
        public static string BuildForeachStr<T>(this IEnumerable<T> list,
            string split, Func<T, string> getInfo = null, string open = null, string close = null) {
            if (list.Count() == 0) {
                return string.Empty;
            }
            StringBuilder sb = new StringBuilder(" ").Append(open ?? "");

            sb.Append(string.Join(split, list.Select(x => getInfo == null ? x.ToString() : getInfo(x))));

            sb.Append(close ?? "").Append(" ");
            return sb.ToString();
        }
        private static string EscapeSql(object value) {
            if (null == value) return null;
            if (value is DateTime time) return time.ToString("yyyy-MM-dd HH:mm:ss");
            else {
                return value.ToString().Replace(@"\", @"\\").Replace(@"'", @"\'");
            }
        }
    }
}