CodeSmith 使用教程(14) 使用SchemaExplorer来获取数据库定义

╰半橙微兮° 2023-10-16 19:08 13阅读 0赞

在前面例子[CodeSmith 使用教程(3): 自动生成Yii Framework ActiveRecord][CodeSmith _3_ _Yii Framework ActiveRecord] 我们使用了SchemaExplorer 来获取数据的MetaData（数据库Schema 定义）来自动生成Yii Framework的数据库表对应的ActiveRecord定义，本篇较详细的介绍一下的SchemaExplorer的用法，下一篇通过实例除了自动生成自动生成Yii Framework的数据库表对应的ActiveRecord定义外，还自动生成[关联ActiveRecord][ActiveRecord]的关系定义，也就是根据数据库表之间的关系（一对多，一对一，多对多）为ActiveRecord定义relations.

CodeSmith的SchemaExplorer定义在Assembly SchemaExplorer.dll 中，其命名空间为SchemaExplorer ，因此如果需要使用CodeSmith的SchemaExplorer 功能的话，需要添加对SchemaExplorer.dll的引用，如下：

**\[csharp\]** [ view plain][view plain] [copy][view plain] [print][view plain] [?][view plain]

1.  <%@ CodeTemplate Language="C\#" TargetLanguage="Text" Description="List all database tables" %>
2.  <%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema"
3.  Category="Context" Description="Database containing the tables." %>
4.  <%@ Assembly Name="SchemaExplorer" %>
5.  <%@ Import Namespace="SchemaExplorer" %>
6.  Tables in database "<%= SourceDatabase %>":
7.  <% for (int i = 0; i < SourceDatabase.Tables.Count; i++) \{ %>
8.  <%= SourceDatabase.Tables\[i\].Name %>
9.  <% \} %>

<%@ CodeTemplate Language="C#" TargetLanguage="Text" Description="List all database tables" %>
    <%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema"
     Category="Context" Description="Database containing the tables." %>
    <%@ Assembly Name="SchemaExplorer" %>
    <%@ Import Namespace="SchemaExplorer" %>
    Tables in database "<%= SourceDatabase %>":
    <% for (int i = 0; i < SourceDatabase.Tables.Count; i++) { %>
            <%= SourceDatabase.Tables[i].Name %>
    <% } %>

以上代码添加了SchemaExplorer库的引用，并定义了一个属性SourceDatabase，其类型为SchemaExplorer.DatabaseSchema ，在运行这个模板前，必须设置SourceDatabase的值：

[![20130107002][]][20130107002 1]

SourceDatabase属性后显示一个“…”的按钮，表示使用一个附加的专用的编辑器来定义这个属性，点击这个按钮将启动数据库选择对话框：

[![20130107003][]][20130107003 1]

使用这个对象框可以选择已通过Schema Explorer定义过的数据库或者添加新的数据库，通过单击“…”来添加新的数据库定义：

[![20130107004][]][20130107004 1]

如果添加一个新的数据源，SchemaExplorer 打开了 数据源对话库 ，选择合适的数据源类型：

[![20130107006][]][20130107006 1]

CodeSmith缺省支持的数据源类型有很多，包括了常用的ADO， DB2,MySQL，Oracle，PostgreSQL， SQL Server，Sqlite等，也可以自定义新的数据源类型。

本例我们选用SQL Server类型 ，并使用[Chinook示例数据库][Chinook]:

[![20130107007][]][20130107007 1]

选择数据库Chinook，显示结果：

Tables in database "Chinook":
            Album
            Artist
            Customer
            Employee
            Genre
            Invoice
            InvoiceLine
            MediaType
            Playlist
            PlaylistTrack
            Track
    SchemaExplorer 对应数据库的MetaData（表定义，列定义，主键，外键定义等）定义如下的对象模型，可以在代码模板中使用：
    
    上图表示SchemaExplorer 定义了多种对象集合类型，对象类型，比如DatabaseSchema 定义了Commands属性，
    其类型为CommandSchemaCollection，这个集合的每项类型为CommandSchema ，对应到数据库定义中的一个命令。
    通过这个属性可以获取Command的定义等信息。
    
    使用SchemaExplorer 除了可以使用SchemaExplorer.DatabaseSchema类型来定义属性，还可以通过下面四种类型：

*  TableSchema和 TableSchemaCollection
 *  ViewSchema 和 ViewSchemaCollection
 *  CommandSchema 和 CommandSchemaCollection
 *  ColumnSchema 和 ColumnSchemaCollection

分别对应到表类型，视图类型，命令类型，列类型，比如使用

**\[csharp\]** [ view plain][view plain] [copy][view plain] [print][view plain] [?][view plain]

1.  <%@ Property Name="SourceColumns" Type="SchemaExplorer.ColumnSchemaCollection"
2.  Category="Database" Description="Select a set of columns." %>

<%@ Property Name="SourceColumns"  Type="SchemaExplorer.ColumnSchemaCollection"
    Category="Database"  Description="Select a set of columns." %>

选择一个表的多个列（ColumnSchemaCollection）

[![20130107009][]][20130107009 1]

对应这些集合类型（比如TableSchemaCollection，ColumnSchemaCollection）缺省的排序是由数据库决定的，因此可能不是排好序的，如果需要排序的话，可以通过`Sort方法来实现，比如：`

**\[csharp\]** [ view plain][view plain] [copy][view plain] [print][view plain] [?][view plain]

1.  TableSchemaCollection tables = new TableSchemaCollection(SourceDatabase.Tables);
2.  tables.Sort(new PropertyComparer("Name"));

TableSchemaCollection tables = new TableSchemaCollection(SourceDatabase.Tables);
    tables.Sort(new PropertyComparer("Name"));

SQL Server数据库可以对表或列定义一些附加的属性（Extended Property）SchemaExplorer 也提供了方法可以来访问/添加 这些Extended Property。  
比如SQL Server定义一个扩展属性表示某个列是否为Identity列，这可以通过下面代码来获取：

**\[csharp\]** [ view plain][view plain] [copy][view plain] [print][view plain] [?][view plain]

1.  Identity Field = <% foreach(ColumnSchema cs in SourceTable.Columns) \{
2.  if( ((bool)cs.ExtendedProperties\["CS\_IsIdentity"\].Value) == true) \{
3.  Response.Write(cs.Name);
4.  \}
5.  \} %>

Identity Field = <% foreach(ColumnSchema cs in SourceTable.Columns) {
        if( ((bool)cs.ExtendedProperties["CS_IsIdentity"].Value) == true) {
            Response.Write(cs.Name);
        }
    } %>

更好的方法是使用[SchemaExplorer.ExtendedPropertyNames][]类和ExtendedProperty定义的扩展方法。

例如：

**\[csharp\]** [ view plain][view plain] [copy][view plain] [print][view plain] [?][view plain]

1.  Identity Field = <% foreach(ColumnSchema cs in SourceTable.Columns) \{
2.  if(cs.ExtendedProperties.GetByKey<bool>(SchemaExplorer.ExtendedPropertyNames.IsIdentity) == true) \{
3.  Response.Write(cs.Name);
4.  \}
5.  \} %>

Identity Field = <% foreach(ColumnSchema cs in SourceTable.Columns) {
        if(cs.ExtendedProperties.GetByKey<bool>(SchemaExplorer.ExtendedPropertyNames.IsIdentity) == true) {
            Response.Write(cs.Name);
        }
    } %>

CodeSmith缺省支持的扩展属性如下：

##### 表的列 #####

<table style="font:inherit; margin:0px 0px 18px; padding:0px; border:1px solid rgb(221,221,221); vertical-align:baseline; border-collapse:collapse; border-spacing:0px"> 
 <tbody style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline"> 
  <tr style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline"> 
   <th style="font:inherit; margin:0px; padding:0px 10px 10px 0px; border:0px currentColor; vertical-align:baseline"> Extended Property Key</th> 
   <th style="font:inherit; margin:0px; padding:0px 10px 10px 0px; border:0px currentColor; vertical-align:baseline"> SchemaExplorer.ExtendedPropertyName Property Name</th> 
   <th style="font:inherit; margin:0px; padding:0px 10px 10px 0px; border:0px currentColor; vertical-align:baseline"> 描述</th> 
  </tr> 
  <tr style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline; background-color:rgb(249,249,249)"> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> CS_Description</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> Description</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> The Description</td> 
  </tr> 
  <tr style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline"> 
   <td style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> CS_IsRowGuidCol</td> 
   <td style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> IsRowGuidColumn</td> 
   <td style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> The Column is a Row Guid</td> 
  </tr> 
  <tr style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline; background-color:rgb(249,249,249)"> 
   <td style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> CS_IsIdentity</td> 
   <td style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> IsIdentity</td> 
   <td style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> Identity Column</td> 
  </tr> 
  <tr style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline"> 
   <td style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> CS_IsComputed</td> 
   <td style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> IsComputed</td> 
   <td style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> Computed Column or Index</td> 
  </tr> 
  <tr style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline; background-color:rgb(249,249,249)"> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> CS_IsDeterministic</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> IsDeterministic</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> Column is Deterministic</td> 
  </tr> 
  <tr style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline"> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> CS_IdentitySeed</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> IdentitySeed</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> Identity Seed</td> 
  </tr> 
  <tr style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline; background-color:rgb(249,249,249)"> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> CS_IdentityIncrement</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> IdentityIncrement</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> Identity Increment</td> 
  </tr> 
  <tr style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline"> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> CS_SystemType</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> SystemType</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> The System Type (E.G., System.String)</td> 
  </tr> 
  <tr style="font:inherit; margin:0px; padding:0px; border:0px currentColor; vertical-align:baseline; background-color:rgb(249,249,249)"> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> CS_Default</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> DefaultValue</td> 
   <td colspan="1" style="margin:0px; padding:9px 10px; border:currentColor; color:rgb(51,51,51); line-height:18px; font-family:inherit; font-size:undefined; font-style:inherit; font-variant:inherit; font-weight:inherit; vertical-align:top"> The default value</td> 
  </tr> 
 </tbody> 
</table>

##### 视图的列 #####

##### 命令参数 #####

下一篇通过Table的Key（外键和主键）为Yii Framework 表的ActiveRecord添加Relations

[CodeSmith _3_ _Yii Framework ActiveRecord]: http://www.imobilebbs.com/wordpress/archives/4196
[ActiveRecord]: http://www.imobilebbs.com/wordpress/archives/4047
[view plain]: http://blog.csdn.net/mapdigit/article/details/8561623#
[20130107002]: http://www.imobilebbs.com/wordpress/wp-content/uploads/2013/01/20130107002.png
[20130107002 1]: http://www.imobilebbs.com/wordpress/archives/4238/attachment/20130107002
[20130107003]: http://www.imobilebbs.com/wordpress/wp-content/uploads/2013/01/20130107003.png
[20130107003 1]: http://www.imobilebbs.com/wordpress/archives/4238/attachment/20130107003
[20130107004]: http://www.imobilebbs.com/wordpress/wp-content/uploads/2013/01/20130107004.png
[20130107004 1]: http://www.imobilebbs.com/wordpress/archives/4238/attachment/20130107004
[20130107006]: http://www.imobilebbs.com/wordpress/wp-content/uploads/2013/01/20130107006.png
[20130107006 1]: http://www.imobilebbs.com/wordpress/archives/4238/attachment/20130107006
[Chinook]: http://chinookdatabase.codeplex.com/
[20130107007]: http://www.imobilebbs.com/wordpress/wp-content/uploads/2013/01/20130107007.png
[20130107007 1]: http://www.imobilebbs.com/wordpress/archives/4238/attachment/20130107007
[20130107009]: http://www.imobilebbs.com/wordpress/wp-content/uploads/2013/01/20130107009.png
[20130107009 1]: http://www.imobilebbs.com/wordpress/archives/4238/attachment/20130107009
[SchemaExplorer.ExtendedPropertyNames]: http://www.codesmithtools.com/help/##GeneratorAPI.chm/html/5d664b18-7a28-1210-813f-a5f56f92c8e9.htm