CodeSmith uses SchemaExplorer to get database definitions

Use SchemaExplorer to get the database definition

In the previous example, CodeSmith used tutorial (3): Autogenerate Yii Framework ActiveRecord We used SchemaExplorer to get data metaData (database Schema definition) to automatically generate the ActiveRecord definition corresponding to Yii Framework's database table, as described in more detail in this article, I n addition to automatically generating the ActiveRecord definition for the database table that automatically generates Yii Framework, the next example automatically generates the relationship definition associated with ActiveRecord, which defines relations for ActiveRecord based on the relationship between the database tables (one-to-many, one-to-one, many-to-many).

CodeSmith's Schema Explorer is defined in Assembly Schema Explorer .dll and has a namespace of SchemaExplorer, so if you need to use CodeSmith's Schema Explorer feature, you need to add a reference to Schema Explorer.dll as follows:

<%@ 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 %>
<% } %>

The above code adds a reference to the Schema Explorer library and defines a property, SourceDatabase, whose type is SchemaExplorer.DatabaseSchema, which must be set before running this template:

The SourceDatabase property is displayed with a "..." button, indicating that the property is defined using an additional dedicated editor, and clicking on this button will start the database selection dialog box:

Use this object box to select a database that has been defined by Schema Explorer or to add a new database, and to add a new database definition by clicking ...:

If you add a new data source, SchemaExplorer opens the Data Source Conversation Library and selects the appropriate data source type:

CodeSmith supports a number of data source types by default, including commonly used ADO, DB2, MySQL, Oracle, PostgreSQL, SQL Server, Sqlite, and more, as well as customizing new data source types.

In this example, we chose the SQL Server type and used the Chinook sample database :

Select database Chinook to display the results:

Tables in database "Chinook": Album Artist Customer Employee InvoiceLine MediaType Playlist PlaylistTrack Track Track Schema Explorer defines the following object model for the database MetaData (table definition, column definition, primary key, foreign key definition, etc.). , which can be used in code templates:

The figure above shows that SchemaExplorer defines a variety of object collection types, such as database Schema, which defines the Commands property, and the commandSchemaCollection, which is the command Schema of each type of the collection, corresponding to a command in the database definition. This property allows you to obtain information such as the definition of Command.

In addition to defining properties using the SchemaExplorer.DatabaseSchema type, there are four types

• TableSchema and TableSchemaCollection
• ViewSchema and ViewSchemaCollection
• CommandSchema and CommandSchemaCollection
• Column Schema and Column SchemaCollection

Corresponds to table type, view type, command type, column type, for example, use

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

Select multiple columns of a table (Column SchemaCollection)

The default sorting for these collection types (e.g. TableSchemaCollection, Column SchemaCollection) is determined by the database, so it may not be ordered, and if you need to sort, you can do so by sorting, for example:

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

SQL Server databases can define additional properties for tables or columns (Extended Property) SchemaExplorer also provides methods to access/add these Extended Property. SQL Server, for example, defines an extended property to indicate whether a column is an Identity column, which can be obtained by the following code:

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

A better approach is to use extension methods defined by the SchemaExplorer.ExtendedPropertyNames class and ExtendedProperty.

For example:

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

The extended properties supported by CodeSmith by default are as follows:

The column of the table

The column of the view

Command parameters

The next article adds Relations to ActiveRecord of the Yii Framework table through Table's Key (foreign and primary).