CodeSmith: Using the SchemaExplorer
 
Published: 26 Feb 2008
Abstract
The SchemaExplorer is a mechanism to connect to a database and retrieve data about databases, tables, and columns in CodeSmith Studio. In this article, Brian examines how to set it up in a step-by-step way with the help of relevant code snippets.
by Brian Mains
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24991/ 77

Introduction

The CodeSmith studio has an underlying API of components that you can use in your CodeSmith scripts. One of the namespaces is the SchemaExplorer, which allows you to connect to a database and reflect on its tables and columns, and read its metadata. This allows you to write more functional templates. We are going to look at this object and some of its properties in this article.

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.

Downloads

Conclusion

Hopefully, you find that this example will help you understand how functional the SchemaExplorer namespace is, by allowing you to navigate the details of a database, table, and column, and use them to generate your custom output. This makes the CodeSmith Studio a very functional and dynamic tool.



User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





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


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-10-20 1:14:39 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search