【翻译】最新版CodeSmith3.2（.net2.0）教程之六：SQL2005数据模板（下）
Posted on 2006-09-29 23:05 seyon 阅读(2210) 评论(5) 编辑 收藏 举报
我们回顾一下上一节的内容：我们对我们要生成的数据模板进行了分析，并生成了静态的部分，接下来我们就来对动态部分进行设置。
1.建立枚举型属性
有时候我们需要用户提供的属性是特定的几种，比如endpoint 的状态只有STARTED, STOPPED, DISABLED三种，而除了这三种以外的都会引起T-SQL错误。如果这些信息都让用户手动输入的话又有可能输入错误的危险，向用户提供一选择菜单让他直接选择里面已经定义好的值就更为保险。而这就是枚举型属性。
为了建立枚举型属性，你需要定义供选择的值。首先点击CodeSmith Studio上方的菜单，选择Edit > Insert Content > Script Block.创建一种新的类型：
<%@ Property Name="InitialState" Type="StateEnum" Category="Options" Default="STARTED" Description="The initial state of the Web service." %>
为了生成基于数据库表的模板，你必须让模板知道你要读取那张表。这就意味着需要通过属性提供表的元数据。而CodeSmith包含了SchemaExplorer 库，包含了一系列与数据库相关的接口。其中一个方式就是：TableSchema，它允许用户从数据库中获取一张表的信息。你可以利用SchemaExplorer库的对象模型获取你需要的数据库和表的信息。下面这个属性就是我们所需要的：<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the Web service will access." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%= SourceTable.Name %>
<%= SourceTable.Database.Name %>
写这个存储过程模板最为灵活的一部分就是编写一个字段名称列表了。同样要利用到SchemaExplorer。TableSchema 对象包含了字段的相关属性。你可以通过将其放在<% %>中，下面就是一个我们建立的一个字段名称列表代码：<% For i As Integer = 0 To SourceTable.Columns.Count -1 %>
<%= SourceTable.Columns(i).Name %><% If i < SourceTable.Columns.Count - 1 Then %>,<% End If %>
<% Next %>
现在你已经完成了模板，你可以在CodeSmith Studio测试结果。首先你需要编译模板，CodeSmith 会弹出属性窗口，让你选择相应属性，填好后，点击生成按钮或者按Ctrl+Shift+B快捷键就可以生成了。假如没有错误的话，你就可以在输出窗口看到输出成功的消息了：
1.建立枚举型属性
有时候我们需要用户提供的属性是特定的几种，比如endpoint 的状态只有STARTED, STOPPED, DISABLED三种，而除了这三种以外的都会引起T-SQL错误。如果这些信息都让用户手动输入的话又有可能输入错误的危险，向用户提供一选择菜单让他直接选择里面已经定义好的值就更为保险。而这就是枚举型属性。
为了建立枚举型属性，你需要定义供选择的值。首先点击CodeSmith Studio上方的菜单，选择Edit > Insert Content > Script Block.创建一种新的类型：
1<script runat="template">
2Public Enum StateEnum
3 STARTED
4 STOPPED
5 DISABLED
6End Enum</script>
<script runat="template">2
Public Enum StateEnum3
STARTED4
STOPPED5
DISABLED6
End Enum</script>现在你就可以使用CodeSmith 直接定义一种属性使用这种新的类型了：
<%@ Property Name="InitialState" Type="StateEnum" Category="Options" Default="STARTED" Description="The initial state of the Web service." %>另外你还需要添加一段代码。因为在后台，.net会把枚举型变量作为整数型来处理，而你准备插入的是字符串。为了完成这个转换，你需要在代码段中添加辅助函数：
1Public Function GetState (ByVal State As StateEnum) As String
2 Select Case State
3 Case StateEnum.STARTED
4 GetState = "STARTED"
5 Case StateEnum.STOPPED
6 GetState = "STOPPED"
7 Case StateEnum.DISABLED
8 GetState = "DISABLED"
9 End Select
10End Function
11
Public Function GetState (ByVal State As StateEnum) As String2
Select Case State3
Case StateEnum.STARTED4
GetState = "STARTED"5
Case StateEnum.STOPPED6
GetState = "STOPPED"7
Case StateEnum.DISABLED8
GetState = "DISABLED"9
End Select10
End Function11
做完这个以后，就可以实现在用户对InitialState 属性进行选择是实现字符串相应，通过在模板中插入 <%= GetState(InitialState) %>实现插入相应选择字符串。接下来你自己完成authentication和port属性的辅助函数，下面是我们的完整模板：
1<%@ CodeTemplate Language="VB" TargetLanguage="T-SQL" Description="Create an HTTP Endpoint." %>
2<%@ Property Name="InitialState" Type="StateEnum" Category="Options" Default="STARTED" Description="The initial state of the Web service." %>
3<%@ Property Name="Authentication" Type="AuthenticationEnum" Category="Options" Default="INTEGRATED" Description="Authentication method." %>
4<%@ Property Name="Port" Type="PortsEnum" Category="Options" Default="CLEAR" Description="Port to use." %>
5CREATE PROC dbo.PersonAddressTypeProc
6AS
7 SELECT
8 AddressTypeID,
9 Name,
10 rowguid,
11 ModifiedDate
12 FROM
13 Person.AddressType
14GO
15CREATE ENDPOINT GetAddressType
16 STATE = <%= GetState(InitialState) %>
17AS HTTP
18(
19 PATH = '/AddressType',
20 AUTHENTICATION = (<%= GetAuthentication(Authentication) %>),
21 PORTS = (<%= GetPort(Port) %>),
22 SITE = 'localhost'
23)
24FOR SOAP
25(
26 WEBMETHOD 'AddressTypeList'
27 (NAME='AdventureWorks.dbo.PersonAddressTypeProc'),
28 BATCHES = DISABLED,
29 WSDL = DEFAULT,
30 DATABASE = 'AdventureWorks',
31 NAMESPACE = 'http://AdventureWorks/AddressType'
32)
33GO
34<script runat="template">
35Public Enum StateEnum
36 STARTED
37 STOPPED
38 DISABLED
39End Enum
40
41Public Enum AuthenticationEnum
42 BASIC
43 DIGEST
44 NTLM
45 KERBEROS
46 INTEGRATED
47End Enum
48
49Public Enum PortsEnum
50 CLEAR
51 SSL
52End Enum
53
54Public Function GetState (ByVal State As StateEnum) As String
55 Select Case State
56 Case StateEnum.STARTED
57 GetState = "STARTED"
58 Case StateEnum.STOPPED
59 GetState = "STOPPED"
60 Case StateEnum.DISABLED
61 GetState = "DISABLED"
62 End Select
63End Function
64
65Public Function GetAuthentication (ByVal Authentication As AuthenticationEnum) As String
66 Select Case Authentication
67 Case AuthenticationEnum.BASIC
68 GetAuthentication = "BASIC"
69 Case AuthenticationEnum.DIGEST
70 GetAuthentication = "DIGEST"
71 Case AuthenticationEnum.NTLM
72 GetAuthentication = "NTLM"
73 Case AuthenticationEnum.KERBEROS
74 GetAuthentication = "KERBEROS"
75 Case AuthenticationEnum.INTEGRATED
76 GetAuthentication = "INTEGRATED"
77 End Select
78End Function
79
80Public Function GetPort (ByVal Port as PortsEnum) As String
81 Select Case Port
82 Case PortsEnum.CLEAR
83 GetPort = "CLEAR"
84 Case PortsEnum.SSL
85 GetPort = "SSL"
86 End Select
87End Function
88</script>
<%@ CodeTemplate Language="VB" TargetLanguage="T-SQL" Description="Create an HTTP Endpoint." %>2
<%@ Property Name="InitialState" Type="StateEnum" Category="Options" Default="STARTED" Description="The initial state of the Web service." %>3
<%@ Property Name="Authentication" Type="AuthenticationEnum" Category="Options" Default="INTEGRATED" Description="Authentication method." %>4
<%@ Property Name="Port" Type="PortsEnum" Category="Options" Default="CLEAR" Description="Port to use." %>5
CREATE PROC dbo.PersonAddressTypeProc6
AS7
SELECT 8
AddressTypeID, 9
Name, 10
rowguid, 11
ModifiedDate12
FROM13
Person.AddressType14
GO15
CREATE ENDPOINT GetAddressType16
STATE = <%= GetState(InitialState) %>17
AS HTTP18
(19
PATH = '/AddressType',20
AUTHENTICATION = (<%= GetAuthentication(Authentication) %>),21
PORTS = (<%= GetPort(Port) %>),22
SITE = 'localhost'23
)24
FOR SOAP25
(26
WEBMETHOD 'AddressTypeList'27
(NAME='AdventureWorks.dbo.PersonAddressTypeProc'),28
BATCHES = DISABLED,29
WSDL = DEFAULT,30
DATABASE = 'AdventureWorks',31
NAMESPACE = 'http://AdventureWorks/AddressType'32
)33
GO34
<script runat="template">35
Public Enum StateEnum36
STARTED37
STOPPED38
DISABLED39
End Enum 40
41
Public Enum AuthenticationEnum42
BASIC43
DIGEST44
NTLM45
KERBEROS46
INTEGRATED47
End Enum 48
49
Public Enum PortsEnum50
CLEAR51
SSL52
End Enum 53
54
Public Function GetState (ByVal State As StateEnum) As String55
Select Case State56
Case StateEnum.STARTED57
GetState = "STARTED"58
Case StateEnum.STOPPED59
GetState = "STOPPED"60
Case StateEnum.DISABLED61
GetState = "DISABLED"62
End Select63
End Function 64
65
Public Function GetAuthentication (ByVal Authentication As AuthenticationEnum) As String66
Select Case Authentication67
Case AuthenticationEnum.BASIC68
GetAuthentication = "BASIC"69
Case AuthenticationEnum.DIGEST70
GetAuthentication = "DIGEST"71
Case AuthenticationEnum.NTLM72
GetAuthentication = "NTLM"73
Case AuthenticationEnum.KERBEROS74
GetAuthentication = "KERBEROS"75
Case AuthenticationEnum.INTEGRATED76
GetAuthentication = "INTEGRATED"77
End Select78
End Function 79
80
Public Function GetPort (ByVal Port as PortsEnum) As String81
Select Case Port82
Case PortsEnum.CLEAR83
GetPort = "CLEAR"84
Case PortsEnum.SSL85
GetPort = "SSL"86
End Select87
End Function88
</script>做好后还有一个问题又出现了：数据库的连接问题。接着往下看：
2.设置SQL属性
为了生成基于数据库表的模板，你必须让模板知道你要读取那张表。这就意味着需要通过属性提供表的元数据。而CodeSmith包含了SchemaExplorer 库，包含了一系列与数据库相关的接口。其中一个方式就是：TableSchema，它允许用户从数据库中获取一张表的信息。你可以利用SchemaExplorer库的对象模型获取你需要的数据库和表的信息。下面这个属性就是我们所需要的：
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the Web service will access." %>CodeSmith 本身并不知道你要使用SchemaExplorer 库的具体类型，所以我们需要告诉它需要加载包含在库里的什么组件。同时导入SchemaExplorer 命名空间也是非常有用的，它可以让我们输入更少的代码（意思是不用每次都输入该命名空间）：
<%@ Assembly Name="SchemaExplorer" %><%@ Import Namespace="SchemaExplorer" %>当用户通过SchemaExplorer选择一张数据库表的时候，TableSchema 会将其组装并返回给CodeSmith。所有这些通过SchemaExplorer 对象模型都变得如此容易。
<%= SourceTable.Name %><%= SourceTable.Database.Name %>你可以把上面读取到的表名称，表所属数据库名称放在你所需要的地方。然后还剩下需要为存储过程中建立一个字段名称列表。
3.编写数据库相关代码
写这个存储过程模板最为灵活的一部分就是编写一个字段名称列表了。同样要利用到SchemaExplorer。TableSchema 对象包含了字段的相关属性。你可以通过将其放在<% %>中，下面就是一个我们建立的一个字段名称列表代码：
<% For i As Integer = 0 To SourceTable.Columns.Count -1 %><%= SourceTable.Columns(i).Name %><% If i < SourceTable.Columns.Count - 1 Then %>,<% End If %><% Next %>在这儿要注意<%%><=%%>的不同，<%%>中间放置执行代码，<=%%>放置计算表达式。你可能会把这两个弄混淆，不过没关系，你可以使用Edit > Insert Content 弹出的子菜单，选择需要的标记，就会自动帮你插入合适的标记。
下面就是我们做的完整的模板代码了：
1 <%@ CodeTemplate Language="VB" TargetLanguage="T-SQL" Debug="True" Description="Create an HTTP Endpoint." %>
2 <%@ Property Name="InitialState" Type="StateEnum" Category="Options" Default="STARTED" Description="The initial state of the Web service." %>
3 <%@ Property Name="Authentication" Type="AuthenticationEnum" Category="Options" Default="INTEGRATED" Description="Authentication method." %>
4 <%@ Property Name="Port" Type="PortsEnum" Category="Options" Default="CLEAR" Description="Port to use." %>
5 <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the Web service will access." %>
6 <%@ Assembly Name="SchemaExplorer" %>
7 <%@ Import Namespace="SchemaExplorer" %>
8 CREATE PROC dbo.<%= SourceTable.Owner %><%= SourceTable.Name %>Proc
9 AS
10 SELECT
11 <% For i As Integer = 0 To SourceTable.Columns.Count -1 %>
12 <%= SourceTable.Columns(i).Name %><% If i < SourceTable.Columns.Count - 1 Then %>,<% End If %>
13 <% Next %>
14 FROM
15 <%= SourceTable.Name %>
16 GO
17 CREATE ENDPOINT Get<%= SourceTable.Name %>
18 STATE = <%= GetState(InitialState) %>
19 AS HTTP
20 (
21 PATH = '/<%= SourceTable.Name %>',
22 AUTHENTICATION = (<%= GetAuthentication(Authentication) %>),
23 PORTS = (<%= GetPort(Port) %>),
24 SITE = 'localhost'
25 )
26 FOR SOAP
27 (
28 WEBMETHOD '<%= SourceTable.Name %>List'
29 (NAME='<%= SourceTable.Database.Name %>.dbo.<%= SourceTable.Owner %><%= SourceTable.Name %>Proc'),
30 BATCHES = DISABLED,
31 WSDL = DEFAULT,
32 DATABASE = '<%= SourceTable.Database.Name %>',
33 NAMESPACE = 'http://<%= SourceTable.Database.Name %>/<%= SourceTable.Name %>'
34 )
35 GO
36 <script runat="template">
37 Public Enum StateEnum
38 STARTED
39 STOPPED
40 DISABLED
41 End Enum
42 Public Enum AuthenticationEnum
43 BASIC
44 DIGEST
45 NTLM
46 KERBEROS
47 INTEGRATED
48 End Enum
49 Public Enum PortsEnum
50 CLEAR
51 SSL
52 End Enum
53
54 Public Function GetState (ByVal State As StateEnum) As String
55 Select Case State
56 Case StateEnum.STARTED
57 GetState = "STARTED"
58 Case StateEnum.STOPPED
59 GetState = "STOPPED"
60 Case StateEnum.DISABLED
61 GetState = "DISABLED"
62 End Select
63 End Function
64
65 Public Function GetAuthentication (ByVal Authentication As AuthenticationEnum) As String
66 Select Case Authentication
67 Case AuthenticationEnum.BASIC
68 GetAuthentication = "BASIC"
69 Case AuthenticationEnum.DIGEST
70 GetAuthentication = "DIGEST"
71 Case AuthenticationEnum.NTLM
72 GetAuthentication = "NTLM"
73 Case AuthenticationEnum.KERBEROS
74 GetAuthentication = "KERBEROS"
75 Case AuthenticationEnum.INTEGRATED
76 GetAuthentication = "INTEGRATED"
77 End Select
78 End Function
79
80 Public Function GetPort (ByVal Port as PortsEnum) As String
81 Select Case Port
82 Case PortsEnum.CLEAR
83 GetPort = "CLEAR"
84 Case PortsEnum.SSL
85 GetPort = "SSL"
86 End Select
87 End Function
88 </script>
2 <%@ Property Name="InitialState" Type="StateEnum" Category="Options" Default="STARTED" Description="The initial state of the Web service." %>
3 <%@ Property Name="Authentication" Type="AuthenticationEnum" Category="Options" Default="INTEGRATED" Description="Authentication method." %>
4 <%@ Property Name="Port" Type="PortsEnum" Category="Options" Default="CLEAR" Description="Port to use." %>
5 <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the Web service will access." %>
6 <%@ Assembly Name="SchemaExplorer" %>
7 <%@ Import Namespace="SchemaExplorer" %>
8 CREATE PROC dbo.<%= SourceTable.Owner %><%= SourceTable.Name %>Proc
9 AS
10 SELECT
11 <% For i As Integer = 0 To SourceTable.Columns.Count -1 %>
12 <%= SourceTable.Columns(i).Name %><% If i < SourceTable.Columns.Count - 1 Then %>,<% End If %>
13 <% Next %>
14 FROM
15 <%= SourceTable.Name %>
16 GO
17 CREATE ENDPOINT Get<%= SourceTable.Name %>
18 STATE = <%= GetState(InitialState) %>
19 AS HTTP
20 (
21 PATH = '/<%= SourceTable.Name %>',
22 AUTHENTICATION = (<%= GetAuthentication(Authentication) %>),
23 PORTS = (<%= GetPort(Port) %>),
24 SITE = 'localhost'
25 )
26 FOR SOAP
27 (
28 WEBMETHOD '<%= SourceTable.Name %>List'
29 (NAME='<%= SourceTable.Database.Name %>.dbo.<%= SourceTable.Owner %><%= SourceTable.Name %>Proc'),
30 BATCHES = DISABLED,
31 WSDL = DEFAULT,
32 DATABASE = '<%= SourceTable.Database.Name %>',
33 NAMESPACE = 'http://<%= SourceTable.Database.Name %>/<%= SourceTable.Name %>'
34 )
35 GO
36 <script runat="template">
37 Public Enum StateEnum
38 STARTED
39 STOPPED
40 DISABLED
41 End Enum
42 Public Enum AuthenticationEnum
43 BASIC
44 DIGEST
45 NTLM
46 KERBEROS
47 INTEGRATED
48 End Enum
49 Public Enum PortsEnum
50 CLEAR
51 SSL
52 End Enum
53
54 Public Function GetState (ByVal State As StateEnum) As String
55 Select Case State
56 Case StateEnum.STARTED
57 GetState = "STARTED"
58 Case StateEnum.STOPPED
59 GetState = "STOPPED"
60 Case StateEnum.DISABLED
61 GetState = "DISABLED"
62 End Select
63 End Function
64
65 Public Function GetAuthentication (ByVal Authentication As AuthenticationEnum) As String
66 Select Case Authentication
67 Case AuthenticationEnum.BASIC
68 GetAuthentication = "BASIC"
69 Case AuthenticationEnum.DIGEST
70 GetAuthentication = "DIGEST"
71 Case AuthenticationEnum.NTLM
72 GetAuthentication = "NTLM"
73 Case AuthenticationEnum.KERBEROS
74 GetAuthentication = "KERBEROS"
75 Case AuthenticationEnum.INTEGRATED
76 GetAuthentication = "INTEGRATED"
77 End Select
78 End Function
79
80 Public Function GetPort (ByVal Port as PortsEnum) As String
81 Select Case Port
82 Case PortsEnum.CLEAR
83 GetPort = "CLEAR"
84 Case PortsEnum.SSL
85 GetPort = "SSL"
86 End Select
87 End Function
88 </script>
4.测试最终结果
现在你已经完成了模板，你可以在CodeSmith Studio测试结果。首先你需要编译模板，CodeSmith 会弹出属性窗口，让你选择相应属性，填好后，点击生成按钮或者按Ctrl+Shift+B快捷键就可以生成了。假如没有错误的话，你就可以在输出窗口看到输出成功的消息了：
---------------- Compile started -----------------
Build complete -- 0 errors, 0 warnings
---------------------- Done ----------------------
Build succeeded
 |
如果模板有任何错误的话，会在输出窗口显示出来。你可以双击错误信息直接撞到有问题的代码行 |
现在你就可以在属性窗口输入模板需要的属性值了。三个枚举型属性你会发现CodeSmith 提供了下拉菜单供你选择。
通过点击数据源属性窗口旁边的三个小点按钮选择数据源属性
你可以选择数据源里已有的数据源，你也可以选择创建一个新的数据源来选择新数据源里的数据表，选好后按选择按钮回到CodeSmith Studio。
当你设置好属性后，点运行按钮或者按F5快捷键运行模板。CodeSmith 会生成相应的代码，并在输出窗口显示，你可以保存或者复制使用了。
