数据库实体生成小工具(C#+mysql)
using MySql.Data.MySqlClient; using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DataTableToModel { public class CreateModel { public CreateModel(string tableName, string connectionString) { this.TableName = tableName; this.ConnectionString = connectionString; } /// <summary> /// 表名称 /// </summary> private string TableName { get; set; } /// <summary> /// 数据库链接 /// </summary> private string ConnectionString { get; set; } private string FieldPath = @"D:\\CreateEntityByDB"; //获取所有的数据库名 //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,a.COLUMN_COMMENT comment From INFORMATION_SCHEMA.Columns a LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.TABLE_NAME=b.TABLE_NAME "; //根据表名创建模型 public string CreateSingleModel() { try { string sql = $"{GetTableInfoSql} where a.table_name='{TableName}'"; using (MySqlConnection conn = new MySqlConnection(ConnectionString))//ConnectionString为自己连接字符串 { MySqlCommand sqlCommand = new MySqlCommand(sql, conn); conn.Open(); MySqlDataReader reader = sqlCommand.ExecuteReader(); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append($"public class {TableName} \r\n{{\r\n"); while (reader.Read()) { stringBuilder.Append($"///<summary>\r\n///{reader["comment"].ToString()}\r\n///</summary>\r\n"); stringBuilder.Append($" public {GetTypeOfColumn(reader["typeName"].ToString(), reader["isnullAble"].ToString())} {reader["columnName"]} {{get;set;}}\r\n"); } stringBuilder.Append("} \r\n"); string directory = string.IsNullOrEmpty(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(); } } catch (Exception ex) { return ex.Message; } return "success"; } //根据数据库直接生成所有模型 //public 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 string GetTypeOfColumn(string type, string nullAble) { //新增类型 if (type.Equals("tinyint") && nullAble.Equals("NO")) return "byte"; else if (type.Equals("tinyint") && nullAble.Equals("YES")) return "byte?"; else if (type.Equals("smallint") && nullAble.Equals("NO")) return "int"; else if (type.Equals("smallint") && nullAble.Equals("YES")) return "int?"; else if (type.Equals("bit") && nullAble.Equals("NO")) return "bool"; else 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("无此类型"); } } }