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 Generator 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" %> |
| Code Block | ||
|---|---|---|
| ||
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 |
| Code Block | ||
|---|---|---|
| ||
<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> |
| Info |
|---|
| Click here to download the completed template. |
Next: Testing the Final Result