使用T4模板动态生成NPoco实体类

这是一个妥妥的NPoco类，这是我们在工作开发中，手动去写这个实体类，属实非常心累，字段少无所谓一次两次，数量多了，字段多了，就心态裂开🙄

---

今天分享一下如何使用T4模板生成实体类

Copy
using System;
using NPoco;
using System.ComponentModel.DataAnnotations;

namespace Electric.Domain.Entities 
{
   /// <summary>
/// Represents a T_RepairParts.
/// NOTE: 这个类是从T4模板生成的——你不应该手动修改它。
/// </summary>
[MetadataType(typeof(T_RepairPartsMetadata))]
[PrimaryKey("ID")]
[TableName("[dbo].[T_RepairParts]")]
public class T_RepairParts 
{
	#region ResultColumn
	#endregion
	#region Ignore
	#endregion
	
	private class T_RepairPartsMetadata{
	
		[StringLength(4, ErrorMessage = "{0}不能超过4个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "")]
		[Column("ID")]
		public int Id { get; set; }

		[StringLength(50, ErrorMessage = "{0}不能超过50个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "配件名称")]
		[Column("PartsName")]
		public string PartsName { get; set; }

		[StringLength(100, ErrorMessage = "{0}不能超过100个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "绑定品牌")]
		[Column("RelationBrands")]
		public string RelationBrands { get; set; }

		[StringLength(100, ErrorMessage = "{0}不能超过100个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "关联维修")]
		[Column("RelationMaintainers")]
		public string RelationMaintainers { get; set; }

		[StringLength(100, ErrorMessage = "{0}不能超过100个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "关联车系")]
		[Column("RelationVehicleSeries")]
		public string RelationVehicleSeries { get; set; }

		[StringLength(-1, ErrorMessage = "{0}不能超过-1个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "")]
		[Column("EditorContent")]
		public string EditorContent { get; set; }

		[StringLength(-1, ErrorMessage = "{0}不能超过-1个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "")]
		[Column("BasicDetails")]
		public string BasicDetails { get; set; }

		[StringLength(8, ErrorMessage = "{0}不能超过8个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "创建时间")]
		[Column("CreateTime")]
		public DateTime CreateTime { get; set; }

		
		
		[Display(Name = "修改时间")]
		[Column("Updatetime")]
		public DateTime? Updatetime { get; set; }

		[StringLength(1, ErrorMessage = "{0}不能超过1个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "是否删除")]
		[Column("IsDel")]
		public bool IsDel { get; set; }

		[StringLength(200, ErrorMessage = "{0}不能超过200个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "配件标签")]
		[Column("Tips")]
		public string Tips { get; set; }

		[StringLength(9, ErrorMessage = "{0}不能超过9个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "人工价")]
		[Column("LaborPrice")]
		public decimal LaborPrice { get; set; }

		[StringLength(9, ErrorMessage = "{0}不能超过9个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "配件价")]
		[Column("PartsPrice")]
		public decimal PartsPrice { get; set; }

		[StringLength(9, ErrorMessage = "{0}不能超过9个字符！")]
		[Required(ErrorMessage = "请填写{0}！")]
		[Display(Name = "门店市场价格")]
		[Column("StorePrice")]
		public decimal StorePrice { get; set; }

	}
  }      
}

模板是搬运国外一个大神博客内的代码，下面换个模板是我改后增强的
支持生成字段注释，支持生成字段可空，支持生成字段长度，我并且按照我自己的项目类结构重新弄了一下，原版的T4代码我也分享出来，可以看看

帮助文章： https://www.davidhaney.io/automatically-generate-pocos-from-db-with-t4/

我改后的-T4模板代码

Copy
<#@ template language="C#" hostspecific="true" debug="True" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>

<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Configuration" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #>

<#@ import namespace="System" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
 
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#@ import namespace="System.Configuration" #>
<#@ import namespace="System.Data.SqlClient" #>
<#

    var configurationFileMap = new ExeConfigurationFileMap();
    configurationFileMap.ExeConfigFilename = this.Host.ResolvePath("App.config");
    var config = ConfigurationManager.OpenMappedExeConfiguration(configurationFileMap, ConfigurationUserLevel.None);

    //**********************************************************************************************
    // This T4 generates POCOs from the specified DB and saves them to the specified folder which 
    // is relative to the template's location. One file per table/POCO.
    //**********************************************************************************************
    //****************************
    // DEFINE YOUR VARIABLES HERE
    //****************************
    // The SQL server name or IP
    string sqlServer = config.AppSettings.Settings["sqlServer"].Value;
    // The SQL username
    string sqlLogin = config.AppSettings.Settings["sqlLogin"].Value;
    // The SQL password
    string sqlPassword = config.AppSettings.Settings["sqlPassword"].Value;
    // The SQL database to generate the POCOs for
    string sqlDatabase = config.AppSettings.Settings["sqlDatabase"].Value;
    // The namespace to apply to the generated classes
    string classNamespace = config.AppSettings.Settings["classNamespace"].Value;
    // The destination folder for the generated classes, relative to this file's location.
    string destinationFolder = config.AppSettings.Settings["destinationFolder"].Value;
    // Loop over each table and create a class file!
    Server server = new Server(sqlServer);
    server.ConnectionContext.LoginSecure = false;
    server.ConnectionContext.Login = sqlLogin;
    server.ConnectionContext.Password = sqlPassword;
    server.ConnectionContext.Connect();
    foreach (Table table in server.Databases[sqlDatabase].Tables)
    {
        // Skip sys tables
        if (table.Name.StartsWith("sys"))
        {
            continue;
        }

		string nombreTabla="["+ table.Schema +"].["+table.Name+"]";
		
		//search PK
		String namePK="";
		foreach (Column col in table.Columns)
        {
			if(col.InPrimaryKey){
				namePK=col.Name;
				continue;
			}
		}

		String nameSchema="";
		if(!table.Schema.Equals("dbo")){
			nameSchema = "."+table.Schema;
		}

#>
using System;
using NPoco;
using System.ComponentModel.DataAnnotations;

namespace <#= classNamespace #><#= nameSchema #> 
{
    /// <summary>
    /// Represents a <#= table.Name #>.
    /// NOTE: 这个类是从T4模板生成的——你不应该手动修改它。
    /// </summary>
	[MetadataType(typeof(<#=table.Name + "Metadata"#>))]
	[PrimaryKey("<#= namePK #>")]
	[TableName("<#= nombreTabla #>")]
    public class <#= table.Name #> 
    {
	    #region ResultColumn
        #endregion
        #region Ignore
        #endregion
		
		private class <#=table.Name + "Metadata"#>{
<# 
        // Keep count so we don't whitespace the last property/column
        int columnCount = table.Columns.Count;
        int i = 0;
        // Iterate all columns
        foreach (Column col in table.Columns)
        {
            i++;
            string propertyType = GetNetDataType(col.DataType.Name);
            // If we can't map it, skip it
            if (string.IsNullOrWhiteSpace(propertyType))
            {
                // Skip
                continue;
            }
			string strLength = string.Empty;
			string remark = GetRemark(col.Name,nombreTabla,config.AppSettings);
			string strNullable = string.Empty;
            // Handle nullable columns by making the type nullable
            if (col.Nullable && propertyType != "string")
            {
                propertyType += "?";
            }else{
				strLength = "[StringLength(" + col.DataType.MaximumLength +", ErrorMessage = \"{0}不能超过"+col.DataType.MaximumLength+"个字符！\")]";
			}

            if (!col.Nullable)
            {
			    strNullable = "[Required(ErrorMessage = \"请填写{0}！\")]";
            }

			String nameColumn="Id";
			String nameColumnNPoco=col.Name;

			if(!col.InPrimaryKey){
				nameColumn=FirstCharToUpper(col.Name);
			}
#>
			<#=strLength#>
			<#=strNullable#>
			[Display(Name = "<#=remark#>")]
			[Column("<#= nameColumnNPoco #>")]
			public <#= propertyType #> <#= nameColumn #> { get; set; }

<#
            // Do we insert the space?
            if (i != columnCount)
            {
#>
<#
            }
#>
<#
        }
#>
    }
  }      
}
<#
        // Write new POCO class to its own file
        SaveOutput(table.Name + ".cs", destinationFolder);
    } 
#>
<#+
    public static string GetNetDataType(string sqlDataTypeName)
    {
        switch (sqlDataTypeName.ToLower())
        {
            case "bigint":
                return "Int64";
            case "binary":
            case "image":
            case "varbinary":
                return "byte[]";
            case "bit":
                return "bool";
            case "char":
                return "char";
            case "datetime":
            case "smalldatetime":
                return "DateTime";
            case "decimal":
            case "money":
            case "numeric":
                return "decimal";
            case "float":
                return "double";
            case "int":
                return "int";
            case "nchar":
            case "nvarchar":
            case "text":
            case "varchar":
            case "xml":
                return "string";
            case "real":
                return "single";
            case "smallint":
                return "Int16";
            case "tinyint":
                return "byte";
            case "uniqueidentifier":
                return "Guid";
            default:
                return null;
        }
    }

	//码农disco修改版，支持字段注释
	public static string GetRemark(string name,string table,AppSettingsSection config)
	{
		string result = string.Empty;
        if (table == "[dbo].[T_RepairParts]" && name!="ID")
        {
		 
        }
		string sqlServer = config.Settings["sqlServer"].Value;
		string sqlLogin = config.Settings["sqlLogin"].Value;
		string sqlPassword = config.Settings["sqlPassword"].Value;
		string sqlDatabase = config.Settings["sqlDatabase"].Value;

		string connString = "Server="+ sqlServer +";DataBase="+sqlDatabase+";Uid="+sqlLogin+";Pwd="+sqlPassword;
        SqlConnection conn = new SqlConnection(connString);

        string sql = string.Format(@"							USE electric2014
							SELECT  
A.name AS table_name,  
B.name AS column_name,  
C.value AS column_description  
FROM sys.tables A  
INNER JOIN sys.columns B ON B.object_id = A.object_id  
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id  
WHERE A.name = '{0}'  AND B.name = '{1}' ",table.Replace("dbo","").Replace("[","").Replace("]","").Replace(".",""),name);
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();
        SqlDataReader  dr = cmd.ExecuteReader();
		while(dr.Read())
		{
		   result = dr["column_description"].ToString();
		}
        conn.Close();
		return result;
	}

	public static string FirstCharToUpper(string input)
	{
		if (String.IsNullOrEmpty(input))
			throw new ArgumentException("ARGH!");
		return input.First().ToString().ToUpper() + input.Substring(1);
	}

    void SaveOutput(string outputFileName, string destinationFolder)
    {
        // Write to destination folder
        string templateDirectory = Path.Combine(Path.GetDirectoryName(Host.TemplateFile), destinationFolder);
        string outputFilePath = Path.Combine(templateDirectory, outputFileName);
        File.Delete(outputFilePath);
        File.WriteAllText(outputFilePath, this.GenerationEnvironment.ToString()); 
        // Flush generation
        this.GenerationEnvironment.Remove(0, this.GenerationEnvironment.Length);
    }
#>

所有表生成的类都会写到Export内，这下美滋滋了！🌈

项目源代码下载：github
代码最好拉取Git上的，因为博客的这个代码，可能后面不会再更新了