Working With Database Metadata

Download Source - 1 KB

In this sample we will work directly with the database schema.
CodeSmith enables this scenario through the use of the SchemaExplorer assembly, which provides types for working directly with SQL Server or ADO data as well as designers that can be used to access those types from CodeSmith.

We will use CodeSmith to generate a stored procedure based on any given table.

We will generate two types of stored procedures; the first will return all the field of a given table, and the second will return only the row for any given key.
We defined enumeration for the two types, an enumration is defined between script block

<script runat="template"> </script>

<script runat="template"> public enum StoredProcEnum {      GetAll,     GetByPrimaryKey, } </script>

And then we can use a CodeSmith Property directive to define a property that makes use of the new type.

<%@ Property Name="StoredProcType" Type="StoredProcEnum" Default="GetAll" Optional="False" Category="Options" Description="Determine the type of the stored procedure" %>

CodeSmith itself doesn't have any special knowledge of the types in the SchemaExplorer library, so we need to tell it to load the assembly containing the library.
It's also useful to import the SchemaExplorer namespace to keep the amount of typing we have to do to a minimum:

<%@ Assembly Name="SchemaExplorer" %> <%@ Import Namespace="SchemaExplorer" %>

Then we define a Property directive that allows the user to pick a table from a database.
This property of type TableSchema.

<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Optional="False" Category="Context" Description="Table that the stored procedure will access."  %>

Now we can use this property to retrive ant information about the table and the database.

<%= SourceTable.Name %> <%= SourceTable.Owner %> <%= SourceTable.Database.Name %>

The last thing we need to build a function that transform table column native type to is corresponding SQL type.
A function id defined between script block.

<script runat="template"> </script>

<script runat="template"> public string GetVariableType(ColumnSchema column) {      switch (column.DataType)       {            case DbType.AnsiString: return "varchar("+column.Size+")";            case DbType.AnsiStringFixedLength: return "char ("+column.Size+")";            case DbType.StringFixedLength: return "nchar("+column.Size+")";            case DbType.String: return "nvarchar ("+column.Size+")";            case DbType.Binary: return "binary("+column.Size+")";            case DbType.Boolean: return "bit";            case DbType.Currency: return "money";            case DbType.Date: return "datetime";            case DbType.DateTime: return "datetime";            case DbType.Decimal: return "numeric";            case DbType.Double: return "float";            case DbType.Guid: return "uniqueidentifier";            case DbType.Int16: return "smallint";            case DbType.Int32: return "int";            case DbType.Int64: return "bigint";            case DbType.Byte: return "tinyint";            case DbType.Single: return "real";            case DbType.Time: return "datetime";            default:            {                 return "__UNKNOWN__"+ column.NativeType;            }       } } </script>

And then we can use this fuction iside a code block.

<%= GetVariableType( SourceTable.PrimaryKey.MemberColumns[i] )%>