CodeSmith: Using the SchemaExplorer
page 2 of 4
by Brian Mains
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 23064/ 33

Reflecting on Tables

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.

View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-06-20 8:06:58 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search