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\"> " +
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\"> </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.
