Everyone hates documentation. Especially we programmers. Documentation takes up quite a bit of time, and tends to be out dated by the time it's finished. ASP.NET and self-documenting code with XML comments have brought us a long way, but this still leaves database documentation out in the cold.

In this article, we'll discuss how to use CodeSmith (*1) to build a template that will create HTML documentation for you. Once you finish up your template you can re-generate your documentation at any time, and often (often referred to as "Active Generation"; the act of re-generating code/documentation at often, scheduled intervals or even part of your build process).

One of CodeSmith's greatest strengths is it's similarity to the syntax used in ASP.NET. Immediately, you'll notice the script blocks (<% %>), template properties and <script> blocks. These should be very familiar to you, and you'll find work in much the same manner as ASP.NET.

The first thing you need to do when creating a CodeSmith template is to put in your CodeTemplate directive. This directive tells the template parser the rules to use when parsing and building your template.

In this case, my template looks like:

 

---

<%@ CodeTemplate Language="C#" TargetLanguage="HTML" 
        Description="Generates a set of HTML-based database documentation." %>

---

 

This particular directive tells the template parser that the template is going to be written in C# (CodeSmith supports any CodeDOM CLR language, but is currently hard-code to support only C#, VB.NET and JScript), and gives a basic description of what your template does. This description is shown as a tool-tip in the Template Explorer window of CodeSmith.

We then need to define any properties that the template will need. Properties are any specific setting that you want users to input when running a template, such as database connection specific settings, specific strings that need to be referenced throughout your template, etc... Properties translate into specific property areas in the Properties window, as well as variables within your script.

For example, here I want to create a property that accepts a string that I'll use as a title within my HTML documentation:

 

---

<%@ Property Name="DocumentationTitle" Type="System.String" Default="Database Documentation" Category="Output" 
        Description="Defines what the title of the HTML page that will be generated will be." %>

---

 

This directive tells the template parser (and template designer) that there is to be a property called DocumentationTitle, it is a System.String, the default value is "Database Documentation", put it in a group called "Output" (used in the Properties window) and gives a description of the property that is to be displayed within the Properties window.

 

*1 = The following article is written using CodeSmith Studio in an upcoming release of  CodeSmith Pro", which can be found in the beta forums at http://www.ericjsmith.net/codesmith/forum/.

Properties allow you to specific a property type. This allows CodeSmith Studio to validate and enforce the type of data you specify in specific strings, as well as hooking specific dialog windows for common types.

For example, instead of having to create properties for each piece of information I would need to build a connection string, I can simply set the property Type to "SchemaExplorer.DatabaseSchema" such as:

 

---

<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" Category="Context" 
        Description="Database that the documentation should be based on." %>

---

 

This creates a property called "SourceDatabase" in the Properties window within CodeSmith Studio. There, a small button is located next to the property value area. Clicking the button brings up a new dialog box titled "Database Picker". These set of dialog boxes allow you to create, edit and delete common connections within your template.

After setting this property properly, this exposes a variable within your template corresponding to your property name, in this case "SourceDatabase". This variable is actually of type "DatabaseSchema", and allows you to navigate through your database schema, gathering any needed information.

For our purposes, we will be making heavy use of these objects, and the objects that it exposes, in order to retrieve specific information such as tables within the database, and columns within each table.

 

Templates are no different then any other ASP.NET page. They require references and imports of any needed assemblies, too.

Since we're using the DatabaseSchema object (remember our previous property), we need to add a reference to the SchemaExplorer assembly, like so:

 

---

<%@ Assembly Name="SchemaExplorer" %>

---

 

And, finally, we'll be referencing the instance of DatabaseSchema throughout our template, so we'll need to import the SchemaExplorer namespace so that we have context to the object:

 

---

<%@ Import Namespace="SchemaExplorer" %>

---

 

Like I had mentioned earlier, one of the greatest strengths of CodeSmith is it's similarity to ASP.NET's style and syntax. In particular, the ability to paste in-line content and then use script blocks (<% %>) to handle any specific template logic and scripting.

In our case, we are going to put the base HTML in-line such as setting up the head, title, body tags, etc... This is ideal for any static content you want within your template.

Outside of that, though, we're going to have quite a bit of logic to navigate through our given database and spit out the respective information.

This "template-side" code must reside within a literal <script> block within the template. Much like ASP.NET, you have to specify <script runat="template"> as well, to make sure the template parser knows to build your given code into the template when it's built and compiled.

For the purposes of my article, we will have four methods. In the following pages we will cover each method in detail.

Within our <script runat="template"> blocks we begin writing our main worker methods for our template. These methods will be compiled within the template, and will maintain context of the template. So, any Response.WriteLine()'s seen in the following will be displayed on render of the template output.

The first method of interest is PrintTableSummary(). This method takes one argument, DatabaseSchema, and builds a column view of all tables within the given database, building anchors down to anchor names within the same page. This allows a large listing of tables, with a quick way to see all tables represented in the documentation and easy navigation between.

Here's the code:

 

---

// How many columns should be displayed in the table summary?
private const int NUM_OF_COLUMNS = 4;

// This method creates a columned display of all tables in the database
// with anchor links down to a detail listing.
public void PrintTableSummary(DatabaseSchema database)
{
 int rowCount = 1;
 int currentRow = 1;
 int numberTablesPerColumn = database.Tables.Count / NUM_OF_COLUMNS;
 bool hasRunOver = ((database.Tables.Count % NUM_OF_COLUMNS == 0) ? false : true);
 
 
 Response.WriteLine("  <tr>");
 for (int i = 0; i < database.Tables.Count; i++)
 {
  // Are we at the end of our column. If so, close the column.
  // Also, take into account if we're at the last column and we need to
  // finish out elements.
  if (rowCount == numberTablesPerRow &&
   (currentRow != NUM_OF_COLUMNS && hasRunOver))
  {
   Response.WriteLine("    </td>");
   currentRow++;
   rowCount = 1;
  }
  
  if (rowCount == 1)
  {
   Response.WriteLine("    <td valign=\"top\">");
  }
  
  Response.WriteLine("      <img src=\"images/tables_icon.gif\" border=\"0\">&nbsp;&nbsp;<a href=\"#" + 
        i + "\">" + database.Tables[i].Name + "</a><br>");
  rowCount++;
 }
 Response.WriteLine("    </td>");
 Response.WriteLine("  </tr>");
}

---

 

This method takes a constant, NUM_OF_COLUMNS, as the driving factor on how many columns should be represented with your table listing. In our case, we're going to use a basic 4 column listing.

At the first of the method you see that we gather some basic calculations. First, calculating (based on our NUM_OF_COLUMNS), about how many elements should be in each column. The next line considers, based on our NUM_OF_COLUMNS and the number of elements per column, if there's any "run off" (any remaining elements outside of our calculated number of elements). This makes sure that no elements are chopped off at the end, and that all elements are represented within our display.

We then go through some basic looping operations, building the table HTML. The only fairly complex logic here is to determine when to start and stop <td>'s (columns). And, note that we display each table's name with an anchor tag pointing to a anchor name in the same page. We base this anchor just on the table's specific index within the collection. The template assumes that if the table is in a certain index here, it will also be in that same index later in code.

The important CodeSmith specifics here to notice are that our DatabaseSchema object contains a property called Tables, which is a collection of TableSchema objects. We then use the TableSchema's Name property to get the actual table name for the respective object.

Next, we'll go through a set of hierarchical methods that traverse all tables within the given database, and then all columns within each table, building and displaying HTML data for each.

Our top-most method, PrintDatabaseDocumentation, takes one parameter, a DatabaseSchema object, and is simply used to "kick off" the process. In our template, the DatabaseSchema object translates back to our first property we set, SourceDatabase. SourceDatabase is just a variable of type DatabaseSchema.

 

---

public void PrintDatabaseDocumentation(DatabaseSchema database)
{
 for (int i = 0; i < database.Tables.Count; i++)
 {
  Response.WriteLine("<table width=\"80%\">");
  Response.WriteLine("  <tr>");
  Response.WriteLine("    <td class=\"tableheaderbar\">");
  Response.WriteLine("      <img src=\"images/tables_icon.gif\" border=\"0\">&nbsp;&nbsp;" + 
database.Tables[i].Name + "<a name=\"" + i + "\">");
  Response.WriteLine("    </td>");
  Response.WriteLine("    <td class=\"tableheaderbar\" align=\"right\" width=\"10%\">");
  Response.WriteLine("      <a href=\"#top\">Back to top</a>");
  Response.WriteLine("    </td>");
  Response.WriteLine("  </tr>");
  
  // Check to see if this table has a description set, and if so display it.
  if (database.Tables[i].ExtendedProperties["CS_Description"].Value != "")
  {
   Response.WriteLine("  <tr>");
   Response.WriteLine("    <td class=\"descriptionBlock\">");
   Response.WriteLine("      <b>Description:</b><br>");
   Response.WriteLine("      " + database.Tables[i].ExtendedProperties["CS_Description"].Value);
   Response.WriteLine("    </td>");
   Response.WriteLine("  </tr>");
  }
  
  
  Response.WriteLine("  <tr>");
  Response.WriteLine("    <td colspan=\"2\">");
  PrintTableDocumentation(database.Tables[i]);
  Response.WriteLine("    </td>");
  Response.WriteLine("  </tr>");
  Response.WriteLine("</table>");
  Response.WriteLine("<br>");
  Response.WriteLine("<br>");
 }
}

---

 

This method simply loops through all tables in the given database, calling PrintTableDocumentation, passing a TableSchema object. It also sets up some basic HTML information, printing out a HTML header with each table's name.

Also note that this template makes use of SQL Server's extended properties for columns and tables, pulling any description that you set within SQL Server to include within your documentation, as well. CodeSmith exposes SQL Server's extended properties as the CodeSmith ExtendedProperty "CS_Description".

---

public void PrintTableDocumentation(TableSchema table)
{ 

 Response.WriteLine("      <table width=\"100%\">");
 Response.WriteLine("        <tr>");
 Response.WriteLine("          <td class=\"columnheaderbar\">");
 Response.WriteLine("   Key");
 Response.WriteLine("          </td>");
 Response.WriteLine("          <td class=\"columnheaderbar\">");
 Response.WriteLine("   ID");
 Response.WriteLine("          </td>");
 Response.WriteLine("          <td class=\"columnheaderbar\">");
 Response.WriteLine("   Name");
 Response.WriteLine("          </td>");
 Response.WriteLine("          <td class=\"columnheaderbar\">");
 Response.WriteLine("   Data Type"); 
 Response.WriteLine("          </td>");
 Response.WriteLine("          <td class=\"columnheaderbar\">");
 Response.WriteLine("   Size(Precision,Scale)");
 Response.WriteLine("          </td>");
 Response.WriteLine("          <td class=\"columnheaderbar\">");
 Response.WriteLine("   Nulls");
 Response.WriteLine("          </td>");
 Response.WriteLine("          <td class=\"columnheaderbar\">");
 Response.WriteLine("   Default"); 
 Response.WriteLine("          </td>");
 Response.WriteLine("        </tr>");
 
 for (int i = 0; i < table.Columns.Count; i++)
 {
   
  PrintColumnDocumentation(table.Columns[i]);
 }
 Response.WriteLine("      </table>");
}

---

 

This method takes a TableSchema object (represented in the .Tables property collection in the earlier method) and builds a HTML header row. It then loops through all columns in the TableSchema object, calling PrintColumnDocumentation() for each.

 

---

public void PrintColumnDocumentation(ColumnSchema column)
{
 Response.WriteLine("        <tr>");
 Response.WriteLine("          <td align=\"center\">");
 
 // Primary key?
 if (column.IsPrimaryKeyMember)
 {
  Response.WriteLine("            <img src=\"images/check_icon.gif\" border=\"0\">");
 }
 else
 {
  Response.WriteLine("            <img src=\"images/unchecked_icon.gif\" border=\"0\">");
 }
 
 Response.WriteLine("          </td>"); 
 Response.WriteLine("          <td align=\"center\">");
 
 // Identity?
 if (column.ExtendedProperties["CS_IsIdentity"].Value == "true")
 {
  Response.WriteLine("            <img src=\"images/check_icon.gif\" border=\"0\">");
 }
 else
 {
  Response.WriteLine("            <img src=\"images/unchecked_icon.gif\" border=\"0\">");
 }
 
 Response.WriteLine("          </td>"); 
 Response.WriteLine("          <td>");
 Response.WriteLine("            " + column.Name);
 Response.WriteLine("          </td>");
 Response.WriteLine("          <td>");
 Response.WriteLine("            " + column.NativeType);
 Response.WriteLine("          </td>"); 
 Response.WriteLine("          <td>");
 Response.WriteLine("            " + column.Size);
 
 if (column.NativeType == "numeric")
 {
  if (column.Precision != 0)
  {
   Response.WriteLine("            (" + column.Precision + ", " + column.Scale + ")");
  }
 }
 
 Response.WriteLine("          </td>"); 
 Response.WriteLine("          <td align=\"center\">");
 
 // Nullable?
 if (column.AllowDBNull)
 {
  Response.WriteLine("            <img src=\"images/check_icon.gif\" border=\"0\">");
 }
 else
 {
  Response.WriteLine("            <img src=\"images/unchecked_icon.gif\" border=\"0\">");
 }

 
 Response.WriteLine("          </td>"); 
 Response.WriteLine("          <td>");
 
 // Does this column have a default value?
 if (column.ExtendedProperties["CS_Default"].Value != "")
 {
  Response.WriteLine("            " + column.ExtendedProperties["CS_Default"].Value);
 }
 
 Response.WriteLine("          </td>"); 
 Response.WriteLine("        </tr>");
 
 if (column.ExtendedProperties["CS_Description"].Value != "")
 {
  Response.WriteLine("        <tr>");
  Response.WriteLine("          <td colspan=\"2\">&nbsp;</td>");
  Response.WriteLine("          <td class=\"descriptionBlock\" colspan=\"5\">");
  Response.WriteLine("            " + column.ExtendedProperties["CS_Description"].Value);
  Response.WriteLine("          </td>");
  Response.WriteLine("        </tr>");
 }
 
}

---

 

And, finally, this is really our workhorse method. This method gets passed a ColumnSchema object and displays the column information for each. The important properties of the ColumnSchema object here are:

.IsPrimaryKeyMember
This property returns a boolean representing if the given column is a member in the primary key of the table.

.ExtendedProperties["CS_IsIdentity"].Value
This extended property (I'll try to cover CodeSmith ExtendedProperties in a later article) simply tells you if the given colum is a identity value. The property will be a string "true" or "false" respective to it's state.

.NativeType
This property tells you what the SQL Server data type of the column is. Not to be confused with the .DataType property, which will give you the .NET equivalent data type of the column.

.AllowDBNull
This property returns a boolean representing if the column allows nulls or not.

.ExtendedProperties["CS_Default"].Value
This extended property tells you what the default value of the column is. If the column has no default value, this extended property will be an empty string ("").

.ExtendedProperties["CS_Description"].ValueThis extended property tells you what the description of the column is, that has been set in SQL Server's extended properties.

Using these properties we output some basic information on each column in a nice HTML format.

Finally, we've finished our CodeSmith template and we're ready to go.

Again, going back to a similar architecture as ASP.NET, CodeSmith uses a similar paradigm of "building" and "running" templates. First, we need to build our new template, and make sure we have no syntax errors, as well as setting up our template properties.

Once your template has built you will see your newly defined properties appear in your Properties window within CodeSmith Studio. Go through and fill out all the required properties. Once complete, simply "run" your template.

Your template output appears in a seperate window tab within CodeSmith studio. Now, simply copy and paste the HTML output into your preferred HTML file (including all of the information from my download), and you're set!

You can see an example of the final output at:

http://www.ngallery.org/articles/dbdocs/

And, you can download the full source, including all dependent collateral to run this template on your own at:

http://www.ngallery.org/getFile.aspx?FileID=12