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} \n{{\n";
var tableInfo = freesql.DbFirst.GetTableByName(tablename);
var CSList = ChangeCsType(tableInfo.Columns.OrderBy(x => x.Position).ToList());
foreach (var item in CSList)
{
string feild = "";
if (item.Name.ToLower().Equals("id"))
{
feild += "\t[FreeSql.DataAnnotations.Column(IsPrimary =true, IsIdentity = true)]\n";
}
else
{
feild += $"\t[Column(\"{item.Name}\")]\n";
}
if (!string.IsNullOrWhiteSpace(item.Comment))
{
feild += $"\t[Description(\"{item.Comment}\")]\n";
}
feild += $"\t{item.Property}\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)
{
item.Name = GetFormatName(item.Name);
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;
}
//转化词(首字母大写)
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"}
};
//下划线转大驼峰
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;
}
/// <summary>
/// 输入查询SQL：输出查询SQL结果的插入语法（有字段限定、内容限定）
/// </summary>
/// <param name="freesql"></param>
/// <param name="sql"></param>
/// <param name="tablename"></param>
/// <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();
List<string> ColumnsList = new List<string>();
var FeildCounts = table.Columns.Count;
for (int i = 0; i < table.Columns.Count; i++)
{
ColumnsList.Add(table.Columns[i].ColumnName);
}
for (int i = 0; i < table.Rows.Count; i++)
{
var values = new List<string>();
for (int j = 0; j < FeildCounts; j++)
{
var value = table.Rows[i][j].ToString();
// 对特殊字符进行转义，防止SQL注入或格式错误
values.Add($"'{value.Replace("'", "''")}'");
}
sb.AppendLine($"INSERT INTO {tablename} \n({string.Join(", ", ColumnsList)}) VALUES ({string.Join(", ", values)});");
}
return sb.ToString();
}
catch (Exception ex)
{
return $"出错了:{ex.Message.ToString()}";
}
}
/// <summary>
/// 输入表名，输出Crud语法
/// </summary>
/// <param name="freesql"></param>
/// <param name="tablenamelist"></param>
/// <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($"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();
}
} public static class Logical2
{
//生成实体类的方法
public static string GetEntityGrammar(IFreeSql freesql, List<string> tablenames)
{
string output = "";
foreach (var tablename in tablenames)
{
string result = $"[Table(\"{tablename}\")]\npublic class {ConvertToPascalCase(tablename)} //: DOEntity \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 += $" [Description(\"{item.Coment}\")]\n";
}
if (item.Name.ToLower().Equals("id"))
{
feild += $" [Column(\"{item.Name}\")] //[FreeSql.DataAnnotations.Column(IsPrimary =true,IsIdentity =true)]\n";
feild += $" public {cstype} {item.Name} {{ get; set; }} //主键：Id ({cstype})\n\n";
}
else
{
feild += $" [Column(\"{item.Name}\")]\n";
feild += $" public {cstype} {GetFormatName(item.Name)} {{ get; set; }}\n\n";
}
result += feild;
}
result += "}";
output += result + "\n";
}
return output;
}
private 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;
}
//转化词(首字母大写)
private 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"};
//转化词(全大写)
private static List<string> KeyWordsAllUp = new List<string> { "aqi", "pm", "no", "so", "co", "tsi" };
//最后替换字典(用key替代value)
private static Dictionary<string,string> ReplaceDict = new Dictionary<string, string>()
{
{"Reason","ReaSOn" },{"Update","UpDate" }
};
//下划线转大驼峰
private 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);
}
//首字母大写
static string CapitalizeFirstLetter(string str)
{
if (string.IsNullOrEmpty(str))
return str; // 返回原始字符串，或可以返回空字符串
return char.ToUpper(str[0]) + str.Substring(1);
}
}因篇幅问题不能全部显示，请点此查看更多更全内容
Copyright © 2019- 517ttc.cn 版权所有 赣ICP备2024042791号-8
违法及侵权请联系：TEL:199 18 7713 E-MAIL:2724546146@qq.com
本站由北京市万商天勤律师事务所王兴未律师提供法律服务