根据数据库表生成Model
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using MySql.Data.MySqlClient; namespace ClassLibrary
{
/// <summary>
/// 生成模型
/// </summary>
public static class GenerativeModel
{
#region 获取服务器所有库 MySQL_GetDBs
/// <summary>
/// 获取服务器所有库
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <returns></returns>
public static List<string> MySQL_GetDBs(string connStr)
{
return GetListString(connStr, "SHOW DATABASES");
}
#endregion #region 获取数据库所有表 MySQL_GetTables
/// <summary>
/// 获取数据库所有表
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <returns></returns>
public static List<string> MySQL_GetTables(string connStr)
{
return GetListString(connStr, "SHOW TABLES");
}
#endregion #region 获取MySQL实体类 MySQL_GetModel
#region 获取MySQL实体类
/// <summary>
/// 获取MySQL实体类
/// </summary>
/// <param name="connStr">连接字符串</param>
/// <param name="tableName">表名</param>
/// <param name="modelName">生成模型名</param>
/// <param name="spaceName">模型命名空间名</param>
/// <returns></returns>
public static string MySQL_GetModel(string connStr, string tableName, string modelName, string spaceName)
{
try
{
List<MySQLTable> ls = MySQLHelp.GetList<MySQLTable>(connStr, "SHOW FULL FIELDS FROM " + tableName);
StringBuilder sb = new StringBuilder();
sb.Append("using System;\nusing System.Collections.Generic;\n");//引用基础类库
sb.AppendFormat("namespace {0}", spaceName).Append("\n{\n");//命名空间
sb.AppendFormat("\tpublic class {0}", modelName).Append("\n\t{\n");//类
foreach (var item in ls)
{
sb.AppendFormat("\t\t///<summary>\n\t\t///{0}\n\t\t///</summary>\n", item.Comment);//注释
string field = item.Field.Substring(, ).ToUpper() + item.Field.Substring();//字段名
sb.AppendFormat("\t\tpublic {0} {1} ", typeConvert(item.Type), field).Append("{get;set;}\n");//添加属性
}
sb.Append("\t}\n}");
return sb.ToString();
}
catch (System.Exception)
{
}
return null;
}
#endregion #region MySQL类型转换成C#类型
private static string typeConvert(string type)
{
type = type.ToLower();
if (type.StartsWith("varchar") || type.StartsWith("char") || type.StartsWith("enum"))
{
return "string";
}
if (type.StartsWith("date") || type.StartsWith("datetime"))
{
return "DateTime";
}
if (type.StartsWith("int") || type.StartsWith("tinyint") || type.StartsWith("smallint") || type.StartsWith("mediumint"))
{
return "int";
};
if (type.StartsWith("bigint"))
{
return "long";
};
if (type.StartsWith("double") || type.StartsWith("float"))
{
return "double";
}
if (type.StartsWith("decimal"))
{
return "decimal";
}
return "[类型]";
}
#endregion #region 表结构
public class MySQLTable
{
/// <summary>
/// 字段名
/// </summary>
public string Field { get; set; }
/// <summary>
/// 字段类型
/// </summary>
public string Type { get; set; }
/// <summary>
///
/// </summary>
public string Collation { get; set; }
/// <summary>
/// 是否可为null
/// </summary>
public string Null { get; set; }
/// <summary>
///
/// </summary>
public string Key { get; set; }
/// <summary>
/// 默认值
/// </summary>
public string Default { get; set; }
/// <summary>
///
/// </summary>
public string Extra { get; set; }
/// <summary>
///
/// </summary>
public string Privileges { get; set; }
/// <summary>
/// 备注
/// </summary>
public string Comment { get; set; } }
#endregion
#endregion #region 获取DataReader
/// <summary>
/// 获取DataReader
/// </summary>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="comText">SQL语句</param>
/// <returns>DataReader</returns>
public static MySqlDataReader ExecuteDataReader(string connStr, string comText)
{
MySqlConnection conn = new MySqlConnection(connStr);
MySqlCommand com = new MySqlCommand();
if (conn.State == ConnectionState.Closed) { conn.Open(); }
com.Connection = conn;
com.CommandType = CommandType.Text;
com.CommandText = comText;
return com.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion #region 获取List<string>
/// <summary>
/// 获取List
/// </summary>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="comText">SQL语句</param>
/// <returns></returns>
private static List<string> GetListString(string connStr, string cmdText)
{
try
{
List<string> ls = new List<string>();
MySqlDataReader read = ExecuteDataReader(connStr, cmdText);
while (read.Read())
{
for (int i = ; i < read.FieldCount; i++)
{
if (read[i] != DBNull.Value)
{
ls.Add(read[i].ToString());
}
}
}
return ls;
}
catch (System.Exception)
{
return null;
}
}
#endregion #region 获取List<T>
/// <summary>
/// 获取List
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="connStr">数据库连接字符串</param>
/// <param name="comText">SQL语句</param>
/// <returns></returns>
public static List<T> GetList<T>(string connStr, string comText) where T : class,new()
{
MySqlDataReader read = ExecuteDataReader(connStr, comText);
List<T> ls = (read.HasRows ? new List<T>() : null);//是否有数据
while (read.Read())
{
Type type = typeof(T);//获取类型
T t = new T();//创建实例
foreach (var item in type.GetProperties())//取出属性
{
for (int i = ; i < read.FieldCount; i++)
{
if (item.Name.ToLower() == read.GetName(i).ToLower() && read[i] != DBNull.Value)//属性名与查询出来的列名比较,且至不能为null
{
item.SetValue(t, read[i], null);
break;
}
}
}
ls.Add(t);
};
return ls;
}
#endregion
}
}