codesmith mysql 注释_完美解决CodeSmith无法获取MySQL表及列Description说明注释的方案...

问题描述:

CodeSmith是现在比较实用的代码生成器,但是我们发现一个问题:

使用CodeSmith编写MySQL模板的时候,会发现一个问题:MySQL数据表中的列说明获取不到,也就是column.Description。如图:

6da66decf2d5e5c064a306ef59200d25.png

我们打开CodeSmith编写一个简单的Model实体类的示例模板如下:

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

Author: 孤影[QQ:778078163]

Description: CodeSmith连接MySQL生成Model实体模板--%>

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;namespace {///

///

///

public class {

//循环遍历 获取当前数据表中的所有列

foreach(ColumnSchema column inSourceTable.Columns){

Response.WriteLine(string.Format("// {0}",column.Description));

Response.WriteLine(string.Format("public {0} {1} ;",GetCSharpVariableType(column),column.Name));

}%>}

}

//获取指定列对应的C#数据类型

public stringGetCSharpVariableType(ColumnSchema column)

{if (column.Name.EndsWith("TypeCode")) returncolumn.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;

}

}

}一个简单的CodeSmith生成Model实体的模板

View Code

然后我们点击生成,生成的代码如下图:

21be081baeb6ec6caab79cb46ba32dd6.png

当然,使用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里面的相关引用即可。

79f7ff75124e7cd7418588843fcf0516.png

需要引用的组件你可以在下面两个CodeSmith安装目录中找到:

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

添加引用之后,错误就全部没了:

6ff7eeb5f155f8c5ab1aafdca63c64ec.png

然后我们果断开始修改代码、首先找到刚刚那个获取列扩展属性的方法:

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

然后我们根据观察SQL Server的代码,发现MySQL里面这个方法:

820f890d90f47ea93023ae22316b2478.png

在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的代码发现:

565a2e0788b2ac291144a010a60e4df3.png

上面查询出来的每一项,下面都有获取返回,而我们刚刚添加的那个“COLUMN_COMMENT”则没有进行数据获取、没有怎么办?加呗~

获取每个数据后,最后统一将封装在“extendedProperties”中,于是我们也将获取到的Description添加进去,其与步骤省略。最终修改的代码如下:

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

public ExtendedProperty[] GetExtendedProperties(stringconnectionString, SchemaObjectBase schemaObject)

{

List extendedProperties = new List();if (schemaObject isColumnSchema)

{

ColumnSchema columnSchema= schemaObject asColumnSchema;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(newExtendedProperty(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(newExtendedProperty(ExtendedPropertyNames.DefaultValue, columndefault, DbType.String));

extendedProperties.Add(new ExtendedProperty("CS_ColumnDefault", columndefault, DbType.String)); //Added for Backwards Compatibility.

extendedProperties.Add(newExtendedProperty(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 isTableSchema)

{

TableSchema tableSchema= schemaObject asTableSchema;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();

}

}returnextendedProperties.ToArray();

}

最终修改完成的“GetExtendedProperties”方法

View Code

然后我们F6生成一个修改后的dll组件"SchemaExplorer.MySQLSchemaProvider.dll"。

找到默认的dll:“X:\...\CodeSmith\v7.0\SchemaProviders\SchemaExplorer.MySQLSchemaProvider.dll”,替.....不行,还是先备份一下。。。哈哈

然后替换。打开重启CodeSmith,再次生成。。。-_-# 我去!这是在逗我么。

985d981f6e2d2a33dc0f2230b21d95a9.png

再次回到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 }

废话不多说。直接上这个方法最终的代码:

8f900a89c6347c561fdf2122f13be562.png

961ddebeb323a10fe0623af514929fc1.png

public ExtendedProperty[] GetExtendedProperties(stringconnectionString, SchemaObjectBase schemaObject)

{

List extendedProperties = new List();if (schemaObject isColumnSchema)

{

ColumnSchema columnSchema= schemaObject asColumnSchema;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(newExtendedProperty(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(newExtendedProperty(ExtendedPropertyNames.DefaultValue, columndefault, DbType.String));

extendedProperties.Add(new ExtendedProperty("CS_ColumnDefault", columndefault, DbType.String)); //Added for Backwards Compatibility.

extendedProperties.Add(newExtendedProperty(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 isTableSchema)

{

TableSchema tableSchema= schemaObject asTableSchema;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();

}

}returnextendedProperties.ToArray();

}

最终的“GetExtendedProperties”方法

View Code

重新生成,替换。。。重启CodeSmith,链接MySQL生成。。。。必然果断Ok:

7ab4099c34701ed7cbdd40d79a2b4c91.png

网上当然也有很多例子,不过都是只处理了列的说明,没有处理表的说明。

我这个处理表说明是通过截取已获得的CreateTableScript里面的数据,获取的表说明。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值