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

In a previousarticle, I had mentioned that we added support for Microsoft SQL Server CLR Stored procedures and Functionsin the release of CodeSmith Generator 5.2. Since then there has been a few questions since then on how to add this to your existing templates. The great news is, you can add the functionality in a few easy changes.

To enable SQL function support you need to set IncludeFunctions="True" on any types inheriting from SchemaObjectBase (E.G. CommandSchema, CommandSchemaCollection, DatabaseSchema…).

Once this has been done you will see SQL functions be added to your User Interface Command pickers as well as show up in the API like DatabaseSchema.Commands.

How do I check to see what type of SQL Function it a command is?

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.

It is also easy to take different actions in your template logic based on the type of function.

<% if(!IsTableValuedFunction) { %>
  cmd.CommandText = "[<%= SourceCommand.Owner %>].[<%= SourceCommand.Name %>]";
  cmd.CommandType = CommandType.StoredProcedure;
<% } else {%>
  cmd.CommandText = "SELECT * FROM [<%= SourceCommand.Owner %>].[<%= SourceCommand.Name %>](<%=BuildArgumentList()%>)";
  cmd.CommandType = CommandType.Text;
<%}%>

public bool IsTableValuedFunction
{
  get
  {
    if (SourceCommand == null || !SourceCommand.ExtendedProperties.Contains("CS_IsTableValuedFunction"))
      return false;
    bool temp;
    bool.TryParse(SourceCommand.ExtendedProperties["CS_IsTableValuedFunction"].Value.ToString(), out temp);
    return temp;
  }
}
<%}%>

What templates can I observe these changes in?

We have completely updated the Command wrapper templates to fully support SQL Functions. Please download the latest build of CodeSmith Generator 5.2 and give them a try.