c# 使用T4模板生成实体类（sqlserver）

新建类库，右键添加 "文本模板"

添加完成之后生成如下后缀为 tt的文件：

 

 双击文件：TextTemplate_Test.tt 文件打开，替换代码如下

 1 <#@ template debug="false" hostspecific="true" language="C#" #>
 2 <#@ assembly name="System.Core" #>
 3 <#@ assembly name="System.Data" #>
 4 <#@ assembly name="System.xml" #>
 5 <#@ import namespace="System.Linq" #>
 6 <#@ import namespace="System.Text" #>
 7 <#@ import namespace="System.Collections.Generic" #>
 8 <#@ import namespace="System.Data.SqlClient" #>
 9 <#@ import namespace="System.Data" #>
10 <#@ include file="ModelAuto.ttinclude"#>
11 <#@ output extension=".cs" #>
12 
13 <# var manager = new Manager(Host, GenerationEnvironment, true) { OutputPath = Path.GetDirectoryName(Host.TemplateFile)}; #>
14 <# 
15     //数据库连接
16     string connectionString ="Data Source=127.0.0.1;Initial Catalog=TestDB;User ID=sa;Password=123456;"; 
17     SqlConnection conn = new SqlConnection(connectionString); 
18     conn.Open(); 
19     //查询表
20     System.Data.DataTable schema = conn.GetSchema("TABLES"); 
21     string selectQuery = "select * from @tableName";   //查询表语句
22     SqlCommand command = new SqlCommand(selectQuery,conn); 
23     SqlDataAdapter ad = new SqlDataAdapter(command); 
24     System.Data.DataSet ds = new DataSet();
25     //查询字段
26     string propQuery = "SELECT 表名=sobj.name,字段名=scol.name,字段说明=sprop.[value] FROM syscolumns as scol inner join sys.sysobjects as sobj on scol.id=sobj.id and sobj.xtype='U' and sobj.name<>'dtproperties' left join sys.extended_properties as sprop on scol.id=sprop.major_id and scol.colid=sprop.minor_id where sobj.name='@tableName' and scol.name='@columnName'"; 
27     SqlCommand command2 = new SqlCommand(propQuery,conn); 
28     SqlDataAdapter ad2 = new SqlDataAdapter(command2); 
29     System.Data.DataSet ds2 = new DataSet();
30  #>
31 
32 <# foreach(System.Data.DataRow row in schema.Rows) { #>
33 <# manager.StartBlock(row["TABLE_NAME"].ToString()+".cs"); #>
34 using System;
35 
36 namespace Entity.Model
37 {
38     /// <summary>
39     /// 实体类：<#= row["TABLE_NAME"].ToString() #> 
40     /// </summary>
41     [Serializable]
42     public class <#= row["TABLE_NAME"].ToString() #>
43     {
44         <# 
45         ds.Tables.Clear();
46         command.CommandText = selectQuery.Replace("@tableName","["+row["TABLE_NAME"].ToString()+"]"); 
47         ad.FillSchema(ds, SchemaType.Mapped, row["TABLE_NAME"].ToString());
48         foreach (DataColumn dc in ds.Tables[0].Columns)
49         { 
50         #>
51             <# 
52             ds2.Tables.Clear();
53             command2.CommandText = propQuery.Replace("@tableName",row["TABLE_NAME"].ToString()); 
54             command2.CommandText = command2.CommandText.Replace("@columnName",dc.ColumnName); 
55             ad2.Fill(ds2);
56             #>
57 
58         ///<summary>
59         ///<#= manager.TransFromSqlType(dc.DataType.Name) #>:<#=ds2.Tables[0].Rows[0].ItemArray[2]#>
60         ///</summary>
61         public <#= manager.TransFromSqlType(dc.DataType.Name) #> <#= dc.ColumnName #> { get; set; }  
62         <#}#>    
63     }
64 }
65 
66 <# manager.EndBlock(); #>
67 
68 <#}#>
69 
70 <#manager.Process(true);#>

需要更换几个配置的地方：

1，设置数据库连接，找到该段代码：string connectionString ="Data Source=127.0.0.1;Initial Catalog=TestDB;User ID=sa;Password=123456;";  替换你要连接的数据库即可；

2，设置命名空间，找到代码：namespace Entity.Model {....} ，将该处的命名空间替换你要的即可；

3，相同目录下添加代码自动生成逻辑文件，文件名字为：ModelAuto.ttinclude

文件内容如下，

  1 <#@ assembly name="System.Core"#>
  2 <#@ assembly name="EnvDTE"#>
  3 <#@ import namespace="System.Collections.Generic"#>
  4 <#@ import namespace="System.IO"#>
  5 <#@ import namespace="System.Text"#>
  6 <#@ import namespace="Microsoft.VisualStudio.TextTemplating"#>
  7 
  8 <#+
  9 
 10 class Manager
 11 {
 12     public struct Block {
 13         public String Name;
 14         public int Start, Length;
 15     }
 16 
 17     public List<Block> blocks = new List<Block>();
 18     public Block currentBlock;
 19     public Block footerBlock = new Block();
 20     public Block headerBlock = new Block();
 21     public ITextTemplatingEngineHost host;
 22     public ManagementStrategy strategy;
 23     public StringBuilder template;
 24     public String OutputPath { get; set; }
 25 
 26     public Manager(ITextTemplatingEngineHost host, StringBuilder template, bool commonHeader) {
 27         this.host = host;
 28         this.template = template;
 29         OutputPath = String.Empty;
 30         strategy = ManagementStrategy.Create(host);
 31     }
 32 
 33     public void StartBlock(String name) {
 34         currentBlock = new Block { Name = name, Start = template.Length };
 35     }
 36 
 37     public void StartFooter() {
 38         footerBlock.Start = template.Length;
 39     }
 40 
 41     public void EndFooter() {
 42         footerBlock.Length = template.Length - footerBlock.Start;
 43     }
 44 
 45     public void StartHeader() {
 46         headerBlock.Start = template.Length;
 47     }
 48 
 49     public void EndHeader() {
 50         headerBlock.Length = template.Length - headerBlock.Start;
 51     }    
 52 
 53     public void EndBlock() {
 54         currentBlock.Length = template.Length - currentBlock.Start;
 55         blocks.Add(currentBlock);
 56     }
 57 
 58     public void Process(bool split) {
 59         String header = template.ToString(headerBlock.Start, headerBlock.Length);
 60         String footer = template.ToString(footerBlock.Start, footerBlock.Length);
 61         blocks.Reverse();
 62         foreach(Block block in blocks) {
 63             String fileName = Path.Combine(OutputPath, block.Name);
 64             if (split) {
 65                 String content = header + template.ToString(block.Start, block.Length) + footer;
 66                 strategy.CreateFile(fileName, content);
 67                 template.Remove(block.Start, block.Length);
 68             } else {
 69                 strategy.DeleteFile(fileName);
 70             }
 71         }
 72     }
 73 
 74         /// <summary>
 75         /// SQL[不完善,需要的自己改造]
 76         /// 更换字段类型
 77         /// </summary>
 78         /// <param name="type"></param>
 79         /// <returns></returns>
 80         public string TransFromSqlType(string type)
 81         {
 82             if (string.IsNullOrEmpty(type))
 83             {
 84                 return string.Empty;
 85             }
 86             if (string.Equals(type, "Boolean", StringComparison.OrdinalIgnoreCase))
 87             {
 88                 return "bool";
 89             }
 90             else if (string.Equals(type, "Int32", StringComparison.OrdinalIgnoreCase))
 91             {
 92                 return "int";
 93             }
 94             else if (string.Equals(type, "Int64", StringComparison.OrdinalIgnoreCase))
 95             {
 96                 return "long";
 97             }
 98             else if (string.Equals(type, "String", StringComparison.OrdinalIgnoreCase))
 99             {
100                 return "string";
101             }
102             else if(string.Equals(type, "Byte", StringComparison.OrdinalIgnoreCase))
103             {
104                 return "byte";
105             }
106             else if (string.Equals(type, "Decimal", StringComparison.OrdinalIgnoreCase))
107             {
108                 return "decimal";
109             }
110             else if (string.Equals(type, "datetime", StringComparison.OrdinalIgnoreCase))
111             {
112                 return "DateTime";
113             }
114             return "string";
115         }
116 
117 }
118 
119 class ManagementStrategy
120 {
121     internal static ManagementStrategy Create(ITextTemplatingEngineHost host) {
122         return (host is IServiceProvider) ? new VSManagementStrategy(host) : new ManagementStrategy(host);
123     }
124 
125     internal ManagementStrategy(ITextTemplatingEngineHost host) { }
126 
127     internal virtual void CreateFile(String fileName, String content) {
128         File.WriteAllText(fileName, content);
129     }
130 
131     internal virtual void DeleteFile(String fileName) {
132         if (File.Exists(fileName))
133             File.Delete(fileName);
134     }
135 }
136 
137 class VSManagementStrategy : ManagementStrategy
138 {
139     private EnvDTE.ProjectItem templateProjectItem;
140 
141     internal VSManagementStrategy(ITextTemplatingEngineHost host) : base(host) {
142         IServiceProvider hostServiceProvider = (IServiceProvider)host;
143         if (hostServiceProvider == null)
144             throw new ArgumentNullException("Could not obtain hostServiceProvider");
145 
146         EnvDTE.DTE dte = (EnvDTE.DTE)hostServiceProvider.GetService(typeof(EnvDTE.DTE));
147         if (dte == null)
148             throw new ArgumentNullException("Could not obtain DTE from host");
149 
150         templateProjectItem = dte.Solution.FindProjectItem(host.TemplateFile);
151     }
152 
153     internal override void CreateFile(String fileName, String content) {
154         base.CreateFile(fileName, content);
155         ((EventHandler)delegate { templateProjectItem.ProjectItems.AddFromFile(fileName); }).BeginInvoke(null, null, null, null);
156     }
157 
158     internal override void DeleteFile(String fileName) {
159         ((EventHandler)delegate { FindAndDeleteFile(fileName); }).BeginInvoke(null, null, null, null);
160     }
161 
162     private void FindAndDeleteFile(String fileName) {
163         foreach(EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems) {
164             if (projectItem.get_FileNames(0) == fileName) {
165                 projectItem.Delete();
166                 return;
167             }
168         }
169     }
170 }#>

接下来就是见证奇迹的时刻，选中 TextTemplate_Test.tt 文件 按 Ctrl+C即可(每次更新实体类时,需要先修改一下模板文件,随便修改什么地方，按个空格也可以)，生成模板代码如下：

 

 

posted @ 2020-01-02 18:28  找.net工作(北京)  阅读(1348)  评论(2)  编辑  收藏  举报