Web开发：ORM框架之使用Freesql的DbFrist封装常见工具

一、调用

public class Program
{

    static string connectionstring  = "连接字符串（数据库名）";
    static void Main(string[] args)
    {
        //1.连接数据库
        var freesql = new FreeSqlBuilder()
                    .UseConnectionString(DataType.SqlServer, connectionstring)//连接数据库
                    //.UseMonitorCommand(cmd => Console.Write(cmd.CommandText))//打印SQL
                    .Build();

        //2.数据库=>C#实体
            List<string> tablenames = new List<string> { {"数据表1"} , {"数据表2"} };
            string entitystring = GetEntitystring(freesql,tablenames);
            Console.WriteLine(entitystring);
    }

}

二、基础功能封装

public class CsProperty
{
    /// <summary>
    /// 字段名称（例如Id）
    /// </summary>
    public string Name { get; set; }
    /// <summary>
    /// Cs默认值（例如DateTime.Now true/false）
    /// </summary>
    public string CsDefaultValue { get; set; }
    /// <summary>
    /// 数据库默认值（例如'2027-01-01' 1/0）
    /// </summary>
    public string DefaultValue { get; set; }
    /// <summary>
    /// cs类型（例如int?）
    /// </summary>
    public string CsType { get; set; }
    /// <summary>
    /// 字段注释
    /// </summary>
    public string Comment { get; set; }
    /// <summary>
    /// 字段是否可空
    /// </summary>
    public bool IsNullable { get; set; }
    /// <summary>
    /// 是否主键(不支持Doris)
    /// </summary>
    public bool IsPrimaryKey { get; set; }
    /// <summary>
    /// 是否自增(不支持Doris)
    /// </summary>
    public bool IsIdentity { get; set; }
    /// <summary>
    /// 属性模板（例如 public int Id { get; set; } ）
    /// </summary>
    public string Property { get; set; }
 
}
 
public static class Logical
{
 
    /// <summary>
    /// 生成实体类的方法
    /// </summary>
    public static string GetEntityGrammar(IFreeSql freesql, List<string> tablenames)
    {
        string output = "";
        foreach (var tablename in tablenames)
        {
            var tableInfo = freesql.DbFirst.GetTableByName(tablename);
            var tableComment = tableInfo.Comment;
            var summaryTableComment = string.IsNullOrEmpty(tableComment) ? "" : $"/// <summary>\n///  {tableComment}\n/// </summary>\n";
            string result = $"{summaryTableComment}[Table(\"{tablename}\")]\npublic class {ConvertToPascalCase(tablename)}  \n{{\n\n";
            var CSList = ChangeCsType(tableInfo.Columns.OrderBy(x => x.Position).ToList());
            foreach (var item in CSList)
            {
                string feild = "";
                string itemNameLow = item.Name.ToString().ToLower();
                if (!string.IsNullOrWhiteSpace(item.Comment))
                {
                    feild += $"\t/// <summary>\n\t///  {item.Comment}\n\t/// </summary>\n";
                    feild += $"\t[Description(\"{item.Comment}\")]\n";
                }
                if (item.IsIdentity || itemNameLow.Equals("id"))
                {
                    string addKey = item.IsPrimaryKey ? "IsPrimary = true," : "";
                    feild += $"\t[FreeSql.DataAnnotations.Column({addKey}IsIdentity = true,Name =\"{item.Name}\")]\n";
                }
                else if (!item.IsIdentity && item.IsPrimaryKey)
                {
                    feild += $"\t[FreeSql.DataAnnotations.Column(IsPrimary = true,Name =\"{item.Name}\")]\n";
                }
                else
                {
                    if (itemNameLow.Equals("createtime"))
                    {
                        feild += $"\t[FreeSql.DataAnnotations.Column(ServerTime = DateTimeKind.Local, CanUpdate = false,Name = \"{item.Name}\")]\n";
                    }
                    else if (itemNameLow.Equals("createuser"))
                    {
                        feild += $"\t[FreeSql.DataAnnotations.Column(CanUpdate = false, Name = \"{item.Name}\")]\n";
                    }
                    else if (itemNameLow.Equals("updatetime"))
                    {
                        var isNull = item.IsNullable ? "CanInsert = false," : "";
                        feild += $"\t[FreeSql.DataAnnotations.Column(ServerTime = DateTimeKind.Local,{isNull}Name = \"{item.Name}\")]\n";
                    }
                    else
                    {
                        feild += $"\t[Column(\"{item.Name}\")]\n";
                    }
                }
                feild += $"\tpublic {item.CsType} {GetFormatName(item.Name)} {{ get; set; }}\n\n";
                result += feild;
            }
            result += "}";
            output += result + "\n";
        }
        return output;
    }
 
    /// <summary>
    /// 生成实体类Dto的方法(最后两个入参是前缀和后缀)
    /// </summary>
    public static string GetDtoGrammar(IFreeSql freesql, List<string> tablenames, string prefix = "", string suffix = "Dto")
    {
        string output = "";
        foreach (var tablename in tablenames)
        {
            var tableInfo = freesql.DbFirst.GetTableByName(tablename);
            var tableComment = tableInfo.Comment;
            var summaryTableComment = string.IsNullOrEmpty(tableComment) ? "" : $"/// <summary>\n///  {tableComment}(Dto)\n/// </summary>";
            string result = $"{summaryTableComment}\npublic class {prefix}{ConvertToPascalCase(tablename)}{suffix}  \n{{\n\n";
            var CSList = ChangeCsType(tableInfo.Columns.OrderBy(x => x.Position).ToList());
            foreach (var item in CSList)
            {
                string feild = "";
                string itemNameLow = item.Name.ToString().ToLower();
                if (!string.IsNullOrWhiteSpace(item.Comment))
                {
                    feild += $"\t/// <summary>\n\t///  {item.Comment}\n\t/// </summary>\n";
                }
                feild += $"\tpublic {item.CsType} {GetFormatName(item.Name)} {{ get; set; }}\n\n";
                result += feild;
            }
            result += "}";
            output += result + "\n";
        }
        return output;
    }
 
    /// <summary>
    /// 生成创建实体的方法
    /// </summary>
    public static string GetBuildEntity(IFreeSql freesql, List<string> tablenames)
    {
        if (tablenames.Count == 0)
        {
            return null;
        }
        string output = "";
        StringBuilder sb_en = new StringBuilder();
        StringBuilder sb_lt = new StringBuilder();
        foreach (var tablename in tablenames)
        {
 
            var tableInfo = freesql.DbFirst.GetTableByName(tablename);
            var tablelist = ChangeCsType(tableInfo.Columns.ToList());
            var first = TryGetFirstDbValue(freesql, tablename);
            var showtablename = tablename.Replace("_", "");
            sb_en.Append($"var {showtablename} = new {ConvertToPascalCase(tablename)}\n{{\n");
            sb_lt.Append($"var destictlist = new List<{ConvertToPascalCase(tablename)}>();\n");
            sb_lt.Append($"var sourcelist = new List<{ConvertToPascalCase(tablename)}>();\n");
            sb_lt.Append($"foreach (var item in sourcelist)\n{{\n");
            sb_lt.Append($"\tvar entity = new {ConvertToPascalCase(tablename)}();\n");
            int index = 0;
            foreach (var item in tablelist)
            {
                bool isLast = index == tablelist.Count - 1;
                var value = (first == null ? item.CsDefaultValue : first[item.Name]);
                if (new List<string>() { "int", "decimal", "int?", "decimal?", "double", "double?" }.Contains(item.CsType.ToLower()))
                {
                    value = $"{value}{(item.CsType.ToLower().Contains("decimal") ? "m" : "")}";
                }
                else if (item.CsType.ToLower().Contains("time"))
                {
                    value = $"{(string.IsNullOrEmpty(value) ? "null" : $"DateTime.Parse(\"{value}\")")}";
                }
                else//字符串系列
                {
                    value = $"\"{value}\"";
                }
                item.Name = GetFormatName(item.Name);
                if (isLast)
                {
                    sb_en.Append($"\t{item.Name} = {value}\n");
                }
                else
                {
                    sb_en.Append($"\t{item.Name} = {value},\n");
                }
                sb_lt.Append($"\tentity.{item.Name} = item.{item.Name};\n");
                index++;
            }
            sb_en.Append($"}};\n");
            sb_lt.Append($"\tdestictlist.Add(entity);\n}}");
        }
        output = sb_en.ToString() + "\n\n" + sb_lt.ToString();
        return output;
    }
 
    /// <summary>
    /// 获取这个表的第一条的内容字典(key:value=字段：内容)
    /// </summary>
    /// <returns></returns>
    public static Dictionary<string, string> TryGetFirstDbValue(IFreeSql freeSql, string tablename)
    {
        var res = freeSql.Select<object>().AsTable((old, news) => tablename).Take(1).ToDataTable();
        if (res.Rows.Count > 0)
        {
            //有数据的
 
            // 初始化一个字典，用来存放字段:值
            Dictionary<string, string> dataDict = new Dictionary<string, string>();
            // 取第一行数据
            var dataRow = res.Rows[0];
            // 遍历每一列，列名为键，列值为值
            foreach (DataColumn column in res.Columns)
            {
                dataDict[column.ColumnName] = dataRow[column].ToString();  // 将列名和对应的值放入字典
            }
            return dataDict;
        }
        return null;
    }
 
    /// <summary>
    /// 根据表名获取表的详细信息
    /// </summary>
    public static List<string> GetTableDetails(IFreeSql freesql, List<string> tablenames)
    {
        List<string> result = new List<string>();
 
        foreach (var tablename in tablenames)
        {
            var detail = freesql.DbFirst.GetTableByName(tablename);
            int index = 0;
            List<string> res = new List<string>();
 
            foreach (var item in detail.Indexes)
            {
                index++;
                string indexcontent = "";
                foreach (var jtem in item.Columns)
                {
                    indexcontent += jtem?.Column?.Name + ",";
                }
                res.Add(indexcontent.Trim(','));
            }
 
            // 每个信息单独作为一个 string
            result.Add($"表名称: {detail.Name}");
            result.Add($"表注释: {(string.IsNullOrEmpty(detail?.Comment) ? "无表注释" : detail?.Comment)}");
            result.Add($"表主键: {detail?.Primarys.FirstOrDefault()?.Name ?? "无表主键"}");
            result.Add($"表的自增列: {detail?.Identitys.FirstOrDefault()?.Name ?? "无自增列"}");
            result.Add($"表的字段数: {detail.Columns.Count}");
            result.Add($"表的索引数: {detail.Indexes.Count} ({string.Join(",", res)})");
        }
        return result;
    }
 
    /// <summary>
    /// 随机插入数据
    /// </summary>
    public static string InsertRandomData(IFreeSql freesql, List<string> tablenamelist, int count)
    {
        foreach (var tablename in tablenamelist)
        {
            // 获取表的结构信息
            var tableInfo = freesql.DbFirst.GetTableByName(tablename);
 
            // 构建字段名和占位符
            var fieldNames = string.Join(",", tableInfo.Columns.Where(x => !x.IsIdentity).OrderBy(x => x.Position).Select(c => c.Name));
            var valuesBuilder = new StringBuilder();
 
            // 随机数生成器
            Random random = new Random();
 
            // 执行插入
            for (int i = 0; i < count; i++)
            {
                StringBuilder valueBuilder = new StringBuilder();
 
                foreach (var item in tableInfo.Columns.Where(x => !x.IsIdentity).OrderBy(x => x.Position))
                {
                    var cstype = item.CsType.Name.ToLower();
                    object defaultValue = null;
                    if (cstype.Equals("string"))
                    {
                        defaultValue = item.MaxLength <= 20 ? $"数据{i}" : $"模拟测试数据{i}";
                    }
                    else if (cstype.StartsWith("int") || cstype.StartsWith("decimal"))
                    {
                        defaultValue = random.Next(0, 1000); // 随机整数
                    }
                    else if (cstype.Equals("datetime"))
                    {
                        defaultValue = DateTime.Now.AddDays(random.Next(-30, 0)); // 随机日期
                    }
                    else if (cstype.Equals("boolean"))
                    {
                        defaultValue = random.Next(0, 2) == 1; // 随机布尔值
                    }
                    else
                    {
                        defaultValue = DBNull.Value; // 未处理的其他类型
                    }
 
                    // 处理值为 null 或 DBNull 的情况
                    if (defaultValue == null || defaultValue == DBNull.Value)
                    {
                        valueBuilder.Append("NULL,");
                    }
                    else if (cstype.Equals("string"))
                    {
                        valueBuilder.Append($"'{defaultValue}',");
                    }
                    else if (cstype.Equals("datetime"))
                    {
                        valueBuilder.Append($"'{((DateTime)defaultValue):yyyy-MM-dd HH:mm:ss}',");
                    }
                    else
                    {
                        valueBuilder.Append($"{defaultValue},");
                    }
                }
 
                // 去掉最后一个逗号
                valueBuilder.Length--;
 
                // 构建完整的 SQL 插入语句
                string insertSql = $"INSERT INTO {tablename} ({fieldNames}) VALUES ({valueBuilder})";
 
                // 执行插入操作
                var effectcount = freesql.Ado.ExecuteNonQuery(insertSql);
 
            }
 
        }
        return $"执行插入到表【{string.Join(",", tablenamelist)}】成功！各自插入了【{count}】条数据！";
    }
 
    /// <summary>
    /// 将列转化为Cs类型表达式
    /// </summary>
    public static List<CsProperty> ChangeCsType(List<DbColumnInfo> list)
    {
        List<CsProperty> result = new List<CsProperty>();
        foreach (var item in list.OrderBy(x => x.Position))
        {
            CsProperty en = new CsProperty();
            en.Name = item.Name;
            en.Comment = item.Comment;
            en.CsDefaultValue = item.DefaultValue;
            en.DefaultValue = item.DefaultValue;
            en.IsNullable = item.IsNullable;
            en.IsIdentity = item.IsIdentity;
            en.IsPrimaryKey = item.IsPrimary;
            var cstype = item.CsType.Name.ToString().ToLower();
            var random = new Random();
            var rnum = random.Next(0, 9999);
 
            if (cstype.Equals("string"))
            {
                en.CsDefaultValue = item.MaxLength <= 10 ? $"\"默认值\"" : $"\"默认值数据{rnum}\"";
                en.DefaultValue = "测试";
                en.CsType = "string";
            }
 
            else if (cstype.StartsWith("int"))
            {
                en.CsDefaultValue = rnum.ToString();
                en.DefaultValue = rnum.ToString();
                en.CsType = "int" + $"{(item.IsNullable ? "?" : "")}";
            }
            else if (cstype.StartsWith("decimal"))
            {
                en.CsDefaultValue = rnum.ToString();
                en.DefaultValue = rnum.ToString();
                en.CsType = "decimal" + $"{(item.IsNullable ? "?" : "")}";
            }
            else if (cstype.StartsWith("double") || cstype.StartsWith("single"))
            {
                en.CsDefaultValue = rnum.ToString();
                en.DefaultValue = rnum.ToString();
                en.CsType = "double" + $"{(item.IsNullable ? "?" : "")}";
            }
            else if (cstype.Equals("datetime"))
            {
                en.CsDefaultValue = "DateTime.Now"; //  今天日期
                en.DefaultValue = DateTime.Now.Date.ToString(); //  今天日期
                en.CsType = "DateTime" + $"{(item.IsNullable ? "?" : "")}";
            }
            else if (cstype.Equals("boolean"))
            {
                en.CsDefaultValue = (random.Next(0, 2) == 1).ToString(); // 随机布尔值
                en.DefaultValue = random.Next(0, 1).ToString(); // 随机0 1
                en.CsType = "bool";
            }
            else
            {
                en.CsDefaultValue = "未知";
                en.DefaultValue = "未知";
                en.CsType = item.CsType.Name.ToLower();
            }
            en.Property = $"public {en.CsType} {en.Name} {{ get; set; }}";
            result.Add(en);
        }
        return result;
    }
 
    /// <summary>
    /// 将列转化为Cs类型表达式
    /// </summary>
    public static string ChangeCsType(string data)
    {
        var cstype = data.ToLower();
        if (cstype.Contains("system."))
        {
            cstype = cstype.Split("system.")[1];
        }
        var result = "";
        if (cstype.Equals("string"))
        {
            result = "string";
        }
 
        else if (cstype.StartsWith("int"))
        {
            result = "int";
        }
        else if (cstype.StartsWith("decimal"))
        {
            result = "decimal;";
        }
        else if (cstype.StartsWith("double") || cstype.StartsWith("single"))
        {
            result = "double";
        }
        else if (cstype.Equals("datetime"))
        {
            result = "DateTime";
        }
        else if (cstype.Equals("boolean"))
        {
            result = "bool";
        }
        else
        {
            result = "string";
        }
        return result;
    }


        /// <summary>
        /// 输入查询SQL：输出查询SQL结果的插入语法（有字段限定、内容限定）
        /// </summary>
        /// <returns></returns>
        public static string GetInsertSQLBySQL(IFreeSql freesql, string sql, string tablename = "YourTableName")
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                var table = freesql.Select<object>().WithSql(sql).ToDataTable();
                var nameTypeList = table.Columns.Cast<DataColumn>().Select(x=> new { ColumnName=x.ColumnName, DataType=x.DataType.ToString(), AllowDBNull = x.AllowDBNull }).ToList();
                List<string> FeildNames = nameTypeList.Select(x=>x.ColumnName).ToList();
                List<string> ColumnsList = new List<string>();
                var FeildCounts = table.Columns.Count;
                var DataCounts = table.Rows.Count;
                var Pages = (int)Math.Ceiling((double)DataCounts / 1000);
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    ColumnsList.Add(table.Columns[i].ColumnName);
                }

                // 遍历每一页，批量插入每1000条数据
                for (int page = 0; page < Pages; page++)
                {
                    // 计算当前页的起始和结束行数
                    int startRow = page * 1000;
                    int endRow = Math.Min((page + 1) * 1000, DataCounts);

                    // 构建每一页的INSERT语句
                    var valuesList = new List<string>();
                    for (int i = startRow; i < endRow; i++)
                    {
                        var values = new List<string>();
                        for (int j = 0; j < FeildCounts; j++)
                        {
                            var feildName = FeildNames[j];
                            var feildType = nameTypeList.FirstOrDefault(x=>x.ColumnName==feildName).DataType.Split(".")[1].ToLower();
                            var allowNull = nameTypeList.FirstOrDefault(x => x.ColumnName == feildName).AllowDBNull;
                            var value = table.Rows[i][j].ToString();
                            if (new List<string> { "int32", "int64", "decimal", "double" }.Contains(feildType.ToLower()))
                            {
                                string ins = value;
                                if (string.IsNullOrWhiteSpace(value))
                                {
                                    ins = allowNull ? "NULL" : "0";
                                }
                                values.Add($"{ins}");//两边不加引号,默认值是0
                            }
                            else if (new List<string> { "boolean" }.Contains(feildType.ToLower()))
                            {
                                values.Add($"{(value.ToLower().Equals("true") ? 1 : 0)}");
                            }
                            else
                            {
                                var svalue = table.Rows[i][j];
                                if (svalue == DBNull.Value)
                                {
                                    values.Add($"NULL");
                                }
                                else if(svalue.ToString() == "")
                                {
                                    values.Add($"''");
                                }
                                else
                                {
                                    values.Add($"'{value}'");
                                }
                            }

                        }
                        // 将当前行的值加到valuesList中
                        valuesList.Add($"({string.Join(", ", values)})");
                    }

                    // 生成INSERT语句，并加到StringBuilder中
                    sb.AppendLine($"INSERT INTO {tablename} ({string.Join(", ", ColumnsList)})\nVALUES \n{string.Join(",\n", valuesList)};");
                }


                return sb.ToString();
            }
            catch (Exception ex)
            {
                return $"出错了:{ex.Message.ToString()}";
            }

        }
 
    /// <summary>
    /// 输入表名，输出Crud语法
    /// </summary>
    /// <returns></returns>
    public static string GetCrudByTableName(IFreeSql freesql, List<string> tablenamelist)
    {
        StringBuilder sb = new StringBuilder();
        foreach (var table in tablenamelist)
        {
            var dbinfo = freesql.DbFirst.GetTableByName(table);
            var ColumnsList = dbinfo.Columns.OrderBy(x => x.Position).Select(x => x.Name).ToList();
            var key = dbinfo.Primarys.FirstOrDefault()?.Name ?? "Id";
            sb.AppendLine($"SELECT \n\t{string.Join(",\n\t", ColumnsList)} \nFROM \t{table}\n");
            sb.AppendLine($"SELECT * INTO 新表名 FROM {table}; -- 迁移表结构和数据，仅支持sqlserver\n");
            sb.AppendLine($"INSERT INTO {table} ({string.Join("\n\t,", ColumnsList)}) \nVALUES \n()\n");
            sb.AppendLine($"DELETE FROM {table} WHERE {key} IN ()\n");
            sb.AppendLine($"UPDATE {table} SET xxx=xxx WHERE {key} IN ()\n");
        }
        return sb.ToString();
    }
 
    /// <summary>
    /// 根据查询SQL输出实体（一般用于联表，不要传入ORDERBY）
    /// </summary>
    public static string GetEntityBySQL(IFreeSql freesql, string sql)
    {
        StringBuilder sb = new StringBuilder();
        var dt = freesql.Select<object>().WithSql(sql).ToDataTable();
        sb.AppendLine($"\npublic class Model\n{{\n");
        // 获取并打印每个列的类型
        foreach (DataColumn column in dt.Columns)
        {//ChangeCsType
            sb.AppendLine($"\tpublic {ChangeCsType(column.DataType.Name.ToString())} {column.ColumnName} {{ get; set; }}");
        }
        sb.AppendLine($"\n}}");
        return sb.ToString();
    }
 
    #region 字段格式化
    //转化词(首字母大写)
    public static List<string> KeyWordsUpFirst = new List<string> { "code", "name", "value", "time", "state", "path", "point", "type", "mark", "user", "log", "status", "data", "flag", "pollutant", "url", "date", "speed", "direct", "rank", "order", "district", "town", "city", "key", "hour" };
 
 
    //转化词(全大写)
    public static List<string> KeyWordsAllUp = new List<string> { "aqi", "pm", "no", "so", "co", "tsi" };
 
    //最后替换字典(用key替代value)
    public static Dictionary<string, string> ReplaceDict = new Dictionary<string, string>()
    {
        {"Reason","ReaSOn" },{"Update","UpDate" },{"Normal","NOrmal"},{ "Remark","ReMark"},{ "Json","JSOn"}
    };
 
    //下划线转大驼峰
    public static string ConvertToPascalCase(string input)
    {
        // 分割字符串
        string[] words = input.Split('_');
 
        // 将每个单词的首字母大写并连接
        for (int i = 0; i < words.Length; i++)
        {
            if (words[i].Length > 0)
            {
                words[i] = char.ToUpper(words[i][0]) + words[i].Substring(1);
            }
        }
 
        // 返回连接后的结果
        return string.Join(string.Empty, words);
    }
 
    //首字母大写
    public static string CapitalizeFirstLetter(string str)
    {
        if (string.IsNullOrEmpty(str))
            return str; // 返回原始字符串，或可以返回空字符串
 
        return char.ToUpper(str[0]) + str.Substring(1);
    }
 
    /// <summary>
    /// 格式化字段
    /// </summary>
    /// <param name="name"></param>
    /// <returns></returns>
    public static string GetFormatName(string name)
    {
        string result = name;
        foreach (var item in KeyWordsUpFirst)
        {
            result = result.Replace(item, CapitalizeFirstLetter(item));
        }
        foreach (var item in KeyWordsAllUp)
        {
            result = result.Replace(item, item.ToUpper());
        }
        foreach (var item in ReplaceDict)
        {
            result = result.Replace(item.Value, item.Key);
        }
        result = ConvertToPascalCase(CapitalizeFirstLetter(result));
        return result;
    }
    #endregion
 
}

三、额外功能封装

1.通过Guid判断表关系

#region 求表关系(仅支持Guid类型)
public class TableRelation
{
    public string TableName { get; set; }
    public List<FeildDetail> dList { get; set; } = new List<FeildDetail>();

}
public class FeildDetail
{
    public string FeildName { get; set; }
    public List<string> FeildContent { get; set; }
    public string FeildRelation { get; set; }
}
public static string GetTableRelationShip(IFreeSql freesql, List<string> tablenamelist)
{
    //对比的关系,例如A表-B表
    List<string> compare = new List<string>();
    // 使用两个嵌套循环来生成所有的两两组合
    for (int i = 0; i < tablenamelist.Count; i++)
    {
        for (int j = i + 1; j < tablenamelist.Count; j++)
        {
            compare.Add(tablenamelist[i] + "-" + tablenamelist[j]);
        }
    }

    //表名,字段名称,Guid字段内容,结论
    var resList = new List<TableRelation>();
    foreach (var tableName in tablenamelist)
    {
        string pa1 = tableName;
        var dt = freesql.Select<object>().AsTable((x, y) => tableName).Take(1).ToDataTable();
        var columnList = dt.Columns.Cast<DataColumn>().Where(x => Guid.TryParse(dt.Rows[0][x].ToString(), out var t1) && !new List<string> { "creat", "update" }.Contains(x.ColumnName.ToLower())).Select(x => x.ColumnName).ToList();
        if (!columnList.Any())
        {
            return $"{tableName}未匹配到Guid类型，校验失败！";
        }
        List<FeildDetail> tlist = new List<FeildDetail>();
        foreach (var item in columnList)
        {
            var cName = item;
            dt = freesql.Select<object>().AsTable((x, y) => tableName).OrderBy(cName).Take(1000).ToDataTable();
            List<string> content = DtToDictionaryList(dt).Where(x => x.FieldName.Equals(cName)).Select(x => x.FieldValue).ToList();
            bool isUnique = content.Count == content.Distinct().Count();
            tlist.Add(new FeildDetail
            {
                FeildName = cName,
                FeildContent = content,
                FeildRelation = isUnique ? "一" : "多"
            });
        }
        resList.Add(new TableRelation
        {
            TableName = tableName,
            dList = tlist
        });
    }

    StringBuilder sb = new StringBuilder();
    foreach (var item in compare)
    {
        var left = resList.FirstOrDefault(x => x.TableName.Equals(item.Split('-')[0]));//A表
        var right = resList.FirstOrDefault(x => x.TableName.Equals(item.Split('-')[1]));//B表
        bool flag = false;
        foreach (var j in left.dList)
        {
            foreach (var k in right.dList)
            {
                bool isrealate = j.FeildContent.Intersect(k.FeildContent).Any();//存在交集
                if (isrealate)
                {
                    sb.AppendLine($"{item.Replace("-", "和")}之间的关系是:{j.FeildRelation}对{k.FeildRelation}，通过【{j.FeildName}-{k.FeildName}】关联");
                    flag = true;
                    break;
                }
            }
        }
        if (!flag)
        {
            sb.AppendLine($"{item.Replace("-", "和")}之间的关系是:暂无关联关系");
        }
    }

    sb.AppendLine("————————————————————————————————");
    foreach (var item in resList)
    {
        var r = item.dList.Select(x => x.FeildName + ":" + (x.FeildRelation.Equals("一") ? "主键" : "关联键"));
        sb.AppendLine($"【表名】{item.TableName}【Guid字段和关系】{string.Join(",", r)}");
    }
    return sb.ToString();
}

// 返回每个列名、列类型和列值的元组列表
public static List<(string FieldName, string FieldType, string FieldValue)> DtToDictionaryList(DataTable res)
{
    var list = new List<(string FieldName, string FieldType, string FieldValue)>();

    // 遍历每行数据
    foreach (DataRow row in res.Rows)
    {
        // 遍历每列，获取字段名称、字段类型和字段值
        foreach (DataColumn column in res.Columns)
        {
            var fieldName = column.ColumnName;
            var fieldType = column.DataType.ToString();
            var fieldValue = row[column].ToString();

            // 添加元组到列表
            list.Add((fieldName, fieldType, fieldValue));
        }
    }

    return list;
}
#endregion

2.生成联表字段

/// <summary>
/// 输入表名，输出SELECT .... FROM A LEFT JOIN B ... 的语法
/// 处理字段重复有两种模式，模式1：字段重复则字段后面加字母，模式2：不允许重复，以第一张表出现的为准
/// </summary>
/// <param name="freesql"></param>
/// <param name="tableNames"></param>
/// <param name="notRepeat"></param>
/// <returns></returns>
public static string GetJoinSQLByTableNameList(IFreeSql freesql, List<string> tableNames ,int mode = 1)
{
    int id = 1;
    int id2 = 1;
    var cols = new List<string>();
    var allfeilds = new List<string>();
    StringBuilder sb = new StringBuilder();
    foreach (var item in tableNames)
    {
        var myCols = freesql.DbFirst.GetTableByName(item).Columns.OrderBy(x => x.Position).Select(x => x.Name);
        var myLab = (char)('A' + id - 1);
        foreach (var j in myCols)
        {
            if (cols.Exists(x => x == j))
            {
                if (mode == 2)
                {
                    continue;//不允许重复，就不加了，以第一个出现的为准
                }
                allfeilds.Add($"\t{myLab}.{j} AS {j}{myLab},\n");
            }
            else
            {
                allfeilds.Add($"\t{myLab}.{j} AS {j},\n");
            }
        }
        cols.AddRange(myCols);
        id++;
    }
    sb.Append(@$"SELECT 
{string.Join("", allfeilds).TrimEnd().TrimEnd(',')}
FROM {tableNames[0]} A
");
    foreach (var item in tableNames.Skip(1))
    {
        var myLab = (char)('A' + id2);
        sb.AppendLine(@$"LEFT JOIN {item} {myLab} ON 条件");
        id2++;
    }
    var res = sb.ToString();
    Console.WriteLine(res);
    return res;
}

3.模糊匹配单表全字段

#region 输出全字段模糊查找
public static string GetAllFeildsFuzzySearch(IFreeSql freesql,string tableName,string search)
{
    var allCols = freesql.DbFirst.GetTableByName(tableName).Columns.Select(x=>x.Name).ToList();
    StringBuilder sb = new StringBuilder($"SELECT * FROM {tableName}\n WHERE");
    foreach (var item in allCols)
    {
        var OR = allCols.IndexOf(item)==0 ? "\n" : "OR ";
        sb.AppendLine($"{OR}{item} like '%{search}%'");
    }
    Console.WriteLine(sb.ToString());
    return sb.ToString();
}
#endregion

4.根据Guid的值查找它在哪张表出现过

#region 根据Guid查找它在哪张表出现过
        /// <summary>
        /// 根据Guid查找它在哪张表出现过
        /// </summary>
        /// <param name="freesql"></param>
        /// <param name="guid"></param>
        /// <param name="type">1 sqlserver 2mysql</param>
        /// <returns></returns>
        public static string GetTableByGuidValue(IFreeSql freesql, string guid,int type = 1)
        {
            StringBuilder sb = new StringBuilder();
            int id = 1;
            string sql = @"SELECT DISTINCT A.TABLE_NAME, A.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS A
INNER JOIN INFORMATION_SCHEMA.TABLES B ON A.TABLE_NAME = B.TABLE_NAME
INNER JOIN sys.dm_db_partition_stats P ON OBJECT_ID(A.TABLE_NAME) = P.object_id
WHERE LOWER(A.COLUMN_NAME) LIKE '%id%' 
  AND LOWER(A.DATA_TYPE) LIKE '%char%' 
  AND B.TABLE_TYPE = 'BASE TABLE'
  AND P.index_id <= 1 
  AND P.row_count > 0 ";
            string sqlAdd = type == 1 ? @" OPTION (MAXDOP 1)" : "";//sqlserver查询需要阻止并行
            if (type == 2)
            {
                sql = @"select 
DISTINCT A.TABLE_NAME,A.COLUMN_NAME
from information_schema.columns  A
INNER JOIN (select * from information_schema.tables where TABLE_ROWS !=0 and TABLE_SCHEMA = (SELECT DATABASE()) ) B
ON A.TABLE_NAME = B.TABLE_NAME
where LOWER(column_name) like '%id%' AND LOWER(COLUMN_TYPE) LIKE '%char%'
ORDER BY A.TABLE_NAME";
            }
            var list = freesql.Select<object>().WithSql(sql).ToList<(string TableName,string FeildName)>();
            foreach (var item in list.GroupBy(x=>x.TableName))
            {
                var feilds = item.Select(x=>x.FeildName + $"='{guid}'").ToList();
                var condition = string.Join(" OR ", feilds);
                var check = freesql.Ado.Query<string>($"SELECT COUNT(1) AS TotalCount FROM {item.Key} WHERE {condition}{sqlAdd}").FirstOrDefault() != "0";
                if (check)
                {
                    foreach (var j in item)
                    {
                        var checkssql = $"SELECT COUNT(1) AS TotalCount FROM {item.Key} WHERE {j.FeildName} = '{guid}'";
                        var checkFeilds = freesql.Select<object>().WithSql(checkssql).ToList<string>().FirstOrDefault() != "0";
                        if (checkFeilds)
                        {
                            sb.AppendLine($"{id}.表【{item.Key}】-字段【{j.FeildName}】存在该值，参考SQL【{checkssql.Replace("COUNT(1) AS TotalCount", "*")}】");
                            id++;
                        }
                    }
                }
            }
            var res = sb.ToString();
            res = string.IsNullOrEmpty(res) ? "查找完毕，该数据库中所有表所有名称含id的字段(string类型)都未找到该值！" : res;
            Console.WriteLine(res);
            return res;
        }
        #endregion

5.获取表字段关系

#region 获取表字段关系
public static string GetTableFeildRelations(IFreeSql freesql, string table1,string table2)
{
    var col1 = freesql.DbFirst.GetTableByName(table1).Columns.OrderBy(x=>x.Position).Select(x=>x.Name);
    var col2 = freesql.DbFirst.GetTableByName(table2).Columns.OrderBy(x => x.Position).Select(x => x.Name);
    //共有
    var intersect = col1.Intersect(col2);
    var c1 = col1.Except(col2);
    var c2 = col2.Except(col1);
    StringBuilder sb = new StringBuilder();
    sb.AppendLine($"1.两表共有字段（{intersect.Count()}/{col1.Count()+ col2.Count()}个）\n{string.Join("\n",intersect)}\n");
    sb.AppendLine($"2.【{table1}】表独有字段（{c1.Count()}/{col1.Count()}个）\n{string.Join("\n", c1)}\n");
    sb.AppendLine($"3.【{table2}】表独有字段（{c2.Count()}/{col2.Count()}个）\n{string.Join("\n", c2)}\n");
    var res = sb.ToString();
    Console.WriteLine(res);
    return res;
}
#endregion