使用 CodeSmith 模板引擎，針對 SQL Server 檢視表自動產生 C# 實體類 (Entity/POCO)，包含私有欄位、建構函式、公開屬性，並透過 CommonUtility 類別統一處理 SQL 型別到 C# 型別的映射、駝峰命名轉換、常數宣告生成。本文提供兩個核心模板檔案完整代碼，可直接放入 CodeSmith 專案使用。
BusinessObjectForView.cst：主模板，產生實體類代碼。CommonUtility.cs：共用工具類，型別映射、命名轉換、常數生成邏輯。兩檔置於同一目錄，主模板透過 @ Assembly Src="CommonUtility.cs" 參考工具類。
<%@ CodeTemplate Language="C#" TargetLanguage="C#" Description="Generates entity class for SQL View." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.ViewSchema" Category="Context" Description="View to generate entity for." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Src="CommonUtility.cs" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="Common.Data" %>
using System;
using System.Collections.Generic;
using System.Data;
namespace SOA.Model
{
///
/// Entity class for view '<%=SourceTable.Name%>'.
///
[Serializable]
public class <%=SourceTable.Name%>
{
#region Private Fields
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
private <%=rule.GetCSharpVariableType(SourceTable.Columns[i])%> _<%=SourceTable.Columns[i].Name%>;
<% } %>
#endregion
#region Constructors
public <%=SourceTable.Name%>() {}
public <%=SourceTable.Name%>(
<% for (int i = 0; i < SourceTable.Columns.Count - 1; i++) { %>
<%=rule.GetCSharpVariableType(SourceTable.Columns[i])%> <%=SourceTable.Columns[i].Name%>,
<% } %>
<%=rule.GetCSharpVariableType(SourceTable.Columns[SourceTable.Columns.Count - 1])%> <%=SourceTable.Columns[SourceTable.Columns.Count - 1].Name%>
)
{
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
_<%=SourceTable.Columns[i].Name%> = <%=SourceTable.Columns[i].Name%>;
<% } %>
}
#endregion
#region Public Properties
<% for (int i = 0; i < SourceTable.Columns.Count; i++) { %>
public <%=rule.GetCSharpVariableType(SourceTable.Columns[i])%> <%=rule.GetCamelCaseName(SourceTable.Columns[i].Name)%>
{
get { return _<%=SourceTable.Columns[i].Name%>; }
set { _<%=SourceTable.Columns[i].Name%> = value; }
}
<% } %>
#endregion
}
}
using System;
using System.Data;
using SchemaExplorer;
namespace Common.Data
{
public class CommonUtility
{
// 駝峰命名：首字大寫 (CustomerName)
public string GetCamelCaseName(string str)
{
if (string.IsNullOrEmpty(str)) return str;
return char.ToUpper(str[0]) + str.Substring(1);
}
// SQL DbType → C# 關鍵字型別 (string, int, DateTime, decimal...)
public string GetCSharpVariableType(ViewColumnSchema column)
{
switch (column.DataType)
{
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
return "string";
case DbType.Binary:
return "byte[]";
case DbType.Boolean:
return "bool";
case DbType.Byte:
return "byte";
case DbType.Currency:
case DbType.Decimal:
case DbType.VarNumeric:
return "decimal";
case DbType.Date:
case DbType.DateTime:
return "DateTime";
case DbType.Double:
return "double";
case DbType.Guid:
return "Guid";
case DbType.Int16:
return "short";
case DbType.Int32:
return "int";
case DbType.Int64:
return "long";
case DbType.Object:
return "object";
case DbType.SByte:
return "sbyte";
case DbType.Single:
return "float";
case DbType.Time:
return "TimeSpan";
case DbType.UInt16:
return "ushort";
case DbType.UInt32:
return "uint";
case DbType.UInt64:
return "ulong";
default:
return "object"; // fallback
}
}
// SQL DbType → C# 完整型別名稱 (System.String, System.Int32...)
public string GetCSharpBaseType(ViewColumnSchema column)
{
switch (column.DataType)
{
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
return "System.String";
case DbType.Binary:
return "System.Byte[]";
case DbType.Boolean:
return "System.Boolean";
case DbType.Byte:
return "System.Byte";
case DbType.Currency:
case DbType.Decimal:
case DbType.VarNumeric:
return "System.Decimal";
case DbType.Date:
case DbType.DateTime:
return "System.DateTime";
case DbType.Double:
return "System.Double";
case DbType.Guid:
return "System.Guid";
case DbType.Int16:
return "System.Int16";
case DbType.Int32:
return "System.Int32";
case DbType.Int64:
return "System.Int64";
case DbType.Object:
return "System.Object";
case DbType.SByte:
return "System.SByte";
case DbType.Single:
return "System.Single";
case DbType.Time:
return "System.TimeSpan";
case DbType.UInt16:
return "System.UInt16";
case DbType.UInt32:
return "System.UInt32";
case DbType.UInt64:
return "System.UInt64";
default:
return "System.Object";
}
}
// 產生常數宣告：public const String TABLE_NAME = "TableName";
public string GetMemberConstantDeclarationStatement(string protectionLevel, ViewSchema table)
{
return protectionLevel + " const String " + table.Name.ToUpper() + "_TABLE = \"" + GetCamelCaseName(table.Name) + "\";";
}
public string GetMemberConstantDeclarationStatement(string protectionLevel, ColumnSchema column)
{
return protectionLevel + " const String " + column.Name.ToUpper() + "_FIELD = \"" + GetCamelCaseName(column.Name) + "\";";
}
// 取得檢視表欄位集合
public ViewColumnSchemaCollection GetColumnCollectionByTable(ViewSchema table)
{
return new ViewColumnSchemaCollection(table.Columns);
}
}
}
BusinessObjectForView.cst。SourceTable 選擇目標檢視表。ViewName.cs。ViewSchema 改為 TableSchema，同樣適用。column.Description (MS_Description 擴充屬性) 產生 XML 文件註解。IEntity、IAuditable (CreatedAt/UpdatedBy) 等基礎介面。[Table("ViewName")]、[Key]、[Column("col_name")] 屬性。DatabaseSchema.Views 一次產生所有實體類。CodeSmith 模板化代碼生成能確保實體類與資料庫結構強一致、風格統一、減少手工錯誤，是中大型專案持續交付的基礎設施之一。
