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:
Code Block | ||
---|---|---|
| ||
<% 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 at first. When in doubt, use the Insert Content toolbar button or the Edit > Insert Content submenu to insert the appropriate code blocks and write blocks. That way you'll get the syntax straight automatically, and can spend your time worrying about the template's logic.
...
With all of the pieces in place, here's the final template:
Code Block | ||
---|---|---|
| ||
<%@ 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 |
...
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
...
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>i |
Next: Testing the Final Result
...