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

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

阅读量：6711 次

发布时间：2019-06-25

本文共 18451 字，大约阅读时间需要 61 分钟。

对于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     ///  20     /// <%=GetClassName(SourceTable) +"模型"%> 21     /// 
 22     [Serializable] 23     public class <%=GetClassName(SourceTable) %> : BaseModel 24     {    25         ///  26         /// 表名 27         /// 
 28         public static readonly string TableName = "<%=GetClassName(SourceTable) %>"; 29          30         ///  31         /// 构造函数 32         /// 
 33         public <%=GetClassName(SourceTable) %>() : base(TableName) 34         { 35         } 36          37         private Guid Id = Guid.Empty; 38         <% foreach (ColumnSchema column in SourceTable.Columns) {%> 39         ///  40         /// <%=column.Description %> 41         /// 
 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     ///  64     /// <%=GetClassName(SourceTable) +"数据模型"%> 65     /// 
 66     [Serializable] 67     public class <%=GetClassName(SourceTable)+"ListData" %> 68     {    69         ///  70         /// 总记录数 71         /// 
 72         public int RecordCount { get; set; } 73          74         ///  75         /// 数据列表 76         /// 
 77         public List<<%=GetClassName(SourceTable)+"ListModel" %>> RecordList { get; set; } 78     } 79      80     ///  81     /// <%=GetClassName(SourceTable) +"列表模型"%> 82     /// 
 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         ///  89         /// <%=column.Description %> 90         /// 
 91         public <%=GetCSharpVariableType(column)=="Guid" || GetCSharpVariableType(column)=="DateTime"?"string":GetCSharpVariableType(column) %> <%=GetPascalName(column) %> { get; set; } 92          93         <% }%> 94     } 95     <%} %> 96 } 97

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         ///  14         /// 数据库连接字符串 15         /// 
 16         private static string _connstr = "Data Source=localhost;Initial Catalog=Test;User Id=sa;Password=123456"; 17  18         ///  19         /// 主函数 20         /// 
 21         ///  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         ///  35         /// 生成Model文件 36         /// 
 37         ///  38         ///  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         ///  54         /// 创建模板 55         /// 
 56         ///  57         /// 
       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
      
        tables = GetTables(); 64             foreach (var table in tables) 65             { 66                 templete.AppendFormat("    #region {0}\n", table.name); 67                 templete.Append("    /// 
       \n"); 68                 templete.AppendFormat("    /// {0}模型\n", table.name); 69                 templete.Append("    /// 
\n"); 70                 templete.Append("    [Serializable]\n"); 71                 templete.AppendFormat("    public class {0} : BaseModel\n    {
     {
     ", table.name); 72                 templete.Append("\n"); 73                 templete.Append("        /// 
       \n"); 74                 templete.Append("        /// 表名\n"); 75                 templete.Append("        /// 
\n"); 76                 templete.AppendFormat("        public static readonly string TableName = \"{0}\";\n", table.name); 77                 templete.Append("\n"); 78                 templete.Append("        /// 
       \n"); 79                 templete.Append("        /// 构造函数\n"); 80                 templete.Append("        /// 
\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("        /// 
       \n"); 87                     templete.AppendFormat("        /// {0}\n", columu.ColComment); 88                     templete.Append("        /// 
\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                     else105                     {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("    /// 
       \n");114                 templete.AppendFormat("    /// {0}数据模型\n", table.name);115                 templete.Append("    /// 
\n");116                 templete.Append("    [Serializable]\n");117                 templete.AppendFormat("    public class {0}ListData\n    {
     {
     ", table.name);118                 templete.Append("\n");119                 templete.Append("        /// 
       \n");120                 templete.Append("        /// 总记录数\n");121                 templete.Append("        /// 
\n");122                 templete.Append("        public int RecordCount { get; set; }\n");123                 templete.Append("        /// 
       \n");124                 templete.Append("\n");125                 templete.Append("        /// 数据列表\n");126                 templete.Append("        /// 
\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("    /// 
       \n");133                 templete.AppendFormat("    /// {0}列表模型\n", table.name);134                 templete.Append("    /// 
\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("        /// 
       \n");144                         templete.AppendFormat("        /// {0}\n", columu.ColComment);145                         templete.Append("        /// 
\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                         else151                         {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         /// 
       166         /// 获取表数据167         /// 
168         /// 
       169         private List
       
         GetTables()170         {171             List
        
          tables = new List
         
          ();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.id182                                                           AND obj.xtype = 'U'--表示用户表183                                                LEFT JOIN systypes typ--类型184                                                       ON col.xtype = typ.xusertype185                                                LEFT JOIN sys.extended_properties cmt--字段说明186                                                       ON col.id = cmt.major_id--表Id187                                                          AND col.colid = cmt.minor_id--字段Id188                                                LEFT JOIN syscomments dft--默认值189                                                       ON col.cdefault = dft.id190                                         ORDER  BY obj.name,191                                                   col.id ASC 192                                         ");193             DataTable pk = Query(@"--获取表的主键字段名194                                    SELECT CCU.COLUMN_NAME,195                                           TC.TABLE_NAME196                                    FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC197                                           INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU198                                                   ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME199                                    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
          
           ();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         /// 
           226 /// 简单的SQL查询227 /// 
228         /// 
           229         /// 
           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         /// 
           247 /// 获取C#类型248 /// 
249         /// 
           250         /// 
           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         /// 
           289 /// 获取C#默认值290 /// 
291         /// 
           292         /// 
           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     /// 
           310 /// 表模型311 /// 
312     public class TableModel313     {314         /// 
           315 /// 表名316 /// 
317         public string name { get; set; }318 319         /// 
           320 /// 表字段321 /// 
322         public List
           
             columns { get; set; }323 }324 325 /// 
            326 /// 字段模型327 /// 
328 public class ColumnModel329 {330 /// 
            331 /// 是否主键332 /// 
333 public bool IsPk { get; set; }334 335 /// 
            336 /// 列名337 /// 
338 public string ColName { get; set; }339 340 /// 
            341 /// 列类型342 /// 
343 public string ColType { get; set; }344 345 /// 
            346 /// 列说明347 /// 
348 public string ColComment { get; set; }349 350 /// 
            351 /// 列默认值352 /// 
353 public string ColDefault { get; set; }354 }355 }