C# 代碼生成器：資料庫表自動映射實體與 CRUD 代碼生成

專案背景

在傳統 ADO.NET 開發中，手寫實體類別與 CRUD 預存程序/參數化 SQL 既繁瑣又易出錯。本文介紹一個基於 VS2005 + SQL Server 2005 的代碼生成器核心邏輯，自動完成：

• 資料庫表結構讀取（表名、欄位、型別、主鍵、Identity）
• C# 實體類別生成（屬性、ToString、Equals/GetHashCode）
• IDAL 介面與具體 DAL 類別生成（增刪改查、分頁、事務支援）
• SQL 參數型別自動映射與防注入參數化

核心模組代碼

1. SQL 型別 ↔ CLR 型別映射

private static string GetClrType(string sqlType)
{
    return sqlType.Trim().ToLower() switch
    {
        "bigint" => "long",
        "binary" => "byte[]",
        "bit" => "bool",
        "char" => "string",
        "datetime" => "DateTime",
        "decimal" => "decimal",
        "float" => "double",
        "image" => "byte[]",
        "int" => "int",
        "money" => "decimal",
        "nchar" => "string",
        "ntext" => "string",
        "nvarchar" => "string",
        "real" => "float",
        "smalldatetime" => "DateTime",
        "smallint" => "short",
        "smallmoney" => "decimal",
        "text" => "string",
        "timestamp" => "byte[]",
        "tinyint" => "byte",
        "uniqueidentifier" => "Guid",
        "varbinary" => "byte[]",
        "varchar" => "string",
        "xml" => "string",
        _ => throw new ArgumentException($"Unsupported SQL type: {sqlType}")
    };
}

private static string GetSqlDbType(string sqlType)
{
    return sqlType.Trim().ToLower() switch
    {
        "bigint" => "SqlDbType.BigInt",
        "bit" => "SqlDbType.Bit",
        "datetime" => "SqlDbType.DateTime",
        "decimal" => "SqlDbType.Decimal",
        "float" => "SqlDbType.Float",
        "int" => "SqlDbType.Int",
        "money" => "SqlDbType.Money",
        "nvarchar" => "SqlDbType.NVarChar",
        "uniqueidentifier" => "SqlDbType.UniqueIdentifier",
        "varchar" => "SqlDbType.VarChar",
        // ... 其他型別
        _ => throw new ArgumentException($"Unknown SqlDbType: {sqlType}")
    };
}

2. 讀取資料庫結構元數據

public List LoadTables(string connStr)
{
    const string sql = @"
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_TYPE = 'BASE TABLE' 
        ORDER BY TABLE_NAME";

    using var conn = new SqlConnection(connStr);
    using var cmd = new SqlCommand(sql, conn);
    conn.Open();
    using var reader = cmd.ExecuteReader();
    
    var tables = new List();
    while (reader.Read())
        tables.Add(new TableSchema { Name = reader.GetString(0) });
    
    return tables;
}

public List LoadColumns(string connStr, string tableName)
{
    const string sql = @"
        SELECT c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH,
               c.NUMERIC_PRECISION, c.NUMERIC_SCALE,
               COLUMNPROPERTY(OBJECT_ID(@Table), c.COLUMN_NAME, 'IsIdentity') AS IsIdentity,
               CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey
        FROM INFORMATION_SCHEMA.COLUMNS c
        LEFT JOIN (
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
            WHERE TABLE_NAME = @Table AND CONSTRAINT_NAME LIKE 'PK%'
        ) pk ON c.COLUMN_NAME = pk.COLUMN_NAME
        WHERE c.TABLE_NAME = @Table
        ORDER BY c.ORDINAL_POSITION";

    using var conn = new SqlConnection(connStr);
    using var cmd = new SqlCommand(sql, conn);
    cmd.Parameters.AddWithValue("@Table", tableName);
    conn.Open();
    using var reader = cmd.ExecuteReader();
    
    var columns = new List();
    while (reader.Read())
    {
        columns.Add(new ColumnSchema
        {
            Name = reader.GetString(0),
            DataType = reader.GetString(1),
            MaxLength = reader.IsDBNull(2) ? 0 : reader.GetInt32(2),
            Precision = reader.IsDBNull(3) ? 0 : reader.GetByte(3),
            Scale = reader.IsDBNull(4) ? 0 : reader.GetByte(4),
            IsIdentity = reader.GetInt32(5) == 1,
            IsPrimaryKey = reader.GetInt32(6) == 1
        });
    }
    return columns;
}

3. 實體類別生成模板

public string GenerateEntityClass(string tableName, List columns)
{
    var sb = new StringBuilder();
    sb.AppendLine("using System;");
    sb.AppendLine($"namespace MyProject.Entities {{");
    sb.AppendLine($"\tpublic partial class {tableName} {{");
    
    // 屬性
    foreach (var col in columns)
    {
        string clrType = GetClrType(col.DataType);
        sb.AppendLine($"\t\tpublic {clrType} {col.Name} {{ get; set; }}");
    }
    
    sb.AppendLine("\t}");
    sb.AppendLine("}");
    return sb.ToString();
}

4. DAL 增刪改查方法生成（以 Insert 為例）

private string GenerateInsertMethod(string tableName, List columns)
{
    var sb = new StringBuilder();
    var nonIdentityCols = columns.Where(c => !c.IsIdentity).ToList();
    
    sb.AppendLine($"\tpublic int Insert({tableName} entity, SqlTransaction tran = null) {{");
    sb.AppendLine($"\t\tconst string sql = @\"INSERT INTO {tableName} (");
    sb.AppendLine($"\t\t{string.Join(", ", nonIdentityCols.Select(c => c.Name))}");
    sb.AppendLine($"\t\t) VALUES (");
    sb.AppendLine($"\t\t{string.Join(", ", nonIdentityCols.Select(c => "@" + c.Name))}");
    sb.AppendLine($"\t\t); SELECT SCOPE_IDENTITY();\";");
    
    sb.AppendLine($"\t\tvar parameters = new SqlParameter[{nonIdentityCols.Count}];");
    for (int i = 0; i < nonIdentityCols.Count; i++)
    {
        var col = nonIdentityCols[i];
        sb.AppendLine($"\t\tparameters[{i}] = new SqlParameter(\"@{col.Name}\", {GetSqlDbType(col.DataType)}) {{ Value = entity.{col.Name} ?? DBNull.Value }};");
        if (col.MaxLength > 0 && (col.DataType.Contains("char") || col.DataType.Contains("varchar")))
            sb.AppendLine($"\t\tparameters[{i}].Size = {col.MaxLength};");
    }
    
    sb.AppendLine($"\t\treturn Convert.ToInt32(SqlHelper.ExecuteScalar(tran, CommandType.Text, sql, parameters));");
    sb.AppendLine($"\t}}");
    
    return sb.ToString();
}

架構設計要點

現代化替代方案

現在專案建議直接採用成熟 ORM：

• Entity Framework Core：Code-First / Database-First，內建遷移、LINQ 查詢、變更追蹤
• Dapper：輕量 Micro-ORM，手寫 SQL、效能極佳、支援多重映射
• SqlSugar / FreeSql：國產 ORM，功能豐富、支援多資料庫、Code-First 友善

總結

代碼生成器在 ORM 不普及時代極大提升了開發效率。理解其核心原理——元數據讀取、型別映射、模板引擎——有助於在現代框架中進行客製化擴充（如自動生成 DTO、Repository、單元測試骨架等）。