CodeSmith 模板：自動產生 SQL Server View 實體類別 (C#)

專案結構

兩個檔案放置於同一目錄：

1. BusinessObjectForView.cst — 主模板，產生 C# 實體類別
2. CommonUtility.cs — 共用工具類別，提供型別對應、命名轉換等功能

1. BusinessObjectForView.cst (主模板)

<%@ CodeTemplate Language="C#" TargetLanguage="C#" Description="Generates C# entity class for SQL Server View" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.ViewSchema" Category="Context" Description="View to generate entity class for." %>
<%@ Assembly Src="CommonUtility.cs" %>
<%@ Import Namespace="Common.Data" %>
<script runat="template">
    CommonUtility rule = new CommonUtility();
</script>
using System;
using System.Collections.Generic;
using System.Data;

namespace SOA.Model
{
    /// <summary>
    /// <%= SourceTable.Name %> entity class for view '<%= SourceTable.Name %>'.
    /// </summary>
    [Serializable]
    public class <%= SourceTable.Name %>
    {
        #region Private Members
        <% 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
    }
}

2. CommonUtility.cs (共用工具類別)

using System;
using System.Data;
using SchemaExplorer;

namespace Common.Data
{
    public class CommonUtility
    {
        // 取得欄位集合
        public ViewColumnSchemaCollection GetColumnCollectionByTable(ViewSchema table)
        {
            return new ViewColumnSchemaCollection(table.Columns);
        }

        // PascalCase 命名：首字大寫，其餘保持 (CustomerName → CustomerName)
        public string GetCamelCaseName(string str)
        {
            if (string.IsNullOrEmpty(str)) return str;
            return char.ToUpper(str[0]) + str.Substring(1);
        }

        // 常數宣告字串
        public string GetMemberConstantDeclarationStatement(ColumnSchema column)
        {
            return GetMemberConstantDeclarationStatement("public const string ", column);
        }

        public string GetTableConstantDeclarationStatement(ViewSchema table)
        {
            return GetMemberConstantDeclarationStatement("public const string ", table);
        }

        public string GetUpperStatement(ViewSchema table) => table.Name.ToUpper() + "_TABLE";
        public string GetUpperStatement(ColumnSchema column) => column.Name.ToUpper() + "_FIELD";

        public string GetMemberConstantDeclarationStatement(string protectionLevel, ViewSchema table)
        {
            return $"{protectionLevel}{GetUpperStatement(table)} = \"{GetCamelCaseName(table.Name)}\";";
        }

        public string GetMemberConstantDeclarationStatement(string protectionLevel, ColumnSchema column)
        {
            return $"{protectionLevel}{GetUpperStatement(column)} = \"{GetCamelCaseName(column.Name)}\";";
        }

        // SQL DbType → C# 關鍵字型別
        public string GetCSharpVariableType(ViewColumnSchema column)
        {
            return column.DataType switch
            {
                DbType.AnsiString => "string",
                DbType.AnsiStringFixedLength => "string",
                DbType.Binary => "byte[]",
                DbType.Boolean => "bool",
                DbType.Byte => "byte",
                DbType.Currency => "decimal",
                DbType.Date => "DateTime",
                DbType.DateTime => "DateTime",
                DbType.Decimal => "decimal",
                DbType.Double => "double",
                DbType.Guid => "Guid",
                DbType.Int16 => "short",
                DbType.Int32 => "int",
                DbType.Int64 => "long",
                DbType.Object => "object",
                DbType.SByte => "sbyte",
                DbType.Single => "float",
                DbType.String => "string",
                DbType.StringFixedLength => "string",
                DbType.Time => "TimeSpan",
                DbType.UInt16 => "ushort",
                DbType.UInt32 => "uint",
                DbType.UInt64 => "ulong",
                DbType.VarNumeric => "decimal",
                _ => "object"
            };
        }

        // SQL DbType → C# 完整型別名稱
        public string GetCSharpBaseType(ViewColumnSchema column)
        {
            return column.DataType switch
            {
                DbType.AnsiString => "System.String",
                DbType.AnsiStringFixedLength => "System.String",
                DbType.Binary => "System.Byte[]",
                DbType.Boolean => "System.Boolean",
                DbType.Byte => "System.Byte",
                DbType.Currency => "System.Decimal",
                DbType.Date => "System.DateTime",
                DbType.DateTime => "System.DateTime",
                DbType.Decimal => "System.Decimal",
                DbType.Double => "System.Double",
                DbType.Guid => "System.Guid",
                DbType.Int16 => "System.Int16",
                DbType.Int32 => "System.Int32",
                DbType.Int64 => "System.Int64",
                DbType.Object => "System.Object",
                DbType.SByte => "System.SByte",
                DbType.Single => "System.Single",
                DbType.String => "System.String",
                DbType.StringFixedLength => "System.String",
                DbType.Time => "System.TimeSpan",
                DbType.UInt16 => "System.UInt16",
                DbType.UInt32 => "System.UInt32",
                DbType.UInt64 => "System.UInt64",
                DbType.VarNumeric => "System.Decimal",
                _ => "System.Object"
            };
        }
    }
}

使用方式

1. 在 CodeSmith 專案中加入 BusinessObjectForView.cst 為模板。
2. 設定 SourceTable 屬性選擇目標 View。
3. 執行產生程式碼，輸出至專案 SOA.Model 命名空間下。

產出程式碼範例 (假設 View: v_UserSummary)

[Serializable]
public class v_UserSummary
{
    private int _UserId;
    private string _UserName;
    private DateTime _CreateDate;

    public v_UserSummary() { }

    public v_UserSummary(int userId, string userName, DateTime createDate)
    {
        _UserId = userId;
        _UserName = userName;
        _CreateDate = createDate;
    }

    public int UserId { get { return _UserId; } set { _UserId = value; } }
    public string UserName { get { return _UserName; } set { _UserName = value; } }
    public DateTime CreateDate { get { return _CreateDate; } set { _CreateDate = value; } }
}

擴充建議

• 加入 DataAnnotations 屬性 ([Key], [Required], [StringLength]) 支援驗證。
• 實作 INotifyPropertyChanged 支援 WPF/MAUI 雙向繫結。
• 產生 ToString()、Equals/GetHashCode 方便除錯與集合操作。
• 支援 Table/Column Attribute 對應 Entity Framework / Dapper 映射。
• 加入 partial class 關鍵字，方便手工擴充邏輯而不被重新產生覆蓋。

注意事項

1. CodeSmith 需安裝 SchemaExplorer 組件 (隨 CodeSmith 安裝或個別下載)。
2. 連線字串需具備檢視目標 View 結構描述的權限。
3. 產生的類別命名直接使用 View 名稱，若 View 命名不符 C# 慣例 (含空格、特殊字元) 需在模板中加入清理邏輯。
4. CodeSmith 專案建議納入版控，團隊共用模板確保一致性。