使用T4模板动态生成NPoco实体类
这是一个妥妥的NPoco类，这是我们在工作开发中，手动去写这个实体类，属实非常心累，字段少无所谓一次两次，数量多了，字段多了，就心态裂开🙄
今天分享一下如何使用T4模板生成实体类
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模板代码
<#@ 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上的，因为博客的这个代码，可能后面不会再更新了
