Writing the Database Code

The trickiest part of writing this particular template is retrieving the list of column names for the stored procedure definition. Those, too, are available from SchemaExplorer. The TableSchema object contains a Columns collection, which you can iterate through in code. You can place scripting code directly in your template by enclosing it within <% and %> tokens. Here's the code we need to build the list of columns, complete with appropriate commas:

<% For i As Integer = 0 To SourceTable.Columns.Count -1 %>
<%= SourceTable.Columns(i).Name %><% If i < SourceTable.Columns.Count - 1 Then %>,<% End If %>
<% Next %>

Note the difference here between code to execute (surrounded by <% %> tokens), expressions to evaluate (surrounded by <%= %> tokens) and static content to copy to the output (not surrounded at all). You may find keeping all this straight one of the more confusing aspects of working with CodeSmith Generator at first.

With all of the pieces in place, here's the final template:

<%@ CodeTemplate Language="VB" TargetLanguage="T-SQL" Debug="True" Description="Create an HTTP Endpoint." %>
<%@ Property Name="InitialState" Type="StateEnum" Category="Options" Default="STARTED" Description="The initial state of the Web service." %>
<%@ Property Name="Authentication" Type="AuthenticationEnum" Category="Options" Default="INTEGRATED" Description="Authentication method." %>
<%@ Property Name="Port" Type="PortsEnum" Category="Options" Default="CLEAR" Description="Port to use." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the Web service will access." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>

CREATE PROC dbo.<%= SourceTable.Owner %><%= SourceTable.Name %>Proc
AS
    SELECT
        <% For i As Integer = 0 To SourceTable.Columns.Count -1 %>
        <%= SourceTable.Columns(i).Name %><% If i < SourceTable.Columns.Count - 1 Then %>,<% End If %>
        <% Next %>
    FROM
    <%= SourceTable.Name %>
GO
CREATE ENDPOINT Get<%= SourceTable.Name %>
    STATE = <%= GetState(InitialState) %>
AS HTTP
(
    PATH = '/<%= SourceTable.Name %>',
    AUTHENTICATION = (<%= GetAuthentication(Authentication) %>),
    PORTS = (<%= GetPort(Port) %>),
    SITE = 'localhost'
)
FOR SOAP
(
    WEBMETHOD '<%= SourceTable.Name %>List'
        (NAME='<%= SourceTable.Database.Name %>.dbo.<%= SourceTable.Owner %><%= SourceTable.Name %>Proc'),
    BATCHES = DISABLED,
    WSDL = DEFAULT,
    DATABASE = '<%= SourceTable.Database.Name %>',
    NAMESPACE = 'http://<%= SourceTable.Database.Name %>/<%= SourceTable.Name %>'
)
GO

<script runat="template">
Public Enum StateEnum
    STARTED
    STOPPED
    DISABLED
End Enum
Public Enum AuthenticationEnum
    BASIC
    DIGEST
    NTLM
    KERBEROS
    INTEGRATED
End Enum
Public Enum PortsEnum
    CLEAR
    SSL
End Enum

Public Function GetState (ByVal State As StateEnum) As String
    Select Case State
        Case StateEnum.STARTED
            GetState = "STARTED"
        Case StateEnum.STOPPED
            GetState = "STOPPED"
        Case StateEnum.DISABLED
            GetState = "DISABLED"
    End Select
End Function

Public Function GetAuthentication (ByVal Authentication As AuthenticationEnum) As String
    Select Case Authentication
        Case AuthenticationEnum.BASIC
            GetAuthentication = "BASIC"
        Case AuthenticationEnum.DIGEST
            GetAuthentication = "DIGEST"
        Case AuthenticationEnum.NTLM
            GetAuthentication = "NTLM"
        Case AuthenticationEnum.KERBEROS
            GetAuthentication = "KERBEROS"
        Case AuthenticationEnum.INTEGRATED
            GetAuthentication = "INTEGRATED"
    End Select
End Function

Public Function GetPort (ByVal Port as PortsEnum) As String
    Select Case Port
        Case PortsEnum.CLEAR
            GetPort = "CLEAR"
        Case PortsEnum.SSL
            GetPort = "SSL"
    End Select
End Function
</script>

Click here to download the completed template.

Next: Testing the Final Result

More Information:

SchemaExplorer