(精华)2020年8月11日 C#基础知识点 数据库自动生成实体类
(精华)2020年8月11日 C#基础知识点 数据库自动生成实体类
数据库相关的sql
//获取所有的数据库名
private static string GetAllDataSql = "SELECT NAME FROM MASTER.DBO.SYSDATABASES ORDER BY NAME";
//获取所有的表名
private static string GetAllTableSql = "SELECT name FROM sys.tables where type ='U'";
//获取所有的表信息
private static string GetTableInfoSql = @"SELECT DISTINCT a.COLUMN_NAME columnName,
a.DATA_TYPE typeName, a.IS_NULLABLE isnullAble
From INFORMATION_SCHEMA.Columns a LEFT JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.TABLE_NAME=b.TABLE_NAME ";
模型相关方法
//根据表名创建模型
public static void CreateSingleModel(string tableName)
{<!-- -->
string sql = $"{GetTableInfoSql} where a.table_name='{tableName}'";
using (SqlConnection conn = new SqlConnection(ConnectionString))//ConnectionString为自己连接字符串
{<!-- -->
SqlCommand sqlCommand = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append($"public class {tableName} \r\n{<!-- -->{\r\n");
while (reader.Read())
{<!-- -->
stringBuilder.Append($" public {GetTypeOfColumn(reader["typeName"].ToString(), reader["isnullAble"].ToString())} {reader["columnName"]} {<!-- -->{get;set;}}\r\n");
}
stringBuilder.Append("} \r\n");
string directory = string.IsNullOrEmpty(StaticConfiguration.FieldPath) ? AppDomain.CurrentDomain.BaseDirectory + "\\Model\\": FieldPath;//FieldPath为自己文件路径
StreamWriter sr;
//是否存在文件夹,不存在则创建
if (!Directory.Exists(directory))
{<!-- -->
Directory.CreateDirectory(directory);
}
string path = directory + "\\" + tableName + ".txt";
//如果该文件存在则追加内容,否则创建文件
if (File.Exists(path))
{<!-- -->
sr = File.AppendText(path);
}
else
{<!-- -->
sr = File.CreateText(path);
}
sr.Write(stringBuilder.ToString());
sr.Flush();
sr.Close();
}
}
//根据数据库直接生成所有模型
public static void BatchCreateModel()
{<!-- -->
using (SqlConnection conn = new SqlConnection(ConnectionString))//ConnectionString为自己连接字符串
{<!-- -->
SqlCommand sqlCommand = new SqlCommand(GetAllTableSql, conn);
conn.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{<!-- -->
CreateSingleModel(reader["name"].ToString());
}
}
}
其他辅助类
//获取列的类型
private static string GetTypeOfColumn(string type, string nullAble)
{<!-- -->
if (type.Equals("int") && nullAble.Equals("NO"))
return "int";
else if (type.Equals("int") && nullAble.Equals("YES"))
return "int?";
else if (type.Equals("bit") && nullAble.Equals("Not"))
return "bool";
else if (type.Equals("bit") && nullAble.Equals("YES"))
return "bool?";
else if ((type.Equals("decimal")|| type.Equals("numeric")|| type.Equals("float") || type.Equals("real")) && nullAble.Equals("NO"))
return "decimal";
else if ((type.Equals("decimal") || type.Equals("numeric") || type.Equals("float") || type.Equals("real")) && nullAble.Equals("Yes"))
return "decimal?";
else if (type.Equals("datetime") && nullAble.Equals("YES"))
return "DateTime?";
else if (type.Equals("datetime") && nullAble.Equals("NO"))
return "DateTime";
else if (type.Equals("nchar") || type.Equals("char") || type.Equals("nvarchar") || type.Equals("varchar") || type.Equals("text"))
return "string";
else throw new Exception("无此类型");
}