How-to: Use Microsoft SQL Server Functions in your CodeSmith Generator Templates

CodeSmith Generator now supports Microsoft SQL ServerFunctions and Microsoft SQL Server CLR Functions with the release of CodeSmith Generator 5.2. I will quickly show you how to add Microsoft SQL Server Function support to your CodeSmith Generator template.

The first thing you need to do in order to use SQL Functions is to set the IncludeFunctions Property on any type that derives from SchemaObjectBase. The following property types are capable of showing functions when you add the IncludeFunctions="True" setting to the property: CommandSchema, CommandSchemaCollection and DatabaseSchema.

<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" Category="Required" Description="Database that contain the stored procedures." IncludeFunctions="True" %>

Now your SQL Functions will show up in SchemaExplorer when you iterate over your commands.

<% foreach (CommandSchema command in SourceDatabase.Commands) { %>
Name: <%= command.Name %>
<% } %>

We have also added five extended properties to the CommandSchema. They are as follows:

1. CS_IsCLR*: Returns true if the command is a CLR Procedure.
2. CS_IsScalarFunction*: Returns true if the command is a Scalar Function.
3. CS_IsTableValuedFunction*: Returns true if the command is a Table-Valued Function.
4. CS_IsInlineTableValuedFunction*: Returns true if the command is a Inline Table-Valued Function.
5. CS_IsMultiStatementTableValuedFunction*: Returns true if the command is a Multi-Statement Table-Valued Function.

A normal stored procedure will always return an int value as the return value. However, a scalar function can return any data type.

The attached template will show you how to get at this information.