C#：CodeSmith根据数据库中的表创建C#数据模型Model + 因为没有钱买正版，所以附加自己写的小代码

<%-- 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> View Code

　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;\n\n");
 62             templete.AppendFormat("namespace {0}\n{{\n", namespaces);
 63             List<TableModel> tables = GetTables();
 64             foreach (var table in tables)
 65             {
 66                 templete.AppendFormat("    #region {0}\n", table.name);
 67                 templete.Append("    /// <summary>\n");
 68                 templete.AppendFormat("    /// {0}模型\n", table.name);
 69                 templete.Append("    /// </summary>\n");
 70                 templete.Append("    [Serializable]\n");
 71                 templete.AppendFormat("    public class {0} : BaseModel\n    {{", table.name);
 72                 templete.Append("\n");
 73                 templete.Append("        /// <summary>\n");
 74                 templete.Append("        /// 表名\n");
 75                 templete.Append("        /// </summary>\n");
 76                 templete.AppendFormat("        public static readonly string TableName = \"{0}\";\n", table.name);
 77                 templete.Append("\n");
 78                 templete.Append("        /// <summary>\n");
 79                 templete.Append("        /// 构造函数\n");
 80                 templete.Append("        /// </summary>\n");
 81                 templete.AppendFormat("        public {0}() : base(TableName) {{ }}\n", table.name);
 82                 templete.Append("        private Guid Id = Guid.Empty;\n");
 83                 table.columns.ForEach(columu =>
 84                 {
 85                     templete.Append("\n");
 86                     templete.Append("        /// <summary>\n");
 87                     templete.AppendFormat("        /// {0}\n", columu.ColComment);
 88                     templete.Append("        /// </summary>\n");
 89                     if (columu.IsPk)
 90                     {
 91                         templete.AppendFormat("        public Guid {0}\n", columu.ColName);
 92                         templete.Append("        {\n");
 93                         templete.Append("            get { return Id; }\n");
 94                         templete.Append("            set\n");
 95                         templete.Append("            {\n");
 96                         templete.Append("                Id = value;\n");
 97                         templete.Append("                if (value != null)\n");
 98                         templete.Append("                {\n");
 99                         templete.Append("                    base.BaseId = value;\n");
100                         templete.Append("                }\n");
101                         templete.Append("            }\n");
102                         templete.Append("        }\n");
103                     }
104                     else
105                     {
106                         templete.AppendFormat("        public {0} {1} {{ get; set; }} {2}\n", GetCSType(columu.ColType), columu.ColName, GetCSDefault(columu.ColDefault));
107                     }
108                 });
109                 templete.Append("    }");
110 
111                 templete.Append("\n");
112 
113                 templete.Append("    /// <summary>\n");
114                 templete.AppendFormat("    /// {0}数据模型\n", table.name);
115                 templete.Append("    /// </summary>\n");
116                 templete.Append("    [Serializable]\n");
117                 templete.AppendFormat("    public class {0}ListData\n    {{", table.name);
118                 templete.Append("\n");
119                 templete.Append("        /// <summary>\n");
120                 templete.Append("        /// 总记录数\n");
121                 templete.Append("        /// </summary>\n");
122                 templete.Append("        public int RecordCount { get; set; }\n");
123                 templete.Append("        /// <summary>\n");
124                 templete.Append("\n");
125                 templete.Append("        /// 数据列表\n");
126                 templete.Append("        /// </summary>\n");
127                 templete.AppendFormat("        public List<{0}ListModel> RecordList {{ get; set; }}\n", table.name);
128                 templete.Append("    }");
129 
130                 templete.Append("\n");
131 
132                 templete.Append("    /// <summary>\n");
133                 templete.AppendFormat("    /// {0}列表模型\n", table.name);
134                 templete.Append("    /// </summary>\n");
135                 templete.Append("    [Serializable]\n");
136                 templete.AppendFormat("    public class {0}ListModel\n    {{", table.name);
137                 templete.Append("\n");
138                 table.columns.ForEach(columu =>
139                 {
140                     if (columu.ColName != "IsDeleted")
141                     {
142                         templete.Append("\n");
143                         templete.Append("        /// <summary>\n");
144                         templete.AppendFormat("        /// {0}\n", columu.ColComment);
145                         templete.Append("        /// </summary>\n");
146                         if (new string[] { "Guid", "DateTime" }.Contains(GetCSType(columu.ColType)))
147                         {
148                             templete.AppendFormat("        public string {0} {{ get; set; }}\n", columu.ColName);
149                         }
150                         else
151                         {
152                             templete.AppendFormat("        public {0} {1} {{ get; set; }}\n", GetCSType(columu.ColType), columu.ColName);
153                         }
154                     }
155                 });
156                 templete.Append("    }\n");
157                 templete.Append("    #endregion\n");
158                 templete.Append("\n");
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