2 minute read

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.

Extended Property Editor

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

IncludeFunctions Attribute

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
    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.

Join the mailing list

Get notified of new posts and related content to your inbox. I will never sell you anything and I will NEVER sell your email address.