CodeSmith 實戰：解析 DBML 檔案自動生成建表 T-SQL 腳本

背景

大學師弟需從 LINQ to SQL 的 .dbml 反向產生 CREATE TABLE T-SQL。DBML 本質為 XML，解析後映射至實體類，再以 CodeSmith 模板輸出腳本。

實體類定義

public class DBTable
{
    public string TableName { get; set; }
    public List<DBColumn> Columns { get; set; } = new List<DBColumn>();
    public List<DBAssociation> Associations { get; set; } = new List<DBAssociation>();
}

public class DBColumn
{
    public string Name { get; set; }
    public string DBType { get; set; }
    public bool IsPrimaryKey { get; set; }
    public bool IsDbGenerated { get; set; }
    public bool CanBeNull { get; set; }
}

public class DBAssociation
{
    public string Name { get; set; }
    public string ThisKey { get; set; }
    public string OtherKey { get; set; }
    public bool IsForeignKey { get; set; }
}

// CodeSmith 2.0 不支援 Lambda，輔助方法編譯進組件
public static class DBTableHelper
{
    public static DBTable GetAssociationTable(List<DBTable> coll, string assName) =>
        coll.Find(t => t.Associations.Find(a => !a.IsForeignKey && a.Name == assName) != null);
}

DBML XML 解析器

public interface IDbTableCollectionHelper
{
    List<DBTable> Transport(XElement element);
}

public class DbTableCollectionHelper : IDbTableCollectionHelper
{
    public List<DBTable> Transport(XElement element)
    {
        var coll = new List<DBTable>();
        XNamespace ns = "https://schemas.microsoft.com/linqtosql/dbml/2007";

        foreach (var tableElem in element.Elements(ns + "Table"))
        {
            var table = new DBTable { TableName = tableElem.Attribute("Name").Value };

            // Columns
            foreach (var colElem in tableElem.Element(ns + "Type").Elements(ns + "Column"))
            {
                table.Columns.Add(new DBColumn
                {
                    Name = colElem.Attribute("Name")?.Value ?? "",
                    DBType = colElem.Attribute("DbType")?.Value ?? "",
                    IsPrimaryKey = bool.Parse(colElem.Attribute("IsPrimaryKey")?.Value ?? "false"),
                    IsDbGenerated = bool.Parse(colElem.Attribute("IsDbGenerated")?.Value ?? "false"),
                    CanBeNull = bool.Parse(colElem.Attribute("CanBeNull")?.Value ?? "false")
                });
            }

            // Associations
            foreach (var assElem in tableElem.Element(ns + "Type").Elements(ns + "Association"))
            {
                table.Associations.Add(new DBAssociation
                {
                    Name = assElem.Attribute("Name")?.Value ?? "",
                    ThisKey = assElem.Attribute("ThisKey")?.Value ?? "",
                    OtherKey = assElem.Attribute("OtherKey")?.Value ?? "",
                    IsForeignKey = bool.Parse(assElem.Attribute("IsForeignKey")?.Value ?? "false")
                });
            }
            coll.Add(table);
        }
        return coll;
    }
}

CodeSmith 模板

<%@ CodeTemplate Language="C#" TargetLanguage="Text" Debug="False" %>
<%@ Assembly Name="DbmlToTable" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Text" %>
<%@ Import Namespace="System.Collections.Generic" %>
<script runat="template">
private List<DbmlToTable.DBTable> _DbTableCollection;
public List<DbmlToTable.DBTable> DbTableCollection { get => _DbTableCollection; set => _DbTableCollection = value; }

public string GeneratorTableSql(List<DbmlToTable.DBTable> coll)
{
    var sb = new StringBuilder();
    var sbFk = new StringBuilder();

    foreach (var t in coll)
    {
        var cols = new List<string>();
        foreach (var c in t.Columns)
            cols.Add($"{c.Name} {c.DBType} {(c.IsPrimaryKey ? "PRIMARY KEY" : "")}");

        sb.AppendFormat("\r\nCREATE TABLE {0} \r\n(\r\n{1}\r\n)", t.TableName, string.Join(",\r\n", cols));

        foreach (var a in t.Associations.Where(x => x.IsForeignKey))
        {
            var refTable = DbmlToTable.DBTableHelper.GetAssociationTable(coll, a.Name);
            if (refTable != null)
                sbFk.AppendFormat("\r\nALTER TABLE {0} WITH NOCHECK ADD CONSTRAINT {1} FOREIGN KEY({2}) REFERENCES {3} ({4})",
                    t.TableName, "FK_" + a.Name, a.ThisKey, refTable.TableName, a.OtherKey);
        }
    }
    return sb.ToString() + "\r\n" + sbFk.ToString();
}
</script>
<%= this.GeneratorTableSql(_DbTableCollection) %>

主控台呼叫

static void Main()
{
    var helper = new DbTableCollectionHelper();
    var coll = helper.Transport(XElement.Load(@"xxpath\MultipleDocumentDB.dbml"));

    var template = CodeSmithTemplateHelper.CompileTemplate("DBMLToTable.cst", Console.WriteLine);
    if (template != null)
    {
        CodeSmithTemplateHelper.AddPropertyParams(template, new { DbTableCollection = coll });
        string sql = template.RenderToString();
        Console.WriteLine(sql);
        // File.AppendAllText(@"D:\1.sql", sql);
    }
    Console.Read();
}

CodeSmith 模板編譯輔助類

public static class CodeSmithTemplateHelper
{
    public static CodeTemplate CompileTemplate(string name, Action<string> errorWriter)
    {
        var compiler = new CodeTemplateCompiler(name);
        compiler.Compile();
        if (compiler.Errors.Count == 0) return compiler.CreateInstance();
        foreach (var err in compiler.Errors) errorWriter(err.ToString());
        return null;
    }

    public static void AddPropertyParams(CodeTemplate t, object param)
        => AddPropertyParams(t, new NameValueDictionary<object>(param));

    public static void AddPropertyParams(CodeTemplate t, NameValueDictionary<object> dict)
    {
        foreach (var k in dict.Keys) t.SetProperty(k, dict[k]);
    }
}

結語

CodeSmith 本質為「字串模板引擎」，配合實體類與解析器，可快速構建各類代碼生成器。後續規劃遷移至 Microsoft T4 模板。