对于C#面向对象的思想，我们习惯于将数据库中的表创建对应的数据模型；
但假如数据表很多时，我们手动增加模型类会显得很浪费时间；
这个时候有些人会用微软提供的EntityFrameWork，这个框架很强大，编写代码效率也很高，但很由于性能差，在复杂查询的时候生成的sql脚本效率不是很高，所以有的时候不会去使用它；
这个时候就会有CodeSmith来协助我们去完成那些费时费力的工作：
CodeSmith如何使用，网上也有很详细的介绍了，下面代码只是简单介绍
属性SourceDataBase是连接的数据库，CodeSmith提供连接数据库的方法很方便
属性NameSpace顾名思义就是命名空间
1 <%-- 2 Name:批量生成实体类 3 Author: TitanChen 4 Description:批量将数据库中的表结构生成数据模型 5 --%> 6 <%@ CodeTemplate Language="C#" TargetLanguage="C#" Description="Template description here." %> 7 <%@ Assembly Name="SchemaExplorer" %> 8 <%@ Import Namespace="SchemaExplorer" %> 9 <%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" Category="Context" %> 10 <%@ Property Name="NameSpace" Type="String" Category="参数" Description="命名空间" Default="Blog.Core.Model" Optional="True"%> 11 <%@ Import Namespace="System.Text.RegularExpressions" %> 12 using System; 13 using System.Collections.Generic; 14 using System.Text; 15 16 namespace <%=NameSpace%> 17 { 18 <% foreach(TableSchema SourceTable in SourceDatabase.Tables) { %> 19 /// <summary> 20 /// <%=GetClassName(SourceTable) +"模型"%> 21 /// </summary> 22 [Serializable] 23 public class <%=GetClassName(SourceTable) %> : BaseModel 24 { 25 /// <summary> 26 /// 表名 27 /// </summary> 28 public static readonly string TableName = "<%=GetClassName(SourceTable) %>"; 29 30 /// <summary> 31 /// 构造函数 32 /// </summary> 33 public <%=GetClassName(SourceTable) %>() : base(TableName) 34 { 35 } 36 37 private Guid Id = Guid.Empty; 38 <% foreach (ColumnSchema column in SourceTable.Columns) {%> 39 /// <summary> 40 /// <%=column.Description %> 41 /// </summary> 42 <% if(column.IsPrimaryKeyMember){ %> 43 public Guid <%= GetPascalName(column) %> 44 { 45 get{ return Id;} 46 set 47 { 48 Id = value; 49 if (value != null) 50 { 51 base.BaseId = value; 52 } 53 } 54 } 55 56 <% }else{ %> 57 public <%=GetCSharpVariableType(column) %> <%=GetPascalName(column) %> { get; set; } 58 59 <% } %> 60 <% }%> 61 } 62 63 /// <summary> 64 /// <%=GetClassName(SourceTable) +"数据模型"%> 65 /// </summary> 66 [Serializable] 67 public class <%=GetClassName(SourceTable)+"ListData" %> 68 { 69 /// <summary> 70 /// 总记录数 71 /// </summary> 72 public int RecordCount { get; set; } 73 74 /// <summary> 75 /// 数据列表 76 /// </summary> 77 public List<<%=GetClassName(SourceTable)+"ListModel" %>> RecordList { get; set; } 78 } 79 80 /// <summary> 81 /// <%=GetClassName(SourceTable) +"列表模型"%> 82 /// </summary> 83 [Serializable] 84 public class <%=GetClassName(SourceTable)+"ListModel" %> 85 { 86 <% foreach (ColumnSchema column in SourceTable.Columns) {%> 87 <%if(new string[]{"IsDeleted"}.Contains(column.Name)){continue;} %> 88 /// <summary> 89 /// <%=column.Description %> 90 /// </summary> 91 public <%=GetCSharpVariableType(column)=="Guid" || GetCSharpVariableType(column)=="DateTime"?"string":GetCSharpVariableType(column) %> <%=GetPascalName(column) %> { get; set; } 92 93 <% }%> 94 } 95 <%} %> 96 } 97 <script runat="template"> 98 99 public string MakeSingle(string name) 100 { 101 return name; 102 } 103 public string GetCamelName(ColumnSchema column) 104 { 105 return column.Name.Substring(0, 1).ToLower() + column.Name.Substring(1); 106 } 107 public string GetCamelName(string value) 108 { 109 return value.Substring(0, 1).ToLower() + value.Substring(1); 110 } 111 public string GetPascalName(ColumnSchema column) 112 { 113 return column.Name.Substring(0, 1).ToUpper() + column.Name.Substring(1); 114 } 115 public string GetPascalName(string value) 116 { 117 return value.Substring(0, 1).ToUpper() + value.Substring(1); 118 } 119 public string GetClassName(TableSchema table) 120 { 121 return GetPascalName(MakeSingle(table.Name)); 122 } 123 public string GetForeignKeyColumnType(ColumnSchema column) 124 { 125 return column.Table.ForeignKeys[0].PrimaryKeyTable.Name; 126 } 127 public string GetForeignKeyColumnName(ColumnSchema column) 128 { 129 if(column.Name.Substring(column.Name.Length-2).ToLower() == "id") 130 { 131 return column.Name.Substring(0,column.Name.Length-2); 132 } 133 else 134 { 135 return column.Name; 136 } 137 } 138 public string GetPrimaryKeyType(TableSchema table) 139 { 140 if (table.PrimaryKey != null) 141 { 142 if (table.PrimaryKey.MemberColumns.Count == 1) 143 { 144 return GetCSharpVariableType(table.PrimaryKey.MemberColumns[0]); 145 } 146 else 147 { 148 throw new ApplicationException("This template will not work on primary keys with more than one member column."); 149 } 150 } 151 else 152 { 153 throw new ApplicationException("This template will only work on tables with a primary key."); 154 } 155 } 156 public string GetCSharpVariableType(ColumnSchema column) 157 { 158 if (column.Name.EndsWith("TypeCode")) return column.Name; 159 160 switch (column.DataType) 161 { 162 case DbType.AnsiString: return "string"; 163 case DbType.AnsiStringFixedLength: return "string"; 164 case DbType.Binary: return "byte[]"; 165 case DbType.Boolean: return "bool"; 166 case DbType.Byte: return "byte"; 167 case DbType.Currency: return "decimal"; 168 case DbType.Date: return "DateTime"; 169 case DbType.DateTime: return "DateTime"; 170 case DbType.Decimal: return "decimal"; 171 case DbType.Double: return "double"; 172 case DbType.Guid: return "Guid"; 173 case DbType.Int16: return "short"; 174 case DbType.Int32: return "int"; 175 case DbType.Int64: return "long"; 176 case DbType.Object: return "object"; 177 case DbType.SByte: return "sbyte"; 178 case DbType.Single: return "float"; 179 case DbType.String: return "string"; 180 case DbType.StringFixedLength: return "string"; 181 case DbType.Time: return "TimeSpan"; 182 case DbType.UInt16: return "ushort"; 183 case DbType.UInt32: return "uint"; 184 case DbType.UInt64: return "ulong"; 185 case DbType.VarNumeric: return "decimal"; 186 default: 187 { 188 return "__UNKNOWN__" + column.NativeType; 189 } 190 } 191 } 192 </script>
CodeSmith虽然方便，但是要安装和激活，这个是很麻烦的；而且每次生成都要打开CodeSmith去生成，不是很方便；
于是我就照着原先在CodeSmith上模板写了个控制台应用程序，可以改写配合着bat使用，贼方便
1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.Data.SqlClient; 5 using System.Text; 6 using System.Linq; 7 using System.IO; 8 9 namespace Blog.Core.Test 10 { 11 public class Program 12 { 13 /// <summary> 14 /// 数据库连接字符串 15 /// </summary> 16 private static string _connstr = "Data Source=localhost;Initial Catalog=Test;User Id=sa;Password=123456"; 17 18 /// <summary> 19 /// 主函数 20 /// </summary> 21 /// <param name="args"></param> 22 static void Main(string[] args) 23 { 24 Console.Write("命名空间："); 25 string namespaces = Console.ReadLine(); 26 Console.Write("文件名："); 27 string filename = Console.ReadLine(); 28 Console.WriteLine("开始生成，请等待..."); 29 new Program().Generate(namespaces, filename); 30 Console.WriteLine("生成成功..."); 31 Console.ReadKey(); 32 } 33 34 /// <summary> 35 /// 生成Model文件 36 /// </summary> 37 /// <param name="namespaces"></param> 38 /// <param name="filename"></param> 39 private void Generate(string namespaces, string filename) 40 { 41 byte[] myByte = Encoding.UTF8.GetBytes(BuildTemplete(namespaces)); 42 string filepath = Environment.CurrentDirectory + "\" + filename; 43 if (File.Exists(filepath)) 44 { 45 File.Delete(filepath); 46 } 47 using (FileStream fsWrite = new FileStream(filepath, FileMode.Append)) 48 { 49 fsWrite.Write(myByte, 0, myByte.Length); 50 }; 51 } 52 53 /// <summary> 54 /// 创建模板 55 /// </summary> 56 /// <param name="namespaces"></param> 57 /// <returns></returns> 58 private string BuildTemplete(string namespaces) 59 { 60 StringBuilder templete = new StringBuilder("using System;"); 61 templete.Append("using System.Collections.Generic; "); 62 templete.AppendFormat("namespace {0} {{ ", namespaces); 63 List<TableModel> tables = GetTables(); 64 foreach (var table in tables) 65 { 66 templete.AppendFormat(" #region {0} ", table.name); 67 templete.Append(" /// <summary> "); 68 templete.AppendFormat(" /// {0}模型 ", table.name); 69 templete.Append(" /// </summary> "); 70 templete.Append(" [Serializable] "); 71 templete.AppendFormat(" public class {0} : BaseModel {{", table.name); 72 templete.Append(" "); 73 templete.Append(" /// <summary> "); 74 templete.Append(" /// 表名 "); 75 templete.Append(" /// </summary> "); 76 templete.AppendFormat(" public static readonly string TableName = "{0}"; ", table.name); 77 templete.Append(" "); 78 templete.Append(" /// <summary> "); 79 templete.Append(" /// 构造函数 "); 80 templete.Append(" /// </summary> "); 81 templete.AppendFormat(" public {0}() : base(TableName) {{ }} ", table.name); 82 templete.Append(" private Guid Id = Guid.Empty; "); 83 table.columns.ForEach(columu => 84 { 85 templete.Append(" "); 86 templete.Append(" /// <summary> "); 87 templete.AppendFormat(" /// {0} ", columu.ColComment); 88 templete.Append(" /// </summary> "); 89 if (columu.IsPk) 90 { 91 templete.AppendFormat(" public Guid {0} ", columu.ColName); 92 templete.Append(" { "); 93 templete.Append(" get { return Id; } "); 94 templete.Append(" set "); 95 templete.Append(" { "); 96 templete.Append(" Id = value; "); 97 templete.Append(" if (value != null) "); 98 templete.Append(" { "); 99 templete.Append(" base.BaseId = value; "); 100 templete.Append(" } "); 101 templete.Append(" } "); 102 templete.Append(" } "); 103 } 104 else 105 { 106 templete.AppendFormat(" public {0} {1} {{ get; set; }} {2} ", GetCSType(columu.ColType), columu.ColName, GetCSDefault(columu.ColDefault)); 107 } 108 }); 109 templete.Append(" }"); 110 111 templete.Append(" "); 112 113 templete.Append(" /// <summary> "); 114 templete.AppendFormat(" /// {0}数据模型 ", table.name); 115 templete.Append(" /// </summary> "); 116 templete.Append(" [Serializable] "); 117 templete.AppendFormat(" public class {0}ListData {{", table.name); 118 templete.Append(" "); 119 templete.Append(" /// <summary> "); 120 templete.Append(" /// 总记录数 "); 121 templete.Append(" /// </summary> "); 122 templete.Append(" public int RecordCount { get; set; } "); 123 templete.Append(" /// <summary> "); 124 templete.Append(" "); 125 templete.Append(" /// 数据列表 "); 126 templete.Append(" /// </summary> "); 127 templete.AppendFormat(" public List<{0}ListModel> RecordList {{ get; set; }} ", table.name); 128 templete.Append(" }"); 129 130 templete.Append(" "); 131 132 templete.Append(" /// <summary> "); 133 templete.AppendFormat(" /// {0}列表模型 ", table.name); 134 templete.Append(" /// </summary> "); 135 templete.Append(" [Serializable] "); 136 templete.AppendFormat(" public class {0}ListModel {{", table.name); 137 templete.Append(" "); 138 table.columns.ForEach(columu => 139 { 140 if (columu.ColName != "IsDeleted") 141 { 142 templete.Append(" "); 143 templete.Append(" /// <summary> "); 144 templete.AppendFormat(" /// {0} ", columu.ColComment); 145 templete.Append(" /// </summary> "); 146 if (new string[] { "Guid", "DateTime" }.Contains(GetCSType(columu.ColType))) 147 { 148 templete.AppendFormat(" public string {0} {{ get; set; }} ", columu.ColName); 149 } 150 else 151 { 152 templete.AppendFormat(" public {0} {1} {{ get; set; }} ", GetCSType(columu.ColType), columu.ColName); 153 } 154 } 155 }); 156 templete.Append(" } "); 157 templete.Append(" #endregion "); 158 templete.Append(" "); 159 } 160 templete = templete.Remove(templete.Length - 2, 1); 161 templete.Append("}"); 162 return templete.ToString(); 163 } 164 165 /// <summary> 166 /// 获取表数据 167 /// </summary> 168 /// <returns></returns> 169 private List<TableModel> GetTables() 170 { 171 List<TableModel> tables = new List<TableModel>(); 172 DataTable tabName = Query("SELECT name AS TableName FROM sysobjects WHERE xtype = 'U'"); 173 DataTable colName = Query(@"--获取表名、字段名称、字段类型、字段说明、字段默认值 174 SELECT obj.name AS TableName,--表名 175 col.name AS ColName,--列名 176 typ.name AS ColType,--字段类型 177 cmt.value AS ColComment,--字段说明 178 dft.text AS ColDefault--字段默认值 179 FROM syscolumns col--字段 180 INNER JOIN sysobjects obj--表 181 ON col.id = obj.id 182 AND obj.xtype = 'U'--表示用户表 183 LEFT JOIN systypes typ--类型 184 ON col.xtype = typ.xusertype 185 LEFT JOIN sys.extended_properties cmt--字段说明 186 ON col.id = cmt.major_id--表Id 187 AND col.colid = cmt.minor_id--字段Id 188 LEFT JOIN syscomments dft--默认值 189 ON col.cdefault = dft.id 190 ORDER BY obj.name, 191 col.id ASC 192 "); 193 DataTable pk = Query(@"--获取表的主键字段名 194 SELECT CCU.COLUMN_NAME, 195 TC.TABLE_NAME 196 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 197 INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU 198 ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME 199 WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 200 "); 201 foreach (DataRow row in tabName.Rows) 202 { 203 TableModel table = new TableModel(); 204 table.name = row["TableName"].ToString(); ; 205 table.columns = new List<ColumnModel>(); 206 DataRow[] cols = colName.Select(string.Format("TableName = '{0}'", row["TableName"].ToString())); 207 DataRow[] pks = pk.Select(string.Format("TABLE_NAME = '{0}'", row["TableName"].ToString())); 208 string primarykey = pks == null || pks.Length == 0 ? "" : pks[0]["COLUMN_NAME"].ToString(); 209 foreach (DataRow col in cols) 210 { 211 ColumnModel column = new ColumnModel(); 212 column.IsPk = primarykey == col["ColName"].ToString(); 213 column.ColName = col["ColName"].ToString(); 214 column.ColType = col["ColType"].ToString(); 215 column.ColComment = col["ColComment"].ToString(); 216 column.ColDefault = col["ColDefault"].ToString(); 217 table.columns.Add(column); 218 } 219 tables.Add(table); 220 } 221 return tables; 222 223 } 224 225 /// <summary> 226 /// 简单的SQL查询 227 /// </summary> 228 /// <param name="sqlString"></param> 229 /// <returns></returns> 230 private DataTable Query(string sqlString) 231 { 232 DataTable dt = new DataTable(); 233 using (SqlConnection conn = new SqlConnection(_connstr)) 234 { 235 using (SqlCommand command = conn.CreateCommand()) 236 { 237 command.CommandText = sqlString; 238 SqlDataAdapter adapter = new SqlDataAdapter(); 239 adapter.SelectCommand = command; 240 adapter.Fill(dt); 241 } 242 } 243 return dt; 244 } 245 246 /// <summary> 247 /// 获取C#类型 248 /// </summary> 249 /// <param name="sqlType"></param> 250 /// <returns></returns> 251 private string GetCSType(string sqlType) 252 { 253 switch (sqlType) 254 { 255 case "datetime": 256 return "DateTime"; 257 case "int": 258 return "int"; 259 case "nchar": 260 return "string"; 261 case "nvarchar": 262 return "string"; 263 case "varchar": 264 return "string"; 265 case "text": 266 return "string"; 267 case "ntext": 268 return "string"; 269 case "uniqueidentifier": 270 return "Guid"; 271 case "decimal": 272 return "decimal"; 273 case "float": 274 return "float"; 275 case "bit": 276 return "byte"; 277 case "binary": 278 return "byte []"; 279 case "varbinary": 280 return "byte []"; 281 case "timestamp": 282 return "int"; 283 default: 284 return ""; 285 } 286 } 287 288 /// <summary> 289 /// 获取C#默认值 290 /// </summary> 291 /// <param name="sqlValue"></param> 292 /// <returns></returns> 293 private string GetCSDefault(string sqlValue) 294 { 295 switch (sqlValue) 296 { 297 case "((0))": 298 return "= 0;"; 299 case "('')": 300 return "= string.Empty;"; 301 case "('00000000-0000-0000-0000-000000000000')": 302 return "= Guid.Empty;"; 303 default: 304 return ""; 305 } 306 } 307 } 308 309 /// <summary> 310 /// 表模型 311 /// </summary> 312 public class TableModel 313 { 314 /// <summary> 315 /// 表名 316 /// </summary> 317 public string name { get; set; } 318 319 /// <summary> 320 /// 表字段 321 /// </summary> 322 public List<ColumnModel> columns { get; set; } 323 } 324 325 /// <summary> 326 /// 字段模型 327 /// </summary> 328 public class ColumnModel 329 { 330 /// <summary> 331 /// 是否主键 332 /// </summary> 333 public bool IsPk { get; set; } 334 335 /// <summary> 336 /// 列名 337 /// </summary> 338 public string ColName { get; set; } 339 340 /// <summary> 341 /// 列类型 342 /// </summary> 343 public string ColType { get; set; } 344 345 /// <summary> 346 /// 列说明 347 /// </summary> 348 public string ColComment { get; set; } 349 350 /// <summary> 351 /// 列默认值 352 /// </summary> 353 public string ColDefault { get; set; } 354 } 355 }