用T4模版生成对应数据库表的实体类

  1 <#@ template debug="false" hostspecific="false" language="C#" #>
  2 <#@ output extension=".cs" #>
  3 <#@ assembly name="System.Data" #>
  4 <#@ assembly name="System.Xml" #>
  5 <#@ import namespace="System" #>
  6 <#@ import namespace="System.Xml" #>
  7 <#@ import namespace="System.Data" #>
  8 <#@ import namespace="System.Data.SqlClient" #>
  9 <# 
 10 ModelManager manager = new ModelManager();
 11 string tableName = "tbOptAuthor";
 12 DataTable table= manager.GetTableSchema(tableName);
 13 #>
 14 /* ****************************************************************************************
 15  * 版权所有：西安xxx软件有限公司
 16  * 用    途：数据传输对象(DTO)
 17  * 结构组成：
 18  * 作    者：王光旭
 19  * 创建日期：<#=DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")#> 
 20  * 历史记录：
 21  * ****************************************************************************************
 22  * 修改人员：             
 23  * 修改日期：
 24  * 修改说明：
 25  * ****************************************************************************************/
 26 using System;
 27 using System.Collections.Generic;
 28 using System.Linq;
 29 using System.Text;
 30 
 31 namespace wgx.Model
 32 {
 33     [Serializable]
 34     /// <summary>
 35     /// <#= tableName.Replace("tb","DTO_") #>
 36     /// </summary>
 37     public class <#= tableName.Replace("tb","DTO_") #>
 38     {
 39         #region 属性
 40     <#
 41     foreach(DataRow row in table.Rows)
 42     {
 43     #>
 44     /// <summary>
 45         /// <#=row["Description"]#>
 46         /// </summary>
 47         public <#= manager.TransFromSqlType(row["DataType"].ToString(),row["IsNullable"].ToString())#>  <#=row["ColumnName"]#>{ get; set; }
 48     <#}
 49     #>
 50     #endregion
 51     }
 52 }
 53 
 54 <#+
 55     public class ModelManager
 56     {
 57         /// <summary>
 58         /// 数据库连接字符串
 59         /// </summary>
 60         private const string CONNECTION_STRING="Data Source=.;Initial Catalog=dbserver;User ID=sa;pwd=000000";
 61         /// <summary>
 62         /// 用户信息表名
 63         /// </summary>
 64         private const string PERSONINFO_TABLE_NAME = "tbOptAuthor";
 65         /// <summary>
 66         /// 根据表名查询表结构信息
 67         /// SQL Server 2005写法
 68         /// </summary>
 69         private const string SELECT_SCHEMA_BY_TABLE_NAMEs = @"SELECT
 70                                                             d.name AS TableName,
 71                                                             a.colorder AS ColumnID,
 72                                                             a.name AS ColumnName,
 73                                                             b.name AS DataType,
 74                                                             g.[value] AS Description,
 75                                                             CASE WHEN a.isnullable = 0 THEN 'false'
 76                                                                 ELSE 'true'
 77                                                             END AS IsNullable
 78                                                             FROM syscolumns a left join systypes b
 79                                                             on a.xtype=b.xusertype
 80                                                             inner join sysobjects d
 81                                                             on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
 82                                                             left join sys.extended_properties g
 83                                                             on a.id=g.major_id AND a.colid = g.minor_id
 84                                                             WHERE d.[name] ='{0}'
 85                                                             order by a.id,a.colorder";
 86 
 87 
 88         /// <summary>
 89         /// 根据表名查询表结构信息
 90         /// SQL Server 2000写法
 91         /// </summary>
 92         private const string SELECT_SCHEMA_BY_TABLE_NAME = @"SELECT (
 93                                                             case when a.colorder=1 then d.name else '' end) N'表名',
 94                                                             a.colorder N'字段序号',a.name N'ColumnName',
 95                                                             (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
 96                                                             (case when (SELECT count(*) FROM sysobjects
 97                                                             WHERE (name in (SELECT name FROM sysindexes
 98                                                             WHERE (id = a.id) AND (indid in
 99                                                             (SELECT indid FROM sysindexkeys
100                                                             WHERE (id = a.id) AND (colid in
101                                                             (SELECT colid FROM syscolumns
102                                                             WHERE (id = a.id) AND (name = a.name))))))) AND
103                                                             (xtype = 'PK'))>0 then '√' else '' end) N'主键',b.name N'DataType',a.length N'占用字节数',
104                                                             COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
105                                                             (case when a.isnullable=1 then '√'else '' end) N'IsNullable',
106                                                             isnull(e.text,'') N'默认值',isnull(g.[value],'') AS N'Description'
107                                                             --into ##tx
108                                                             FROM syscolumns a left join systypes b on a.xtype=b.xusertype
109                                                             inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
110                                                             left join syscomments e on a.cdefault=e.id
111                                                             left join sysproperties g on a.id=g.id AND a.colid = g.smallid
112                                                             where d.name='{0}'
113                                                             order by object_name(a.id),a.colorder";
114 
115         /// <summary>
116         /// 获得数据连接
117         /// </summary>
118         /// <returns></returns>
119         private SqlConnection GetConnection()
120         {
121             return new SqlConnection(CONNECTION_STRING);
122         }
123         /// <summary>
124         /// 释放连接
125         /// </summary>
126         /// <param name="con"></param>
127         private void ReleaseConnection(SqlConnection con)
128         {
129             if (con != null)
130             {
131                 if (con.State == ConnectionState.Open)
132                 {
133                     con.Close();
134                 }
135             }
136         }
137 
138         /// <summary>
139         /// 
140         /// </summary>
141         /// <param name="tableName"></param>
142         public DataTable GetTableSchema(string tableName)
143         {
144             DataTable dt;
145             using (SqlConnection con = GetConnection())
146             {
147                 con.Open();
148 
149                 SqlCommand cmd = con.CreateCommand();
150                 cmd.CommandText = string.Format(SELECT_SCHEMA_BY_TABLE_NAME,tableName);
151                 cmd.CommandType = CommandType.Text;
152 
153                 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
154                 DataSet ds = new DataSet(); 
155                 adapter.Fill(ds);
156                 dt = ds.Tables[0];
157             }
158 
159             return dt;
160         }
161         /// <summary>
162         /// 
163         /// </summary>
164         public void Generate()
165         {
166             DataTable table = GetTableSchema(PERSONINFO_TABLE_NAME);
167 
168             if (table != null && table.Rows.Count > 0)
169             {
170                 foreach (DataRow row in table.Rows)
171                 {
172                     Console.WriteLine("public class {0}", row["TableName"]);
173                     Console.WriteLine("public {0} {1}", TransFromSqlType(row["DataType"].ToString(),row["IsNullable"].ToString()), row["ColumnName"]);
174                 }
175             }
176         }
177         /// <summary>
178         /// SQL
179         /// </summary>
180         /// <param name="type"></param>
181         /// <returns></returns>
182         public string TransFromSqlType(string type,string isNull)
183         {
184             string typeStr="";
185             switch (type)        //数据库的数据类型转换为对应的C#的数据类型，不是很完整
186             {
187                 case "datetime":
188                 case "smalldatetime":
189                     typeStr=isNull=="true"? "?":"";
190                     return "DateTime"+typeStr;
191                     break;
192                 case "int":
193                 case "smallint":
194                     typeStr=isNull=="true"? "?":"";
195                     return "int"+typeStr;
196                     break;
197                 case "bit":
198                     typeStr=isNull=="true"? "?":"";
199                     return "bool"+typeStr;
200                     break;
201                 case "float":
202                     typeStr=isNull=="true"? "?":"";
203                     return "double"+typeStr;
204                     break;
205                 case "decimal":
206                 case "money":
207                 case "smallmoney":
208                 case "numeric":
209                     typeStr=isNull=="true"? "?":"";
210                     return "decimal"+typeStr;
211                     break;
212                 case "uniqueidentifier":
213                     typeStr=isNull=="true"? "?":"";
214                     return "Guid"+typeStr;
215                     break;
216                 default:
217                     return "string"+typeStr;
218                     break;
219             }
220         }
221     }
222 #>

生成后的实体：

 1 /* ****************************************************************************************
 2  * 版权所有：西安xxx软件有限公司
 3  * 用    途：数据传输对象(DTO)
 4  * 结构组成：
 5  * 作    者：王光旭
 6  * 创建日期：2012-12-28 11:32:47 
 7  * 历史记录：
 8  * ****************************************************************************************
 9  * 修改人员：wgx             
10  * 修改日期：2014-12-28 11:32:47
11  * 修改说明：新增一个针对sql 2000数据库的查询方法
12  * ****************************************************************************************/
13 using System;
14 using System.Collections.Generic;
15 using System.Linq;
16 using System.Text;
17 
18 namespace wgx.Model
19 {
20     [Serializable]
21     /// <summary>
22     /// DTO_OptAuthor
23     /// </summary>
24     public class DTO_OptAuthor
25     {
26         #region 属性
27         /// <summary>
28         /// 用户组代码
29         /// </summary>
30         public string  strGroupCode{ get; set; }
31         /// <summary>
32         /// 功能模块代码
33         /// </summary>
34         public string  strCaseCode{ get; set; }
35         /// <summary>
36         /// 权限级别
37         /// </summary>
38         public string  strLevel{ get; set; }
39         /// <summary>
40         /// 组数据级别
41         /// </summary>
42         public string  strGroupLevel{ get; set; }
43         #endregion
44     }
45 }

 使用过程可能遇到的问题：

解决办法：

 

转载于:https://www.cnblogs.com/wgx0428/p/3332964.html