As most people know, a database is sort of a logical container;
although it is setup with a physical file storage, log file, namespace, etc.,
the database from an application point-of-view (or at least from the schema
explorer's view) simply groups tables related together for a single
application. The schema explorer is added to a script by importing the
following assembly.
Listing 1
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
CodeSmith allows the user to determine which database to
connect to. For instance, one of the pre-built scripts for CodeSmith has this
designation.
Listing 2
<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema"
Category="1. Context" %>
The DatabaseSchema object defines a database that is being
connected to and its metadata. In the properties window on the right sidebar,
there is an ellipse next to the SourceDatabase property; clicking it produces
the following.
Figure 1
Petshop is one of the built-in databases with CodeSmith; it
is possible though to select another database by clicking on the ellipse and
selecting the Add option, using the connection mechanism below.
Figure 2
As a personal note, this mechanism requires that the
database is attached. Once this database is connected, the script can make use
of it and generate database-specific code in your template. Let us look at the
next mechanism, the table.
Listing 3
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="1. Context" %>
The table schema selection works the same way; clicking the
ellipse in the properties window produces the following screen.
Figure 3
Selecting a table notes the table name in the properties
window, and the script can make use of it through the SourceTable property. In
the script, the SourceTable property can do some of the following.
Listing 4
<%= this.SourceTable.Owner%>
<%= this.SourceTable.Name %>
(<%= this.SourceTable.Database.Name %>)
Has PK: <%= this.SourceTable.HasPrimaryKey %>
FK Count: <%= this.SourceTable.ForeignKeys.Count %>
The TableSchema object, which is the type that SourceTable
is, renders the owner of the table, the table name, the database's name,
whether it has a primary key and the total number of foreign keys. Not only can
it do this, it also has access to the primary and foreign key columns, the date
it was created, a description, etc.
It also contains a collection of columns, as shown below.
Listing 5
<%= column.Name %>
<%= column.DataType %>
(<%= column.Precision %>, <%=column.Scale %>)
<%= column.AllowDBNull ? "null" : "not null" %>
Is PK: <%= column.IsPrimaryKeyMember %>
Is FK: <%= column.IsForeignKeyMember %>
Is Unique: <%= column.IsUnique %>
As you can see, the ColumnSchema object, which is the column
variable's type, has access to the name, data type, precision, scale,
nullability, primary key status, foreign key status, and unique status. This
makes it very functional in what it can do, along with the additional
parameters not mentioned here.
I have included the above template and the generated output
into a zip file that you can download from the link given below.
