mysql codesmith_解决CodeSmith无法获取MySQL表及列Description说明注释的方案

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));

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();

}