完美解決CodeSmith無法獲取MySQL表及列Description說明註釋的方案

問題描述：

　　CodeSmith是現在比較實用的程式碼生成器，但是我們發現一個問題：

　　使用CodeSmith編寫MySQL模板的時候，會發現一個問題：MySQL資料表中的列說明獲取不到，也就是column.Description。如圖：

 

我們開啟CodeSmith編寫一個簡單的Model實體類的示例模板如下：

<%-- 
Name:           MySQL Model實體模板
Author:         孤影[QQ:778078163]
Description:    CodeSmith連線MySQL生成Model實體模板
--%>

<%@ Template Language="C#" TargetLanguage="C#" ResponseEncoding="UTF-8" %>

<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>

<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Description="目標資料表" %>
<%@ Property Name="ModelNamespace" Type="System.String" Description="Model實體所在的名稱空間" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace <%=ModelNamespace %>
{
    /// <summary>
    /// <%=SourceTable.Description %>
    /// </summary>
    public class <%=SourceTable.Name %>
    {
        <%
            // 迴圈遍歷 獲取當前資料表中的所有列
            foreach(ColumnSchema column in SourceTable.Columns){
                Response.WriteLine(string.Format("// {0}",column.Description));
                Response.WriteLine(string.Format("public {0} {1} ;",GetCSharpVariableType(column),column.Name));
            }
        %>
    }
}

<script runat="template">
// 獲取指定列對應的C#資料型別
public string GetCSharpVariableType(ColumnSchema column)
{
    if (column.Name.EndsWith("TypeCode")) return column.Name;
    
    switch (column.DataType)
    {
        case DbType.AnsiString: return "string";
        case DbType.AnsiStringFixedLength: return "string";
        case DbType.Binary: return "byte[]";
        case DbType.Boolean: return "bool";
        case DbType.Byte: return "byte";
        case DbType.Currency: return "decimal";
        case DbType.Date: return "DateTime";
        case DbType.DateTime: return "DateTime";
        case DbType.Decimal: return "decimal";
        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.String: return "string";
        case DbType.StringFixedLength: return "string";
        case DbType.Time: return "TimeSpan";
        case DbType.UInt16: return "ushort";
        case DbType.UInt32: return "uint";
        case DbType.UInt64: return "ulong";
        case DbType.VarNumeric: return "decimal";
        default:
        {
            return "__UNKNOWN__" + column.NativeType;
        }
    }
}
</script>

 

然後我們點選生成，生成的程式碼如下圖：

 

當然，使用SQL Server及其他資料庫都是可以獲取到的，這是為什麼呢？

 

逼的沒招沒招了的時候，果斷開啟.NET Reflector，看看CodeSmith對SQL Server和MySQL二者，資料表生成操作的時候，有什麼不同的地方，或者有什麼缺少的地方。

CodeSmith中對MySQL操作的DLL元件位置是：“X:\...\CodeSmith\v7.0\SchemaProviders\SchemaExplorer.MySQLSchemaProvider.dll”

 

展開後，開始一個個找裡面的方法，突然發現一個亮點：“GetTableColumns(string connectionString, TableSchema table);”

這個字面的意思不就是獲取列資料麼？開啟看看。。。可惜，裡面只是根據表查詢所有列，並沒有Description相關操作。

 

繼續找，繼續對比。。。最終終於找到問題了：

方法“GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject);”裡面的查詢語句是：

string str = string.Format("SELECT EXTRA, COLUMN_DEFAULT, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema.Table.Database.Name, schema.Table.Name, schema.Name);

這個不就是獲取Column列中的擴充套件屬性的方法麼？！

對比發現，SQL Server的dll裡這個方法的下面，有返回Description，而MySQL正好沒有！

 

二話不說，找到CodeSmith的原始碼包解壓，翻出MySQL的專案：“X:\...\CodeSmith\v7.0\Samples\Samples\Projects\CSharp\MySQLSchemaProvider”

然後開啟Visual Studio載入"MySQLSchemaProvider.csproj"，有很多錯誤，那是因為缺少了引用，新增CodeSmith\bin裡面的相關引用即可。

 

 

需要引用的元件你可以在下面兩個CodeSmith安裝目錄中找到：

“X:\...\CodeSmith\v7.0\bin\”、“X:\...\CodeSmith\v7.0\AddIns\”

 

新增引用之後，錯誤就全部沒了：

 

然後我們果斷開始修改程式碼、首先找到剛剛那個獲取列擴充套件屬性的方法：

“public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject)”

 

然後我們根據觀察SQL Server的程式碼，發現MySQL裡面這個方法：

在SQL語句查詢的時候少查詢了一項資料：“COLUMN_COMMENT”，於是我們首先修改它查詢的SQL語句如下：

string commandText = string.Format(@"SELECT EXTRA, COLUMN_DEFAULT, COLUMN_TYPE, COLUMN_COMMENT
                                                      FROM INFORMATION_SCHEMA.COLUMNS
                                                      WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'",
                                                      columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name);

 

既然上面查詢了，按照正常的資料查詢流程，下面應該遍歷讀取，然後返回吧？

於是繼續看，下面有一個while，正是將上面查詢出來的資料返回的，我們對比SQL Server的程式碼發現：

上面查詢出來的每一項，下面都有獲取返回，而我們剛剛新增的那個“COLUMN_COMMENT”則沒有進行資料獲取、沒有怎麼辦？加唄~

獲取每個資料後，最後統一將封裝在“extendedProperties”中，於是我們也將獲取到的Description新增進去，其與步驟省略。最終修改的程式碼如下：

public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject)
        {
            List<ExtendedProperty> extendedProperties = new List<ExtendedProperty>();

            if (schemaObject is ColumnSchema)
            {
                ColumnSchema columnSchema = schemaObject as ColumnSchema;

                string commandText = string.Format(@"SELECT EXTRA, COLUMN_DEFAULT, COLUMN_TYPE, COLUMN_COMMENT
                                                      FROM INFORMATION_SCHEMA.COLUMNS
                                                      WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'",
                                                      columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name);

                using (DbConnection connection = CreateConnection(connectionString))
                {
                    connection.Open();

                    DbCommand command = connection.CreateCommand();
                    command.CommandText = commandText;
                    command.Connection = connection;

                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            string extra = reader.GetString(0).ToLower();
                            bool columndefaultisnull = reader.IsDBNull(1);
                            string columndefault = "";
                            if (!columndefaultisnull)
                            {
                                columndefault = reader.GetString(1).ToUpper();
                            }
                            string columntype = reader.GetString(2).ToUpper();
                            string columncomment = reader.GetString(3);

                            bool isIdentity = (extra.IndexOf("auto_increment") > -1);
                            extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, isIdentity, columnSchema.DataType));

                            if (isIdentity)
                            {
                                /*
                                MySQL auto_increment doesn't work exactly like SQL Server's IDENTITY
                                I believe that auto_increment is equivalent to IDENTITY(1, 1)
                                However, auto_increment behaves differently from IDENTITY when used
                                with multi-column primary keys.  See the MySQL Reference Manual for details.
                                */
                                extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, 1, columnSchema.DataType));
                                extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, 1, columnSchema.DataType));
                            }

                            extendedProperties.Add(new ExtendedProperty("CS_ColumnDefaultIsNull", columndefaultisnull, DbType.Boolean)); // Added for Backwards Compatibility.
                            extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, columndefault, DbType.String));
                            extendedProperties.Add(new ExtendedProperty("CS_ColumnDefault", columndefault, DbType.String)); // Added for Backwards Compatibility.
                            extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.SystemType, columntype, DbType.String));
                            extendedProperties.Add(new ExtendedProperty("CS_ColumnType", columntype, DbType.String)); // Added for Backwards Compatibility.
                            extendedProperties.Add(new ExtendedProperty("CS_ColumnExtra", extra.ToUpper(), DbType.String));
                            extendedProperties.Add(new ExtendedProperty("CS_Description", columncomment, DbType.String));
                        }

                        if (!reader.IsClosed)
                            reader.Close();
                    }

                    if (connection.State != ConnectionState.Closed)
                        connection.Close();
                }
            }
            if (schemaObject is TableSchema)
            {
                TableSchema tableSchema = schemaObject as TableSchema;
                string commandText = string.Format(@"SHOW CREATE TABLE `{0}`.`{1}`", tableSchema.Database.Name, tableSchema.Name);

                using (DbConnection connection = CreateConnection(connectionString))
                {
                    connection.Open();

                    DbCommand command = connection.CreateCommand();
                    command.CommandText = commandText;
                    command.Connection = connection;

                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            string createtable = reader.GetString(1);
                            extendedProperties.Add(new ExtendedProperty("CS_CreateTableScript", createtable, DbType.String));
                        }

                        if (!reader.IsClosed)
                            reader.Close();
                    }

                    if (connection.State != ConnectionState.Closed)
                        connection.Close();
                }
            }

            return extendedProperties.ToArray();
        }

 

然後我們F6生成一個修改後的dll元件"SchemaExplorer.MySQLSchemaProvider.dll"。

找到預設的dll：“X:\...\CodeSmith\v7.0\SchemaProviders\SchemaExplorer.MySQLSchemaProvider.dll”，替.....不行，還是先備份一下。。。哈哈

 

然後替換。開啟重啟CodeSmith，再次生成。。。-_-#　我去！這是在逗我麼。

 

再次回到Visual Studio中仔細看看整個方法，最後發現。。服了。它的這個方法的判斷邏輯是：

public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject)

{

　　List<要返回的東西>......

　　if(schemaObject 是一個 ColumnSchema)// 如果是一個列物件

　　{

　　　　// 這裡面也就是我們剛剛改的，獲取列說明部分的程式碼

　　}

　　if(schemaObject 是一個 TableSchema)// 完全沒有注意下面的這個判斷，如果是一個表物件！！！

　　{

　　　　// 這裡也就是我們下面要動手腳的地方了。

　　}

}

 

廢話不多說。直接上這個方法最終的程式碼：

        public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject)
        {
            List<ExtendedProperty> extendedProperties = new List<ExtendedProperty>();

            if (schemaObject is ColumnSchema)
            {
                ColumnSchema columnSchema = schemaObject as ColumnSchema;

                string commandText = string.Format(@"SELECT EXTRA, COLUMN_DEFAULT, COLUMN_TYPE, COLUMN_COMMENT
                                                      FROM INFORMATION_SCHEMA.COLUMNS
                                                      WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'",
                                                      columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name);

                using (DbConnection connection = CreateConnection(connectionString))
                {
                    connection.Open();

                    DbCommand command = connection.CreateCommand();
                    command.CommandText = commandText;
                    command.Connection = connection;

                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            string extra = reader.GetString(0).ToLower();
                            bool columndefaultisnull = reader.IsDBNull(1);
                            string columndefault = "";
                            if (!columndefaultisnull)
                            {
                                columndefault = reader.GetString(1).ToUpper();
                            }
                            string columntype = reader.GetString(2).ToUpper();
                            string columncomment = reader.GetString(3);

                            bool isIdentity = (extra.IndexOf("auto_increment") > -1);
                            extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, isIdentity, columnSchema.DataType));

                            if (isIdentity)
                            {
                                /*
                                MySQL auto_increment doesn't work exactly like SQL Server's IDENTITY
                                I believe that auto_increment is equivalent to IDENTITY(1, 1)
                                However, auto_increment behaves differently from IDENTITY when used
                                with multi-column primary keys.  See the MySQL Reference Manual for details.
                                */
                                extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, 1, columnSchema.DataType));
                                extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, 1, columnSchema.DataType));
                            }

                            extendedProperties.Add(new ExtendedProperty("CS_ColumnDefaultIsNull", columndefaultisnull, DbType.Boolean)); // Added for Backwards Compatibility.
                            extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, columndefault, DbType.String));
                            extendedProperties.Add(new ExtendedProperty("CS_ColumnDefault", columndefault, DbType.String)); // Added for Backwards Compatibility.
                            extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.SystemType, columntype, DbType.String));
                            extendedProperties.Add(new ExtendedProperty("CS_ColumnType", columntype, DbType.String)); // Added for Backwards Compatibility.
                            extendedProperties.Add(new ExtendedProperty("CS_ColumnExtra", extra.ToUpper(), DbType.String));
                            extendedProperties.Add(new ExtendedProperty("CS_Description", columncomment, DbType.String));
                        }

                        if (!reader.IsClosed)
                            reader.Close();
                    }

                    if (connection.State != ConnectionState.Closed)
                        connection.Close();
                }
            }
            if (schemaObject is TableSchema)
            {
                TableSchema tableSchema = schemaObject as TableSchema;
                string commandText = string.Format(@"SHOW CREATE TABLE `{0}`.`{1}`", tableSchema.Database.Name, tableSchema.Name);

                using (DbConnection connection = CreateConnection(connectionString))
                {
                    connection.Open();

                    DbCommand command = connection.CreateCommand();
                    command.CommandText = commandText;
                    command.Connection = connection;

                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            string createtable = reader.GetString(1);
                            extendedProperties.Add(new ExtendedProperty("TS_Description", createtable, DbType.String));
                            int engineIndex = createtable.LastIndexOf("ENGINE");
                            int commentIndex = createtable.LastIndexOf("COMMENT=");
                            string tableDescription = reader.GetString(0);
                            if (commentIndex > engineIndex)
                            {
                                tableDescription = createtable.Substring(commentIndex + 9).Replace("'", "");
                            }
                            extendedProperties.Add(new ExtendedProperty("CS_Description", tableDescription, DbType.String));

                        }

                        if (!reader.IsClosed)
                            reader.Close();
                    }

                    if (connection.State != ConnectionState.Closed)
                        connection.Close();
                }
            }

            return extendedProperties.ToArray();
        }

 

重新生成，替換。。。重啟CodeSmith，連結MySQL生成。。。。必然果斷Ok：

 

 

網上當然也有很多例子，不過都是只處理了列的說明，沒有處理表的說明。

我這個處理表說明是通過擷取已獲得的CreateTableScript裡面的資料，獲取的表說明。

 

碼字不容易，感覺不錯的話，請不要忘了點贊哦~(*^_^ *)

 

【本章來自 孤影'Blog：http://www.cnblogs.com/LonelyShadow，碼字不容易，轉載請註明出處。】