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
{public string Name { get; set; }//字段名称public string DefaultValue { get; set; }//默认值,例如"123"（string带双引号的），123（int decimal bool不带引号的）public string CsType { get; set; }//cs类型，形如int?public string Comment { get; set; }//注释public string Property { get; set; }//属性模板，形如public int Id { get; set; }}
public static class Logical
{// 表字段注释(只支持单表)public static string GetTableComment(IFreeSql fsql, string tablename, string filed){var tableinfo = fsql.DbFirst.GetTableByName(tablename);var tablelist = ChangeCsType(tableinfo.Columns.ToList());var tablecomment = tableinfo.Comment;StringBuilder sb = new StringBuilder();sb.Append($"【表({tablename})】注释：{tablecomment}\n");sb.Append($"【字段】注释：\n");foreach (var item in tablelist){sb.Append($"【{item.Name}({item.CsType})】{item.Comment}\n");}string CommonSQL = $@"--添加表注释
EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'你的注释语', 
@level0type = N'SCHEMA', @level0name = N'dbo', 
@level1type = N'TABLE',  @level1name = N'{tablename}', 
@level2type = N'COLUMN', @level2name = N'{filed}';
--修改表注释
EXEC sys.sp_updateextendedproperty 
@name = N'MS_Description', 
@value = N'新的注释语', 
@level0type = N'SCHEMA', @level0name = N'dbo', 
@level1type = N'TABLE',  @level1name = N'{tablename}', 
@level2type = N'COLUMN', @level2name = N'{filed}';";sb.Append($"\n\n{CommonSQL}");return sb.ToString();}//生成实体类的方法public static string GetEntityGrammar(IFreeSql freesql, List<string> tablenames){string output = "";foreach (var tablename in tablenames){string result = $"[Table(\"{tablename}\")]\npublic class {tablename} : AuditedAggregateRoot<int> \n{{\n";var tableInfo = freesql.DbFirst.GetTableByName(tablename);foreach (var item in tableInfo.Columns.OrderBy(x => x.Position)){var cstype = item.CsType.Name.ToLower();//获取类型if (cstype.StartsWith("int")){cstype = "int";}else if (cstype.Equals("datetime")){cstype = "DateTime";}else if (cstype.Equals("boolean")){cstype = "bool";}//获取是否可为空if (!cstype.Equals("string") && item.IsNullable){cstype += "?";}//获取注释，套入属性模板string feild = "";if (!string.IsNullOrEmpty(item.Comment)){feild += $"    /// <summary>\n    ///  {item.Comment}\n    /// </summary>\n";}feild += $"    public {cstype} {item.Name} {{ get; set; }}\n";result += feild;}result += "}";output += result + "\n";}return output;}//生成创建实体的方法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());sb_en.Append($"var {tablename.ToLower()} = new {tablename}\n{{\n");sb_lt.Append($"List<{tablename}> destictlist = new List<{tablename}>();\n");sb_lt.Append($"foreach (var item in sourcelist)\n{{\n");sb_lt.Append($"\tvar entity = new {tablename}();\n");int index = 0;foreach (var item in tablelist){bool isLast = index == tablelist.Count - 1;if (isLast){sb_en.Append($"\t{item.Name} = {item.DefaultValue}\n");}else{sb_en.Append($"\t{item.Name} = {item.DefaultValue},\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;}//生成实体字段分类的方法public static string GetEntityFeildsClassify(IFreeSql freesql, List<string> tablenames){string output = "";foreach (var tablename in tablenames){var tableColumns = freesql.DbFirst.GetTableByName(tablename)?.Columns.OrderBy(x => x.Position);//item.CsType.Name.ToLower();var grouplist = tableColumns?.GroupBy(x => x.CsType.Name.ToLower()).Select(list => (Key: list.Key.ToString(), List: list.Select(x => x.Name)));output += $"【{tablename}全部字段】\n";output += $"{string.Join("\n", tableColumns.Select(x => x.Name))}\n";foreach (var item in grouplist){output += $"【{item.Key}】\n";output += $"{string.Join("\n", item.List)}\n";}output += "\n";}return output;}//根据表名获取表的详细信息public static string GetTableDetails(IFreeSql freesql, List<string> tablenames){StringBuilder result = new StringBuilder();foreach (var tablename in tablenames){var detail = freesql.DbFirst.GetTableByName(tablename);result.Append($"表名称:{detail.Name}\n");result.Append($"表注释:{(string.IsNullOrEmpty(detail?.Comment) ? "无表注释" : detail?.Comment)}\n");result.Append($"表主键:{detail?.Primarys.FirstOrDefault()?.Name ?? "无表主键"}\n");result.Append($"表的自增列:{detail?.Identitys.FirstOrDefault()?.Name ?? "无自增列"}\n");result.Append($"表的字段数:{detail.Columns.Count}\n");result.Append($"表的索引数:{detail.Indexes.Count}\n");int index = 0;foreach (var item in detail.Indexes){index++;result.Append($"索引编号{index}:");string indexcontent = "";foreach (var jtem in item.Columns){indexcontent += jtem?.Column?.Name + ",";}result.Append($"{indexcontent.Trim(',')}\n");}result.Append("\n");}return result.ToString();}//随机插入数据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}】条数据！";}//返回多个表的共有字段public static string GetTablesPublicFeilds(IFreeSql freesql, List<string> tablenamelist){if (tablenamelist.Count == 0 || tablenamelist.Count == 1){return null;}List<string> last = new List<string>();List<DbColumnInfo> storage = new List<DbColumnInfo>();List<DbColumnInfo> dbinfo = new List<DbColumnInfo>();foreach (var item in tablenamelist){dbinfo = freesql.DbFirst.GetTableByName(item).Columns.ToList();var tablefeilds = dbinfo.Select(x => x.Name).ToList();last = last.Count == 0 ? tablefeilds : tablefeilds.Intersect(last).ToList();}storage = dbinfo.Where(x => last.Contains(x.Name)).ToList();var counts = "总数是：" + storage.Count + "\n";var allfeilds = string.Join("\n", last);var changeinfo = ChangeCsType(storage);var showfeilds = string.Join("\n", changeinfo.Select(x => x.Property).ToList());return counts + allfeilds + "\n\n" + showfeilds;}//返回多个表的全部字段（去重复）public static string GetTablesAllFeilds(IFreeSql freesql, List<string> tablenamelist){if (tablenamelist.Count == 0 || tablenamelist.Count == 1){return null;}List<string> last = new List<string>();List<DbColumnInfo> storage = new List<DbColumnInfo>();List<DbColumnInfo> dbinfo = new List<DbColumnInfo>();foreach (var item in tablenamelist){dbinfo = freesql.DbFirst.GetTableByName(item).Columns.ToList();var tablefeilds = dbinfo.Select(x => x.Name).ToList();last.AddRange(tablefeilds);storage.AddRange(dbinfo);}last = last.Distinct().ToList();storage = storage.Distinct().ToList();storage = storage.Where(x => last.Contains(x.Name)).ToList();var counts = "总数是：" + last.Count + "\n";var allfeilds = string.Join("\n", last);var changeinfo = ChangeCsType(storage);var showfeilds = string.Join("\n", changeinfo.Select(x => x.Property).Distinct().ToList());return counts + allfeilds + "\n\n" + showfeilds;}//返回两表的独有字段public static string GetTwoTablesUniqueFeilds(IFreeSql freesql, List<string> tablenamelist){if (tablenamelist.Count != 2){return $"您输入了{tablenamelist.Count}个表，该功能只支持两表查询！";}var aname = tablenamelist[0];var bname = tablenamelist[1];var alist = freesql.DbFirst.GetTableByName(aname).Columns;var blist = freesql.DbFirst.GetTableByName(bname).Columns;var a_feilds = alist.Select(x => x.Name);var b_feilds = blist.Select(x => x.Name);var a_unique_feilds = a_feilds.Except(b_feilds);var b_unique_feilds = b_feilds.Except(a_feilds);var a_unique_cols = alist.Where(x => a_unique_feilds.Contains(x.Name)).ToList();var b_unique_cols = blist.Where(x => b_unique_feilds.Contains(x.Name)).ToList();var aUniqueList = ChangeCsType(a_unique_cols);var bUniqueList = ChangeCsType(b_unique_cols);StringBuilder result = new StringBuilder();StringBuilder result2 = new StringBuilder();result.Append($"{aname}的独有字段是:\n");result2.Append($"{aname}的独有字段是:\n");foreach (var item in aUniqueList){result.Append($"{item.Name}({item.CsType})\n");result2.Append(item.Property + "\n");}result.Append($"\n{bname}的独有字段是:\n");result2.Append($"\n{bname}的独有字段是:\n");foreach (var item in bUniqueList){result.Append($"{item.Name}({item.CsType})\n");result2.Append(item.Property + "\n");}string output = result.ToString() + "\n" + result2.ToString();return output;}//将列转化为Cs类型表达式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.DefaultValue = item.DefaultValue;var cstype = item.CsType.Name.ToString().ToLower();var random = new Random();var rnum = random.Next(0, 9999);if (cstype.Equals("string")){en.DefaultValue = item.MaxLength <= 10 ? $"\"默认值\"" : $"\"默认值数据{rnum}\"";en.CsType = "string";}else if (cstype.StartsWith("int")){en.DefaultValue = rnum.ToString();en.CsType = "int" + $"{(item.IsNullable ? "?" : "")}";}else if (cstype.StartsWith("decimal")){en.DefaultValue = rnum.ToString();en.CsType = "decimal" + $"{(item.IsNullable ? "?" : "")}";}else if (cstype.Equals("datetime")){en.DefaultValue = "DateTime.Now"; //  今天日期en.CsType = "DateTime" + $"{(item.IsNullable ? "?" : "")}";}else if (cstype.Equals("boolean")){en.DefaultValue = (random.Next(0, 2) == 1).ToString(); // 随机布尔值en.CsType = "bool";}else{en.DefaultValue = "未知";en.CsType = item.CsType.Name.ToLower();}en.Property = $"public {en.CsType} {en.Name} {{ get; set; }}";result.Add(en);}return result;}
}