[C#.NET] 動態產生 AS400 對應的 POCO/DTO Part2

2016-06-12

1782
0
AS400

續上篇：https://dotblogs.com.tw/yc421206/2015/12/30/linqpad_generate_poco_for_as400

原本的功能稍有不足，我將它能稍作些強化，添加了類別和欄位的說明註解(XML)，讓類別看起來更好一些，最後的結果如下圖

開發環境

Winodws 10 Enterprise x64 CHT
IBM i Access for Windows V7R1，IBM DB2 for i .NET Provider
VS 2015 Enterprise Update2

取得資料表說明

我需要用他來轉換成類別的註解

透過 qsys2.systables 取得資料表說明： select * from qsys2.systables

取得欄位相關狀態：cmd.ExecuteReader(CommandBehavior.KeyInfo)

取得說明定義：reader["TABLE_TEXT"]

範例程式碼如下：

public static string GetTableText(this IDbConnection connection, string libraryName, string tableName)
{
    var result = string.Empty;
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    var queryString = string.Format(@"select *
from qsys2.systables where
table_schema like '{0}' and
table_Name like '{1}'
", libraryName, tableName);

    var cmd = connection.CreateCommand();
    cmd.CommandText = queryString;
    //var reader = cmd.ExecuteReader();
    var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
    reader.Read();
    if (reader["TABLE_TEXT"] == DBNull.Value)
    {
        return result;
    }
    result = reader["TABLE_TEXT"].ToString().Trim();

    return result;
}

取得欄位說明

我需要用他來轉成欄位的註解

取得欄位說明：cmd.ExecuteReader(CommandBehavior.KeyInfo)

範例程式碼如下：

public static DataTable GetSchema(this IDbConnection connection, string libraryName, string tableName)
{
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }
    var result = new DataTable("Schema");
    var queryString = string.Format("SELECT * FROM {0}.{1}", libraryName.Trim(), tableName.Trim());
    var cmd = connection.CreateCommand();
    cmd.CommandText = queryString;

    var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
    result = reader.GetSchemaTable();
    return result;
}

最後把註解加上去，範例程式碼如下：

public static string DumpClass(this IDbConnection connection, string libraryName, string tableName)
{
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    var classDescription = connection.GetTableText(libraryName, tableName);
    var classSchema = connection.GetSchema(libraryName, tableName);

    var builder = new StringBuilder();

    builder.AppendFormat("/// <summary>\r\n/// {0}\r\n/// </summary>{1}", classDescription, Environment.NewLine);
    builder.AppendFormat("public class {0}{1}", tableName, Environment.NewLine);

    builder.AppendLine("{");

    foreach (DataRow row in classSchema.Rows)
    {
        var type = (Type)row["DataType"];
        var name = TypeAliases.ContainsKey(type) ? TypeAliases[type] : type.Name;
        var isNullable = (bool)row["AllowDBNull"] && NullableTypes.Contains(type);
        var collumnName = row["ColumnName"].ToString().Trim();
        var columnHeading = row["ColumnHeading"].ToString().Trim();
        builder.AppendFormat("\t/// <summary>\r\n\t/// {3}\r\n\t/// </summary>\r\n\tpublic {0}{1} {2} {{ get; set; }}", name, isNullable ? "?" : string.Empty, collumnName, columnHeading);

        builder.AppendLine();
        builder.AppendLine();
    }

    builder.AppendLine("}");
    builder.AppendLine();

    return builder.ToString();
}

我做了一個簡單的 GUI，最後產出的結果就會像下圖

若有謬誤，煩請告知，新手發帖請多包涵

Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET

回首頁

余小章 @ 大內殿堂

祕訣無它，唯勤而已;唯有不斷學習，才能成長 by 余小章