MySQL生成模型

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data;
  4 using System.Text;
  5 using MySql.Data.MySqlClient;
  6 
  7 namespace ClassLibrary
  8 {
  9     /// <summary>
 10     /// 生成模型
 11     /// </summary>
 12     public static class GenerativeModel
 13     {
 14         #region 获取服务器所有库 MySQL_GetDBs
 15         /// <summary>
 16         /// 获取服务器所有库
 17         /// </summary>
 18         /// <param name="connStr">连接字符串</param>
 19         /// <returns></returns>
 20         public static List<string> MySQL_GetDBs(string connStr)
 21         {
 22             return GetListString(connStr, "SHOW DATABASES");
 23         }
 24         #endregion
 25 
 26         #region 获取数据库所有表 MySQL_GetTables
 27         /// <summary>
 28         /// 获取数据库所有表
 29         /// </summary>
 30         /// <param name="connStr">连接字符串</param>
 31         /// <returns></returns>
 32         public static List<string> MySQL_GetTables(string connStr)
 33         {
 34             return GetListString(connStr, "SHOW TABLES");
 35         }
 36         #endregion
 37 
 38         #region 获取MySQL实体类 MySQL_GetModel
 39         #region 获取MySQL实体类
 40         /// <summary>
 41         /// 获取MySQL实体类
 42         /// </summary>
 43         /// <param name="connStr">连接字符串</param>
 44         /// <param name="tableName">表名</param>
 45         /// <param name="modelName">生成模型名</param>
 46         /// <param name="spaceName">模型命名空间名</param>
 47         /// <returns></returns>
 48         public static string MySQL_GetModel(string connStr, string tableName, string modelName, string spaceName)
 49         {
 50             try
 51             {
 52                 List<MySQLTable> ls = MySQLHelp.GetList<MySQLTable>(connStr, "SHOW FULL FIELDS FROM " + tableName);
 53                 StringBuilder sb = new StringBuilder();
 54                 sb.Append("using System;\nusing System.Collections.Generic;\n");//引用基础类库
 55                 sb.AppendFormat("namespace {0}", spaceName).Append("\n{\n");//命名空间
 56                 sb.AppendFormat("\tpublic class {0}", modelName).Append("\n\t{\n");//类
 57                 foreach (var item in ls)
 58                 {
 59                     sb.AppendFormat("\t\t///<summary>\n\t\t///{0}\n\t\t///</summary>\n", item.Comment);//注释
 60                     string field = item.Field.Substring(0, 1).ToUpper() + item.Field.Substring(1);//字段名
 61                     sb.AppendFormat("\t\tpublic {0} {1} ", typeConvert(item.Type), field).Append("{get;set;}\n");//添加属性
 62                 }
 63                 sb.Append("\t}\n}");
 64                 return sb.ToString();
 65             }
 66             catch (System.Exception)
 67             {
 68             }
 69             return null;
 70         }
 71         #endregion
 72 
 73         #region MySQL类型转换成C#类型
 74         private static string typeConvert(string type)
 75         {
 76             type = type.ToLower();
 77             if (type.StartsWith("varchar") || type.StartsWith("char") || type.StartsWith("enum"))
 78             {
 79                 return "string";
 80             }
 81             if (type.StartsWith("date") || type.StartsWith("datetime"))
 82             {
 83                 return "DateTime";
 84             }
 85             if (type.StartsWith("int") || type.StartsWith("tinyint") || type.StartsWith("smallint") || type.StartsWith("mediumint"))
 86             {
 87                 return "int";
 88             };
 89             if (type.StartsWith("bigint"))
 90             {
 91                 return "long";
 92             };
 93             if (type.StartsWith("double") || type.StartsWith("float"))
 94             {
 95                 return "double";
 96             }
 97             if (type.StartsWith("decimal"))
 98             {
 99                 return "decimal";
100             }
101             return "[类型]";
102         }
103         #endregion
104 
105         #region 表结构
106         public class MySQLTable
107         {
108             /// <summary>
109             /// 字段名
110             /// </summary>
111             public string Field { get; set; }
112             /// <summary>
113             /// 字段类型
114             /// </summary>
115             public string Type { get; set; }
116             /// <summary>
117             /// 
118             /// </summary>
119             public string Collation { get; set; }
120             /// <summary>
121             /// 是否可为null
122             /// </summary>
123             public string Null { get; set; }
124             /// <summary>
125             /// 
126             /// </summary>
127             public string Key { get; set; }
128             /// <summary>
129             /// 默认值
130             /// </summary>
131             public string Default { get; set; }
132             /// <summary>
133             /// 
134             /// </summary>
135             public string Extra { get; set; }
136             /// <summary>
137             /// 
138             /// </summary>
139             public string Privileges { get; set; }
140             /// <summary>
141             /// 备注
142             /// </summary>
143             public string Comment { get; set; }
144 
145         }
146         #endregion
147         #endregion
148 
149         #region 获取DataReader
150         /// <summary>
151         /// 获取DataReader
152         /// </summary>
153         /// <param name="connStr">数据库连接字符串</param>
154         /// <param name="comText">SQL语句</param>
155         /// <returns>DataReader</returns>
156         public static MySqlDataReader ExecuteDataReader(string connStr, string comText)
157         {
158             MySqlConnection conn = new MySqlConnection(connStr);
159             MySqlCommand com = new MySqlCommand();
160             if (conn.State == ConnectionState.Closed) { conn.Open(); }
161             com.Connection = conn;
162             com.CommandType = CommandType.Text;
163             com.CommandText = comText;
164             return com.ExecuteReader(CommandBehavior.CloseConnection);
165         }
166         #endregion
167 
168         #region 获取List<string>
169         /// <summary>
170         /// 获取List
171         /// </summary>
172         /// <param name="connStr">数据库连接字符串</param>
173         /// <param name="comText">SQL语句</param>
174         /// <returns></returns>
175         private static List<string> GetListString(string connStr, string cmdText)
176         {
177             try
178             {
179                 List<string> ls = new List<string>();
180                 MySqlDataReader read = ExecuteDataReader(connStr, cmdText);
181                 while (read.Read())
182                 {
183                     for (int i = 0; i < read.FieldCount; i++)
184                     {
185                         if (read[i] != DBNull.Value)
186                         {
187                             ls.Add(read[i].ToString());
188                         }
189                     }
190                 }
191                 return ls;
192             }
193             catch (System.Exception)
194             {
195                 return null;
196             }
197         }
198         #endregion
199 
200         #region 获取List<T>
201         /// <summary>
202         /// 获取List
203         /// </summary>
204         /// <typeparam name="T">类型</typeparam>
205         /// <param name="connStr">数据库连接字符串</param>
206         /// <param name="comText">SQL语句</param>
207         /// <returns></returns>
208         public static List<T> GetList<T>(string connStr, string comText) where T : class,new()
209         {
210             MySqlDataReader read = ExecuteDataReader(connStr, comText);
211             List<T> ls = (read.HasRows ? new List<T>() : null);//是否有数据
212             while (read.Read())
213             {
214                 Type type = typeof(T);//获取类型
215                 T t = new T();//创建实例
216                 foreach (var item in type.GetProperties())//取出属性
217                 {
218                     for (int i = 0; i < read.FieldCount; i++)
219                     {
220                         if (item.Name.ToLower() == read.GetName(i).ToLower() && read[i] != DBNull.Value)//属性名与查询出来的列名比较,且至不能为null
221                         {
222                             item.SetValue(t, read[i], null);
223                             break;
224                         }
225                     }
226                 }
227                 ls.Add(t);
228             };
229             return ls;
230         }
231         #endregion
232     }
233 }