問題描述:
CodeSmith是現在比較實用的程式碼生成器,但是我們發現一個問題:
使用CodeSmith編寫MySQL模板的時候,會發現一個問題:MySQL資料表中的列說明獲取不到,也就是column.Description。如圖:
我們開啟CodeSmith編寫一個簡單的Model實體類的示例模板如下:
1 <%-- 2 Name: MySQL Model實體模板 3 Author: 孤影[QQ:778078163] 4 Description: CodeSmith連線MySQL生成Model實體模板 5 --%> 6 7 <%@ Template Language="C#" TargetLanguage="C#" ResponseEncoding="UTF-8" %> 8 9 <%@ Assembly Name="SchemaExplorer" %> 10 <%@ Import Namespace="SchemaExplorer" %> 11 12 <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Description="目標資料表" %> 13 <%@ Property Name="ModelNamespace" Type="System.String" Description="Model實體所在的名稱空間" %> 14 15 using System; 16 using System.Collections.Generic; 17 using System.Linq; 18 using System.Text; 19 namespace <%=ModelNamespace %> 20 { 21 /// <summary> 22 /// <%=SourceTable.Description %> 23 /// </summary> 24 public class <%=SourceTable.Name %> 25 { 26 <% 27 // 迴圈遍歷 獲取當前資料表中的所有列 28 foreach(ColumnSchema column in SourceTable.Columns){ 29 Response.WriteLine(string.Format("// {0}",column.Description)); 30 Response.WriteLine(string.Format("public {0} {1} ;",GetCSharpVariableType(column),column.Name)); 31 } 32 %> 33 } 34 } 35 36 <script runat="template"> 37 // 獲取指定列對應的C#資料型別 38 public string GetCSharpVariableType(ColumnSchema column) 39 { 40 if (column.Name.EndsWith("TypeCode")) return column.Name; 41 42 switch (column.DataType) 43 { 44 case DbType.AnsiString: return "string"; 45 case DbType.AnsiStringFixedLength: return "string"; 46 case DbType.Binary: return "byte[]"; 47 case DbType.Boolean: return "bool"; 48 case DbType.Byte: return "byte"; 49 case DbType.Currency: return "decimal"; 50 case DbType.Date: return "DateTime"; 51 case DbType.DateTime: return "DateTime"; 52 case DbType.Decimal: return "decimal"; 53 case DbType.Double: return "double"; 54 case DbType.Guid: return "Guid"; 55 case DbType.Int16: return "short"; 56 case DbType.Int32: return "int"; 57 case DbType.Int64: return "long"; 58 case DbType.Object: return "object"; 59 case DbType.SByte: return "sbyte"; 60 case DbType.Single: return "float"; 61 case DbType.String: return "string"; 62 case DbType.StringFixedLength: return "string"; 63 case DbType.Time: return "TimeSpan"; 64 case DbType.UInt16: return "ushort"; 65 case DbType.UInt32: return "uint"; 66 case DbType.UInt64: return "ulong"; 67 case DbType.VarNumeric: return "decimal"; 68 default: 69 { 70 return "__UNKNOWN__" + column.NativeType; 71 } 72 } 73 } 74 </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新增進去,其與步驟省略。最終修改的程式碼如下:
1 public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) 2 { 3 List<ExtendedProperty> extendedProperties = new List<ExtendedProperty>(); 4 5 if (schemaObject is ColumnSchema) 6 { 7 ColumnSchema columnSchema = schemaObject as ColumnSchema; 8 9 string commandText = string.Format(@"SELECT EXTRA, COLUMN_DEFAULT, COLUMN_TYPE, COLUMN_COMMENT 10 FROM INFORMATION_SCHEMA.COLUMNS 11 WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", 12 columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name); 13 14 using (DbConnection connection = CreateConnection(connectionString)) 15 { 16 connection.Open(); 17 18 DbCommand command = connection.CreateCommand(); 19 command.CommandText = commandText; 20 command.Connection = connection; 21 22 using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) 23 { 24 while (reader.Read()) 25 { 26 string extra = reader.GetString(0).ToLower(); 27 bool columndefaultisnull = reader.IsDBNull(1); 28 string columndefault = ""; 29 if (!columndefaultisnull) 30 { 31 columndefault = reader.GetString(1).ToUpper(); 32 } 33 string columntype = reader.GetString(2).ToUpper(); 34 string columncomment = reader.GetString(3); 35 36 bool isIdentity = (extra.IndexOf("auto_increment") > -1); 37 extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, isIdentity, columnSchema.DataType)); 38 39 if (isIdentity) 40 { 41 /* 42 MySQL auto_increment doesn't work exactly like SQL Server's IDENTITY 43 I believe that auto_increment is equivalent to IDENTITY(1, 1) 44 However, auto_increment behaves differently from IDENTITY when used 45 with multi-column primary keys. See the MySQL Reference Manual for details. 46 */ 47 extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, 1, columnSchema.DataType)); 48 extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, 1, columnSchema.DataType)); 49 } 50 51 extendedProperties.Add(new ExtendedProperty("CS_ColumnDefaultIsNull", columndefaultisnull, DbType.Boolean)); // Added for Backwards Compatibility. 52 extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, columndefault, DbType.String)); 53 extendedProperties.Add(new ExtendedProperty("CS_ColumnDefault", columndefault, DbType.String)); // Added for Backwards Compatibility. 54 extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.SystemType, columntype, DbType.String)); 55 extendedProperties.Add(new ExtendedProperty("CS_ColumnType", columntype, DbType.String)); // Added for Backwards Compatibility. 56 extendedProperties.Add(new ExtendedProperty("CS_ColumnExtra", extra.ToUpper(), DbType.String)); 57 extendedProperties.Add(new ExtendedProperty("CS_Description", columncomment, DbType.String)); 58 } 59 60 if (!reader.IsClosed) 61 reader.Close(); 62 } 63 64 if (connection.State != ConnectionState.Closed) 65 connection.Close(); 66 } 67 } 68 if (schemaObject is TableSchema) 69 { 70 TableSchema tableSchema = schemaObject as TableSchema; 71 string commandText = string.Format(@"SHOW CREATE TABLE `{0}`.`{1}`", tableSchema.Database.Name, tableSchema.Name); 72 73 using (DbConnection connection = CreateConnection(connectionString)) 74 { 75 connection.Open(); 76 77 DbCommand command = connection.CreateCommand(); 78 command.CommandText = commandText; 79 command.Connection = connection; 80 81 using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) 82 { 83 while (reader.Read()) 84 { 85 string createtable = reader.GetString(1); 86 extendedProperties.Add(new ExtendedProperty("CS_CreateTableScript", createtable, DbType.String)); 87 } 88 89 if (!reader.IsClosed) 90 reader.Close(); 91 } 92 93 if (connection.State != ConnectionState.Closed) 94 connection.Close(); 95 } 96 } 97 98 return extendedProperties.ToArray(); 99 }
然後我們F6生成一個修改後的dll元件"SchemaExplorer.MySQLSchemaProvider.dll"。
找到預設的dll:“X:\...\CodeSmith\v7.0\SchemaProviders\SchemaExplorer.MySQLSchemaProvider.dll”,替.....不行,還是先備份一下。。。哈哈
然後替換。開啟重啟CodeSmith,再次生成。。。-_-# 我去!這是在逗我麼。
再次回到Visual Studio中仔細看看整個方法,最後發現。。服了。它的這個方法的判斷邏輯是:
1 public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) 2 3 { 4 5 List<要返回的東西>...... 6 7 if(schemaObject 是一個 ColumnSchema)// 如果是一個列物件 8 9 { 10 11 // 這裡面也就是我們剛剛改的,獲取列說明部分的程式碼 12 13 } 14 15 if(schemaObject 是一個 TableSchema)// 完全沒有注意下面的這個判斷,如果是一個表物件!!! 16 17 { 18 19 // 這裡也就是我們下面要動手腳的地方了。 20 21 } 22 23 }
廢話不多說。直接上這個方法最終的程式碼:
1 public ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject) 2 { 3 List<ExtendedProperty> extendedProperties = new List<ExtendedProperty>(); 4 5 if (schemaObject is ColumnSchema) 6 { 7 ColumnSchema columnSchema = schemaObject as ColumnSchema; 8 9 string commandText = string.Format(@"SELECT EXTRA, COLUMN_DEFAULT, COLUMN_TYPE, COLUMN_COMMENT 10 FROM INFORMATION_SCHEMA.COLUMNS 11 WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", 12 columnSchema.Table.Database.Name, columnSchema.Table.Name, columnSchema.Name); 13 14 using (DbConnection connection = CreateConnection(connectionString)) 15 { 16 connection.Open(); 17 18 DbCommand command = connection.CreateCommand(); 19 command.CommandText = commandText; 20 command.Connection = connection; 21 22 using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) 23 { 24 while (reader.Read()) 25 { 26 string extra = reader.GetString(0).ToLower(); 27 bool columndefaultisnull = reader.IsDBNull(1); 28 string columndefault = ""; 29 if (!columndefaultisnull) 30 { 31 columndefault = reader.GetString(1).ToUpper(); 32 } 33 string columntype = reader.GetString(2).ToUpper(); 34 string columncomment = reader.GetString(3); 35 36 bool isIdentity = (extra.IndexOf("auto_increment") > -1); 37 extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IsIdentity, isIdentity, columnSchema.DataType)); 38 39 if (isIdentity) 40 { 41 /* 42 MySQL auto_increment doesn't work exactly like SQL Server's IDENTITY 43 I believe that auto_increment is equivalent to IDENTITY(1, 1) 44 However, auto_increment behaves differently from IDENTITY when used 45 with multi-column primary keys. See the MySQL Reference Manual for details. 46 */ 47 extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentitySeed, 1, columnSchema.DataType)); 48 extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.IdentityIncrement, 1, columnSchema.DataType)); 49 } 50 51 extendedProperties.Add(new ExtendedProperty("CS_ColumnDefaultIsNull", columndefaultisnull, DbType.Boolean)); // Added for Backwards Compatibility. 52 extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, columndefault, DbType.String)); 53 extendedProperties.Add(new ExtendedProperty("CS_ColumnDefault", columndefault, DbType.String)); // Added for Backwards Compatibility. 54 extendedProperties.Add(new ExtendedProperty(ExtendedPropertyNames.SystemType, columntype, DbType.String)); 55 extendedProperties.Add(new ExtendedProperty("CS_ColumnType", columntype, DbType.String)); // Added for Backwards Compatibility. 56 extendedProperties.Add(new ExtendedProperty("CS_ColumnExtra", extra.ToUpper(), DbType.String)); 57 extendedProperties.Add(new ExtendedProperty("CS_Description", columncomment, DbType.String)); 58 } 59 60 if (!reader.IsClosed) 61 reader.Close(); 62 } 63 64 if (connection.State != ConnectionState.Closed) 65 connection.Close(); 66 } 67 } 68 if (schemaObject is TableSchema) 69 { 70 TableSchema tableSchema = schemaObject as TableSchema; 71 string commandText = string.Format(@"SHOW CREATE TABLE `{0}`.`{1}`", tableSchema.Database.Name, tableSchema.Name); 72 73 using (DbConnection connection = CreateConnection(connectionString)) 74 { 75 connection.Open(); 76 77 DbCommand command = connection.CreateCommand(); 78 command.CommandText = commandText; 79 command.Connection = connection; 80 81 using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) 82 { 83 while (reader.Read()) 84 { 85 string createtable = reader.GetString(1); 86 extendedProperties.Add(new ExtendedProperty("TS_Description", createtable, DbType.String)); 87 int engineIndex = createtable.LastIndexOf("ENGINE"); 88 int commentIndex = createtable.LastIndexOf("COMMENT="); 89 string tableDescription = reader.GetString(0); 90 if (commentIndex > engineIndex) 91 { 92 tableDescription = createtable.Substring(commentIndex + 9).Replace("'", ""); 93 } 94 extendedProperties.Add(new ExtendedProperty("CS_Description", tableDescription, DbType.String)); 95 96 } 97 98 if (!reader.IsClosed) 99 reader.Close(); 100 } 101 102 if (connection.State != ConnectionState.Closed) 103 connection.Close(); 104 } 105 } 106 107 return extendedProperties.ToArray(); 108 }
重新生成,替換。。。重啟CodeSmith,連結MySQL生成。。。。必然果斷Ok:
網上當然也有很多例子,不過都是只處理了列的說明,沒有處理表的說明。
我這個處理表說明是通過擷取已獲得的CreateTableScript裡面的資料,獲取的表說明。
碼字不容易,感覺不錯的話,請不要忘了點贊哦~(*^_^ *)
【本章來自 孤影'Blog:http://www.cnblogs.com/LonelyShadow,碼字不容易,轉載請註明出處。】