mysql所有任务为灰色_MySQL生成模型

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Data;4 usingSystem.Text;5 usingMySql.Data.MySqlClient;6

7 namespaceClassLibrary8 {9 ///

10 ///生成模型11 ///

12 public static classGenerativeModel13 {14 #region 获取服务器所有库 MySQL_GetDBs

15 ///

16 ///获取服务器所有库17 ///

18 /// 连接字符串

19 ///

20 public static List MySQL_GetDBs(stringconnStr)21 {22 return GetListString(connStr, "SHOW DATABASES");23 }24 #endregion

25

26 #region 获取数据库所有表 MySQL_GetTables

27 ///

28 ///获取数据库所有表29 ///

30 /// 连接字符串

31 ///

32 public static List MySQL_GetTables(stringconnStr)33 {34 return GetListString(connStr, "SHOW TABLES");35 }36 #endregion

37

38 #region 获取MySQL实体类 MySQL_GetModel

39 #region 获取MySQL实体类

40 ///

41 ///获取MySQL实体类42 ///

43 /// 连接字符串

44 /// 表名

45 /// 生成模型名

46 /// 模型命名空间名

47 ///

48 public static string MySQL_GetModel(string connStr, string tableName, string modelName, stringspaceName)49 {50 try

51 {52 List ls = MySQLHelp.GetList(connStr, "SHOW FULL FIELDS FROM" +tableName);53 StringBuilder sb = newStringBuilder();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 inls)58 {59 sb.AppendFormat("\t\t///\n\t\t///{0}\n\t\t///\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 returnsb.ToString();65 }66 catch(System.Exception)67 {68 }69 return null;70 }71 #endregion

72

73 #region MySQL类型转换成C#类型

74 private static string typeConvert(stringtype)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 classMySQLTable107 {108 ///

109 ///字段名110 ///

111 public string Field { get; set; }112 ///

113 ///字段类型114 ///

115 public string Type { get; set; }116 ///

117 ///

118 ///

119 public string Collation { get; set; }120 ///

121 ///是否可为null122 ///

123 public string Null { get; set; }124 ///

125 ///

126 ///

127 public string Key { get; set; }128 ///

129 ///默认值130 ///

131 public string Default { get; set; }132 ///

133 ///

134 ///

135 public string Extra { get; set; }136 ///

137 ///

138 ///

139 public string Privileges { get; set; }140 ///

141 ///备注142 ///

143 public string Comment { get; set; }144

145 }146 #endregion

147 #endregion

148

149 #region 获取DataReader

150 ///

151 ///获取DataReader152 ///

153 /// 数据库连接字符串

154 /// SQL语句

155 /// DataReader

156 public static MySqlDataReader ExecuteDataReader(string connStr, stringcomText)157 {158 MySqlConnection conn = newMySqlConnection(connStr);159 MySqlCommand com = newMySqlCommand();160 if (conn.State ==ConnectionState.Closed) { conn.Open(); }161 com.Connection =conn;162 com.CommandType =CommandType.Text;163 com.CommandText =comText;164 returncom.ExecuteReader(CommandBehavior.CloseConnection);165 }166 #endregion

167

168 #region 获取List

169 ///

170 ///获取List171 ///

172 /// 数据库连接字符串

173 /// SQL语句

174 ///

175 private static List GetListString(string connStr, stringcmdText)176 {177 try

178 {179 List ls = new List();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 returnls;192 }193 catch(System.Exception)194 {195 return null;196 }197 }198 #endregion

199

200 #region 获取List

201 ///

202 ///获取List203 ///

204 /// 类型

205 /// 数据库连接字符串

206 /// SQL语句

207 ///

208 public static List GetList(string connStr, string comText) where T : class,new()209 {210 MySqlDataReader read =ExecuteDataReader(connStr, comText);211 List ls = (read.HasRows ? new List() : 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 returnls;230 }231 #endregion

232 }233 }