懒人小工具：T4生成实体类Model,Insert,Select,Delete以及导出Excel的方法

       由于最近公司在用webform开发ERP，用到大量重复机械的代码，之前写了篇文章，懒人小工具：自动生成Model,Insert,Select,Delete以及导出Excel的方法,但是有人觉得这种方法很麻烦。其实我感觉确实是有点麻烦，麻烦在于字符串的拼接。
      这种时候我想到了T4模板，用过EF的 DatabaseFirst自动生成实体的同学就明白，dbfirst 自带T4模板，之前我们在学习spring.net框架的时候，也有用过T4模板根据数映射到实体类自动创建仓储。T4模板其实还有很多应用的场景。
       T4模板确实挺方便的，但是其实其中用过的原理和我之前做winform小工具差不多。都是根据数据字段和类型的映射生成实体。另外就是ado.net基础知识。
但是这种方法你得了解T4模板的基础语法。各有利弊，但是其实语法也不是很难.   

先预览下扩展的界面，winform程序就不在乎美观不美观了...

 

 

      现在添加了安装包，直接点击下一步就可以了，在桌面生成快捷方式，点击可用。

     一、下面我就用最简单的方式用T4模板创建Model.

 1 <#@ template language="C#" debug="True" hostspecific="True" #>
 2 <#@ output extension=".cs" #>
 3 <#@ assembly name="System.Data" #>
 4 <#@ assembly name="System.xml" #>
 5 <#@ import namespace="System.Collections.Generic" #>
 6 <#@ import namespace="System.Data.SqlClient" #>
 7 <#@ import namespace="System.Data" #>
 8 using System.Collections.Generic;
 9 using System.Linq;
10 using System.Text;
11 using System.Text.RegularExpressions;
12 using System.Windows.Forms;
13 using System.Data;
14 using CMS.Utilities;
15 using System.Data.OleDb;
16 using System.Configuration;
17 using System.Data.SqlClient;
18 using System;
19 namespace MyProject.Entities
20 {     
21       <#
22           string connectionString = "server=.;database=databasename;uid=uid;pwd=123456";
23              string selectQuery = "select * from Team_";
24              string tableName="Team_";
25           SqlConnection conn = new SqlConnection(connectionString);
26           conn.Open();
27              System.Data.DataTable schema = null;
28                 SqlCommand selectcommand = new SqlCommand(selectQuery, conn);
29                 SqlDataAdapter sda = new SqlDataAdapter(selectcommand);
30                 System.Data.DataSet dss = new   System.Data.DataSet();
31                 sda.Fill(dss);
32                 schema=dss.Tables[0];
33                 System.Data.DataTable   dt = dss.Tables[0];
34                 System.Data.DataRow   dr = dss.Tables[0].Rows[0];
35            SqlCommand command = new SqlCommand(selectQuery,conn);
36            SqlDataAdapter ad = new SqlDataAdapter(command); 
37 
38      #>  
39  public class <#= tableName#>Biz 
40  {      
41       public class <#= tableName#>Model      
42        {         
43           <#   foreach (DataColumn dc in dss.Tables[0].Columns)
44                   {    #>                    
45             private <#= dc.DataType.Name #> _<#= dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower())      #>;                      
46             public <#= dc.DataType.Name #> <#= dc.ColumnName #>
47             {
48                    get { return _<#= dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower()) #>; }
49                    set { _<#= dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower()) #> = value; }
50              }                                                
51               <# }  #>     
52      
53            }     
54            
55 
56    }
57           
58 }
View Code

 

        这种方式是挺简单的。<#@ import namespace="System.Data" #>这段代码是引用命名空间，在你后台代码中用，也就是包括下面ado.net中需要引用的命名空间，基本都写在这里。用 <#...dosomething...#>这段就是不用展示出来的代码，其他的代码就是在你用T4模板生成的时候要用的代码。是不是很简单。上面的代码无需详细解释了吧,运用ado.net链接数据库，获取表字段和类型。Ctrl+S就自动生成了实体类。

    根据模板生成的Model

 1 using System.Collections.Generic;
 2 using System.Linq;
 3 using System.Text;
 4 using System.Text.RegularExpressions;
 5 using System.Windows.Forms;
 6 using System.Data;
 7 using CMS.Utilities;
 8 using System.Data.OleDb;
 9 using System.Configuration;
10 using System.Data.SqlClient;
11 using System;
12 namespace MyProject.Entities
13 {
14 
15     public class Team_Biz
16     {
17         public class Team_Model
18         {
19 
20             private String _team_code;
21             public String Team_code
22             {
23                 get { return _team_code; }
24                 set { _team_code = value; }
25             }
26 
27             private String _team_name;
28             public String Team_name
29             {
30                 get { return _team_name; }
31                 set { _team_name = value; }
32             }
33 
34             private String _team_status;
35             public String Team_status
36             {
37                 get { return _team_status; }
38                 set { _team_status = value; }
39             }
40 
41             private String _team_user;
42             public String Team_user
43             {
44                 get { return _team_user; }
45                 set { _team_user = value; }
46             }
47 
48             private DateTime _team_date;
49             public DateTime Team_date
50             {
51                 get { return _team_date; }
52                 set { _team_date = value; }
53             }
54         }
55 
56     }
57 
58 }
View Code

 

     二、T4数据库所有表生成所有实体。大同小异，注意细节。

 1 <#@ template language="C#" debug="True" hostspecific="True" #>
 2 
 3 <#@ assembly name="System.Data" #> 
 4 
 5 <#@ assembly name="System.xml" #>
 6 
 7 <#@ import namespace="System.Collections.Generic" #>
 8 
 9 <#@ import namespace="System.Data.SqlClient" #>
10 
11 <#@ import namespace="System.Data" #>
12 
13 <#@ output extension=".cs" #>
14 
15  using System;
16 
17 namespace Test.T4
18 
19 {     
20 
21       <#
22 
23            string connectionString="data source=(local);initial catalog=musicstore;user id=sa;password=123456;";
24 
25            SqlConnection conn = new SqlConnection(connectionString);
26 
27            conn.Open();
28 
29            DataTable schema = conn.GetSchema("TABLES");
30 
31            string strSql = "select * from @tableName";
32 
33            SqlCommand command = new SqlCommand(strSql,conn);
34 
35            SqlDataAdapter ad = new SqlDataAdapter(command);
36 
37            DataSet ds = new DataSet();        
38 
39            foreach(DataRow row in schema.Rows)
40 
41            {  #>  
42 
43            public class <#= row["TABLE_NAME"].ToString().Trim() #>                   
44 
45            {    <#                     
46 
47                    ds.Tables.Clear();
48 
49                   command.CommandText = strSql.Replace("@tableName",row["TABLE_NAME"].ToString());
50 
51                   ad.FillSchema(ds, SchemaType.Mapped, row["TABLE_NAME"].ToString());         
52                
53                   foreach (DataColumn dc in ds.Tables[0].Columns)
54 
55                   {    #>                    
56                   public <#= dc.DataType.Name #> <#= dc.ColumnName #> { get; set; }
57               <# }  #>         
58            }                  
59 
60            <# 
61                   
62            } #>                
63            <# conn.Close(); #>
64 }
View Code

 

          DataTable schema = conn.GetSchema("TABLES");是获取数据库所有表名，然后遍历所有表名遍历表字段和类型，根据这些表生成实体。
另外还有一种方法是用数据存储过程自动生成实体。这种方法就需要SQL的基础知识了。

      三、存储过程生成表实体

 1 SET ANSI_NULLS ON;  
 2 SET QUOTED_IDENTIFIER ON;  
 3 GO  
 4    
 5 CREATE PROC [dbo].[p_db_wsp]
 6     @dbname VARCHAR(50) ,   --数据库名  
 7     @path VARCHAR(100) ,    --实体类所在目录名，如D:/My/Models  
 8     @namespace VARCHAR(50) --实体类命名空间,默认值为Models  
 9 AS --判断数据库是否存在  
10     IF ( DB_ID(@dbname) IS NOT NULL )
11         BEGIN  
12             IF ( ISNULL(@namespace, '') = '' )
13                 SET @namespace = 'Models';  
14 -- 允许配置高级选项  
15             EXEC sp_configure 'show advanced options', 1;  
16 -- 重新配置  
17             RECONFIGURE;  
18 -- 启用Ole Automation Procedures   
19             EXEC sp_configure 'Ole Automation Procedures', 1;  
20 -- 启用xp_cmdshell,可以向磁盘中写入文件  
21             EXEC sp_configure 'xp_cmdshell', 1;  
22 -- 重新配置  
23             RECONFIGURE;  
24             DECLARE @dbsql VARCHAR(1000) ,
25                 @tablename VARCHAR(100);  
26             SET @dbsql = 'declare wsp cursor for select name from ' + @dbname
27                 + '..sysobjects where xtype=''u''  and name <>''sysdiagrams''';  
28             EXEC(@dbsql);  
29             OPEN wsp;  
30             FETCH wsp INTO @tablename;--使用游标循环遍历数据库中每个表  
31             WHILE ( @@fetch_status = 0 )
32                 BEGIN  
33 --根据表中字段组合实体类中的字段和属性  
34                     DECLARE @nsql NVARCHAR(4000) ,
35                         @sql VARCHAR(8000);  
36                     SET @nsql = 'select @s=isnull(@s+char(9)+''private '',''using System;'
37                         + CHAR(13) + 'using System.Collections.Generic;'
38                         + CHAR(13) + 'using System.Text;' + CHAR(13)
39                         + 'namespace ' + @namespace + CHAR(13) + '{' + CHAR(13)
40                         + CHAR(9) + 'public class ' + @tablename + CHAR(13)
41                         + '{''+char(13)+char(9)+''private '')+  
42 case when a.name in(''image'',''uniqueidentifier'',''ntext'',''varchar'',''ntext'',''nchar'',''nvarchar'',''text'',''char'') then ''string''  
43 when a.name in(''tinyint'',''smallint'',''int'',''bigint'') then ''int''  
44 when a.name in(''datetime'',''smalldatetime'') then ''DateTime''  
45 when a.name in(''float'',''decimal'',''numeric'',''money'',''real'',''smallmoney'') then ''decimal''  
46 when a.name =''bit'' then ''bool''  
47 else a.name end+'' ''+lower(''_''+b.name)+'';''+char(13)+char(9)+''public ''+  
48 case when a.name in(''image'',''uniqueidentifier'',''ntext'',''varchar'',''ntext'',''nchar'',''nvarchar'',''text'',''char'') then ''string''  
49 when a.name in(''tinyint'',''smallint'',''int'') then ''int''  
50 when a.name=''bigint'' then ''long''  
51 when a.name in(''datetime'',''smalldatetime'') then ''DateTime''  
52 when a.name in(''float'',''decimal'',''numeric'',''money'',''real'',''smallmoney'') then ''decimal''  
53 when a.name =''bit'' then ''bool''  
54 else a.name end  
55 +'' ''+b.name+char(13)+char(9)+''{''+char(13)+char(9)+char(9)+''get{return ''+lower(''_''+b.name)+'';}''+  
56 char(13)+char(9)+char(9)+''set{''+lower(''_''+b.name)+''=value;}''+char(13)+char(9)+''}''+char(13)  
57 from ' + @dbname + '..syscolumns b,  
58 (select distinct name,xtype from ' + @dbname + '..systypes where status=0) a  
59 where a.xtype=b.xtype and b.id=object_id(''' + @dbname + '..' + @tablename
60                         + ''')';  
61                     EXEC sp_executesql @nsql, N'@s varchar(8000) output',
62                         @sql OUTPUT;  
63                     SET @sql = @sql + CHAR(9) + '}' + CHAR(13) + '}';  
64 --print @sql  
65                     DECLARE @err INT ,
66                         @fso INT ,
67                         @fleExists BIT ,
68                         @file VARCHAR(100);  
69                     SET @file = @path + '/' + @tablename + '.cs';  
70                     EXEC @err= sp_OACreate 'Scripting.FileSystemObject',
71                         @fso OUTPUT;  
72                     EXEC @err= sp_OAMethod @fso, 'FileExists',
73                         @fleExists OUTPUT, @file;  
74                     EXEC @err = sp_OADestroy @fso;  
75    
76                     IF @fleExists != 0
77                         EXEC('exec xp_cmdshell ''del '+@file+''''); --存在则删除  
78                     EXEC('exec xp_cmdshell ''echo '+@sql+' > '+@file+''''); --将文本写进文件中  
79                     SET @sql = NULL;  
80                     FETCH wsp INTO @tablename;  
81                 END;  
82             CLOSE wsp;  
83             DEALLOCATE wsp;  
84             PRINT '生成成功！';  
85         END;  
86     ELSE
87         PRINT '数据库不存在！';
View Code

 

     调用存储过程：    EXEC [dbo].[p_db_wsp] '数据库名字', '保存的路径：D:\work\新建文件夹', '生成实体类名字';

    上面的方法都是生成实体的，下面就是生成insert的方法

   四,T4生成insert的方法

  1 <#@ template language="C#" debug="True" hostspecific="True" #>
  2 <#@ output extension=".cs" #>
  3 <#@ assembly name="System.Data" #>
  4 <#@ assembly name="System.xml" #>
  5 <#@ import namespace="System.Collections.Generic" #>
  6 <#@ import namespace="System.Data.SqlClient" #>
  7 <#@ import namespace="System.Data" #>
  8 using System.Collections.Generic;
  9 using System.Linq;
 10 using System.Text;
 11 using System.Text.RegularExpressions;
 12 using System.Windows.Forms;
 13 using System.Data;
 14 using CMS.Utilities;
 15 using System.Data.OleDb;
 16 using System.Configuration;
 17 using System.Data.SqlClient;
 18 using System;
 19 namespace MyProject.Entitiese
 20 {     
 21     <#
 22           string connectionString = "server=192.168.2.230;database=tjprj;uid=erptest;pwd=test@123456";
 23              string selectQuery = "select * from Team_";
 24              string tableName="Team_";
 25           SqlConnection conn = new SqlConnection(connectionString);
 26           conn.Open();
 27              System.Data.DataTable schema = null;
 28                 SqlCommand selectcommand = new SqlCommand(selectQuery, conn);
 29                 SqlDataAdapter sda = new SqlDataAdapter(selectcommand);
 30                 System.Data.DataSet dss = new   System.Data.DataSet();
 31                 sda.Fill(dss);
 32                 schema=dss.Tables[0];
 33                 System.Data.DataTable   dt = dss.Tables[0];
 34                 System.Data.DataRow   dr = dss.Tables[0].Rows[0];
 35            SqlCommand command = new SqlCommand(selectQuery,conn);
 36            SqlDataAdapter ad = new SqlDataAdapter(command); 
 37      #>  
 38  public class <#= tableName#>Bizs
 39  {      
 40       public class <#= tableName#>Models      
 41        {         
 42           <#   foreach (DataColumn dc in dss.Tables[0].Columns)
 43                   {    #>                    
 44             private <#= dc.DataType.Name #> _<#= dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower())      #>;                      
 45             public <#= dc.DataType.Name #> <#= dc.ColumnName #>
 46             {
 47                    get { return _<#= dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower()) #>; }
 48                    set { _<#= dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower()) #> = value; }
 49              }                                                
 50               <# }  #>     
 51      
 52            }     
 53         public bool Insert<#= tableName#>(<#= tableName#>Models model)
 54         {
 55              string strSql = @"
 56                    INSERT Team_(
 57             <#   foreach (DataColumn dc in dss.Tables[0].Columns)
 58                   {    #><#= dc.ColumnName #>,
 59               <# }  #>   
 60                      )
 61                  VALUES (
 62                   <#   foreach (DataColumn dc in dss.Tables[0].Columns)
 63                   {    #><#= dc.ColumnName #>,
 64                <# }  #>         
 65                      )
 66                      ";
 67                  SqlParameter[] parameters = new SqlParameter[]
 68                     {
 69                         <#   foreach (DataColumn dc in dss.Tables[0].Columns)
 70                   {    #>  new SqlParameter("<#= dc.ColumnName #>,", SqlDbType.NVarChar, 255),
 71                     <# }  #>       
 72                         };
 73                 <#    for (int i = 0; i < dr.Table.Columns.Count; i++)
 74                   {    #> parameters[<#=i#>].Value = model.<#=dr.Table.Columns[i] #>;
 75                    <# }  #>       
 76              using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
 77             {
 78                 conn.Open();
 79                 using (SqlTransaction trans = conn.BeginTransaction())
 80                 {
 81                     try
 82                     {
 83                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
 84                         if (i > 0)
 85                         {
 86                             trans.Commit();
 87                             return i > 0;
 88                         }
 89                         else
 90                         {
 91                             trans.Rollback();
 92                             return false;
 93                         }
 94                     }
 95                     catch (System.Exception e)
 96                     {
 97                         trans.Rollback();
 98                         return false;
 99                         throw e;
100                     }
101                 }
102             }
103            }     
104 
105 
106    }
107           
108 }
View Code

 

 生成代码：

  1 using System.Collections.Generic;
  2 using System.Linq;
  3 using System.Text;
  4 using System.Text.RegularExpressions;
  5 using System.Windows.Forms;
  6 using System.Data;
  7 using CMS.Utilities;
  8 using System.Data.OleDb;
  9 using System.Configuration;
 10 using System.Data.SqlClient;
 11 using System;
 12 namespace MyProject.Entitiese
 13 {
 14 
 15     public class Team_Bizs
 16     {
 17         public class Team_Models
 18         {
 19 
 20             private String _team_code;
 21             public String Team_code
 22             {
 23                 get { return _team_code; }
 24                 set { _team_code = value; }
 25             }
 26 
 27             private String _team_name;
 28             public String Team_name
 29             {
 30                 get { return _team_name; }
 31                 set { _team_name = value; }
 32             }
 33 
 34             private String _team_status;
 35             public String Team_status
 36             {
 37                 get { return _team_status; }
 38                 set { _team_status = value; }
 39             }
 40 
 41             private String _team_user;
 42             public String Team_user
 43             {
 44                 get { return _team_user; }
 45                 set { _team_user = value; }
 46             }
 47 
 48             private DateTime _team_date;
 49             public DateTime Team_date
 50             {
 51                 get { return _team_date; }
 52                 set { _team_date = value; }
 53             }
 54 
 55 
 56         }
 57         public bool InsertTeam_(Team_Models model)
 58         {
 59             string strSql = @"
 60                    INSERT Team_(
 61             Team_code,
 62               Team_name,
 63               Team_status,
 64               Team_user,
 65               Team_date,
 66                  
 67                      )
 68                  VALUES (
 69                   Team_code,
 70                Team_name,
 71                Team_status,
 72                Team_user,
 73                Team_date,
 74                         
 75                      )
 76                      ";
 77             SqlParameter[] parameters = new SqlParameter[]
 78                     {
 79                           new SqlParameter("Team_code,", SqlDbType.NVarChar, 255),
 80                       new SqlParameter("Team_name,", SqlDbType.NVarChar, 255),
 81                       new SqlParameter("Team_status,", SqlDbType.NVarChar, 255),
 82                       new SqlParameter("Team_user,", SqlDbType.NVarChar, 255),
 83                       new SqlParameter("Team_date,", SqlDbType.NVarChar, 255),
 84                            
 85                         };
 86             parameters[0].Value = model.Team_code;
 87             parameters[1].Value = model.Team_name;
 88             parameters[2].Value = model.Team_status;
 89             parameters[3].Value = model.Team_user;
 90             parameters[4].Value = model.Team_date;
 91 
 92             using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
 93             {
 94                 conn.Open();
 95                 using (SqlTransaction trans = conn.BeginTransaction())
 96                 {
 97                     try
 98                     {
 99                         int i = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSql, parameters);
100                         if (i > 0)
101                         {
102                             trans.Commit();
103                             return i > 0;
104                         }
105                         else
106                         {
107                             trans.Rollback();
108                             return false;
109                         }
110                     }
111                     catch (System.Exception e)
112                     {
113                         trans.Rollback();
114                         return false;
115                         throw e;
116                     }
117                 }
118             }
119         }
120 
121 
122     }
123 
124 }
View Code

 

 

  方法与之前一篇文章和上面讲到生成实体的方法差不多，还有update，select，delete 方法也是差不多的，我就不再贴上代码了。

  github地址：https://github.com/Jimmey-Jiang/JWorkHelper稍后推送代码上去。

    懒人小工具1：自动生成Model,Insert,Select,Delete以及导出Excel的方法

 

posted @ 2017-09-22 09:47  云衢  阅读(2930)  评论(5)  编辑  收藏  举报
levels of contents --------------------------------------------------------------------------------------------------------