December 2009 - Blake Niemyjski

  • How-to: CodeSmith and SQL Functions.

     

    CodeSmith now supports SQL Functions and SQL CLR Functions with the release of CodeSmith 5.2. I will quickly show you how to add SQL Function support to your 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.

windowscoding.com;
Copyright © 2008 Windows Coding
Microsoft and Microsoft logo's are trademarks of Microsoft Corporation.