4.3 使用自定义类型
Microsoft SQL Server除了向用户提供了自定义数据类型的功能之外，还提供了一种新的名为自定义类型的功能。数据库开发人员可以使用自定义类型提供的功能，通过Visual Studio .NET等高级语言来设计自定义的数据类型。实际上，自定义类型与自定义数据类型的区别并不是很大，只是使用自定义数据类型来设计一个数据类型时，必须也只能基于SQL Server提供的默认数据类型。而使用自定义类型则可以根据需要设计出结构更加复杂的自定义数据类型。换句话说，开发人员可以使用高级语言根据应用需求设计一个类(如使用C#语言设计一个引用型数据类型)或结构(如使用Visual Basic.NET设计一个数值型数据类型)，然后将设计的类或结构作为一种自定义数据类型应用到数据表的字段设计中。需要注意的是，SQL Server使用“用户定义类型”，而不是“用户定义数据类型”来保存自定义类型。
自定义类型的实现非常复杂，好在采用Visual Studio .NET开发环境开发CLR自定义类型时，开发环境已经为用户生成了基本框架。通过Visual Studio开发设计一个自定义数据类型时，其基础数据结构继承自.NET提供的命名空间System.ValueType，而基础类则继承于System.Object。
下面通过一个示例来解释自定义类型的创建和使用方法。
【示例5】编程中自定义类型的创建和使用方法。
上面的示例数据表Account中包含一个名为AccountType的字段，该字段用于表示账户的类权限级别。假如“1”表示“数据管理员”；“2”表示“数据输入员”；“3”表示“一般用户”。为了开发数据库应用程序时方便对该字段的操作，为该字段定义一个名为UserDefinedType的自定义类型。具体设计过程如下。
(1) 打开Microsoft Visual Studio 2005，单击【新建项目】链接，打开【新建项目】对话框。在【项目类型】树中依次选择Visual Basic|【数据库】节点，然后在【新建项目】对话框右部的【模板】中选择【SQL Server项目】。在对话框下部的【名称】文本框中输入项目的名称(本示例将其命名为CLRUDDTSample)，在【位置】文本框中输入创建项目的位置，单击【确定】按钮创建一个新的SQL Server项目，如图4.19所示。
 |
| 图4.19 创建一个新的SQL Server项目 |
(2) Microsoft Visual Studio 2005在创建一个新的SQL Server项目之前会弹出一个名为【新建数据库引用】对话框，如图4.20所示。在该对话框中，需要填写连接数据库服务器的相关配置信息，本示例中的相关配置如图4.20所示，在配置完相关信息后，单击【测试连接】按钮，如果配置信息正确，那么测试时将提示连接成功。
 |
| 图4.20 【新建数据库引用】对话框 |
(3) 在测试连接成功后，单击【确定】按钮，Microsoft Visual Studio.NET会弹出一个对话框，询问用户是否需要启用SQL/CLR调试功能，如图4.21所示。单击【是】按钮关闭该对话框，创建名为CLRUDDTSample的项目。
 |
| 图4.21 是否需要启用SQL/CLR调试功能 |
(4) 打开【解决方案资源管理器】，右击项目CLRUDDTSample，从弹出的快捷菜单中依次选择【添加】|【新建项目】命令，打开【添加新项】对话框。选择该对话框中的【用户定义的类型】选项，并根据需要为将要创建的用户定义类型命名(本示例中将要创建的用户定义类型命名为UserDefinedType)，如图4.22所示。
 |
| 图4.22 选择对话框中的【用户定义的类型】选项 |
(5) 单击【添加】按钮创建名为UserDefinedType的用户定义类型。双击【解决方案资源管理器】中的UserDefinedType.vb，在代码编辑器中查看Visual Studio.NET创建的默认代码，如下所示：
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
_
_
Public Structure UserDefinedType
Implements INullable
Public Overrides Function ToString() As String
' 在此处放置代码
Return ""
End Function
Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
Get
' 在此处放置代码
Return m_Null
End Get
End Property
Public Shared ReadOnly Property Null As UserDefinedType
Get
Dim h As UserDefinedType = New UserDefinedType
h.m_Null = True
Return h
End Get
End Property
Public Shared Function Parse(ByVal s As SqlString) As UserDefinedType
If s.IsNull Then
Return Null
End If
Dim u As UserDefinedType = New UserDefinedType
' 在此处放置代码
Return u
End Function
' 这是占位符方法
Public Function Method1() As String
' 在此处放置代码
Return "Hello"
End Function
' 这是占位符静态方法
Public Shared Function Method2() As SqlString
' 在此处放置代码
Return New SqlString("Hello")
End Function
' 这是占位符字段成员
Public m_var1 As Integer
' 私有成员
Private m_Null As Boolean
End Structure
|
提示
从上面的代码中可以看出，Microsoft Visual Studio.NET为创建用户自定义类型生成了默认的模板。下面就模板中的主要部分进行简单地介绍。默认情况下，Visual Studio.NET为用户自定义类型生成的模板中主要包括6个部分，如表4.4所示。
表4.4 用户自定义类型生成的模板中的内容
|
主要方法和属性 |
功 能 |
|
Parse方法 |
用于设置UDT的字符串属性值。通常与UDT相关的字符串操作逻辑都是在该方法中完成的 |
|
ToString方法 |
将一个UDT实例转换为String类型 |
|
用于向外面提供的接口的各个属性 |
可以在UDT结构或类中设置相应的属性，既可以是私有的，也可以是公有的。如果设置的属性是私有的，还需要为这些属性设计访问它们的方法 |
|
INullable接口 |
继承于System.Data.SqlTypes.INullable接口，用于定义UDT处理空值的方式 |
|
Read()和Write()方法 |
用户为UDT提供存储功能 |
|
IBinarySerialize接口的MaxByteSize属性 |
与UDT序列化相关的属性 |
(6) 根据本示例的需要，对原有代码进行相应的改变。首先对代码的前两行进行修改如下：
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize:=512)> _
Public Structure UserDefinedType
Implements INullable, IBinarySerialize
提示
由于本示例中需要为创建的UDT结构添加字符串型的属性。而SQL Server自身提供的默认格式(Format.Native)无法序列化字符串类型(String)的数据，因此需要对默认模板代码的前两行进行修改，将其更改为Format.UserDefined，并指定字符串类型的最大长度为512个字节。
(7) 接下来为该结构添加两个私有成员，即字符串类型的TypeName(用于保存账户类型的名称)和整数类型的TypeID(用于保存账户类型号)。由于添加的两个属性为私有属性，因此为了便于外部对象对这两个属性值进行访问，还需要为这两个私有属性设计属性函数，在Structure UserDefinedType中继续添加如下代码：
Private TypeId As Integer
Private TypeName As String
Public Property MangeTypeID() As Integer
Get
Return (Me.TypeId)
End Get
SET(ByVal Value As Integer)
TypeId = Value
End SET
End Property
Public Property ManageTypeName() As String
Get
Return (Me.TypeName)
End Get
SET(ByVal Value As String)
TypeName = Value
End SET
End Property
|
(8) 接下来继续对模板中的Parse方法进行改造，根据本示例的应用逻辑，为Parse方法添加以下代码：
Dim u As UserDefinedType = New UserDefinedType
' 在此处放置代码
SELECT CASE s
CASE "1"
u.TypeName = "数据管理员"
u.TypeId = 1
CASE "2"
u.TypeName = "数据输入员"
u.TypeId = 2
CASE "3"
u.TypeName = "一般用户"
u.TypeId = 3
End SELECT
Return u
|
(9) 代码的作用是根据输入的参数s来设置私有变量TypeName和TypeId。最后，还需要实现UDT的序列化操作，即为Read和Write方法设计代码。完整的代码如下所示：
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
_
_
Public Structure UserDefinedType
Implements INullable, IBinarySerialize
Private TypeId As Integer
Private TypeName As String
Public Property MangeTypeID() As Integer
Get
Return (Me.TypeId)
End Get
SET(ByVal Value As Integer)
TypeId = Value
End SET
End Property
Public Property ManageTypeName() As String
Get
Return (Me.TypeName)
End Get
SET(ByVal Value As String)
TypeName = Value
End SET
End Property
Public Overrides Function ToString() As String
' 在此处放置代码
Return TypeName
End Function
Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
Get
' 在此处放置代码
Return m_Null
End Get
End Property
Public Shared ReadOnly Property Null() As UserDefinedType
Get
Dim h As UserDefinedType = New UserDefinedType
h.m_Null = True
Return h
End Get
End Property
Public Shared Function Parse(ByVal s As SqlString) As UserDefinedType
If s.IsNull Then
Return Null
End If
Dim u As UserDefinedType = New UserDefinedType
' 在此处放置代码
SELECT CASE s
CASE "1"
u.TypeName = "数据管理员"
u.TypeId = 1
CASE "2"
u.TypeName = "数据输入员"
u.TypeId = 2
CASE "3"
u.TypeName = "一般用户"
u.TypeId = 3
End SELECT
Return u
End Function
' 这是占位符方法
Public Function Method1() As String
' 在此处放置代码
Return "Hello"
End Function
' 这是占位符静态方法
Public Shared Function Method2() As SqlString
' 在此处放置代码
Return "Hello"
End Function
' 这是占位符字段成员
Public m_var1 As Integer
' 私有成员
Private m_Null As Boolean
Public Shared Function DefaultValue() As UserDefinedType
Return UserDefinedType.Parse("3")
End Function
Public Sub Read(ByVal r As System.IO.BinaryReader) Implements Microsoft.SqlServer.Server.IBinarySerialize.Read
TypeName = r.ReadString
TypeId = r.ReadInt16
End Sub
Public Sub Write(ByVal w As System.IO.BinaryWriter) Implements Microsoft.SqlServer.Server.IBinarySerialize.Write
w.Write(TypeName)
w.Write(TypeId)
End Sub
End Structure
|
(10) 选择菜单中的【生成】命令，并选择【生成CLRUDDTSample】命令，生成该用户自定义类型。如果正确地生成了CLRUDDTSample.dll，可继续将其部署到SQL Server数据库中。右击【解决方案资源管理器】中的CLRUDDTSample，从弹出的快捷菜单中选择【部署】命令，Visual Studio.NET将会自动将刚才生成的DLL文件部署到SQL Server数据库中，如图4.23所示。
 |
| 图4.23 选择【部署】命令 |
(11) 如果部署操作成功，可打开Microsoft SQL Server Management Studio，展开【对象资源管理器】中的Northwind示例数据库下的【可编程性】|【用户定义类型】节点，查看刚刚创建的用户自定义类型，如图4.24所示。
 |
| 图4.24 刚创建的用户自定义类型 |
(12) 下面首先删除数据表Account中的记录，然后在【查询编辑器】中输入面的SQL脚本：
USE Northwind
GO
ALTER TABLE Account
ALTER COLUMN AccountType UserDefinedType NOT NULL
GO
(13) 单击【执行】按钮将数据表Account中的AccountType字段更改为UserDefinedType。继续在【查询编辑器】中输入以下SQL脚本：
USE Northwind
GO
INSERT INTO Account (AccountName,Password,AccountType,EML)
VALUES('小吴','123456',UserDefinedType::Parse('1'),'xiaowu@163.com')
(14) 单击【执行】按钮，向数据表Account中插入一条记录。注意上面的SQL脚本在插入一条记录时，使用了自定义数据类型的Parse方法。插入后的数据表Account如图4.25所示。
 |
| 图4.25 插入后的数据表Account |
提示
与类的方法一样，在Transact-SQL中可以使用::直接调用自定义类型的方法。
(15) 继续在【查询编辑器】中输入下面的SQL代码：
(16) 单击【执行】按钮，运行上述脚本的结果如图4.26所示。
USE Northwind
GO
DECLARE @lAccountName AS VARCHAR(20)
DECLARE @lPassword AS VARCHAR(10)
DECLARE @lAccountType AS UserDefinedType
DECLARE @lEML AS VARCHAR(20)
SELECT @lAccountName=AccountName,@lPassword=Password,
@lAccountType=AccountType,@lEML=EML
FROM Account
PRINT @lAccountName +' '+@lPassword+' '+CONVERT(VARCHAR(20),@lAccountType)+' '+CONVERT(VARCHAR(20),@lEML)
+' 账号类型代码：'+CONVERT(VARCHAR(20),@lAccountType.MangeTypeID)
|
(16) 单击【执行】按钮，运行上述脚本的结果如图4.26所示。
 |
| 图4.26 运行结果 |
提示
与访问一个对象的方法一样，在Transact-SQL中也可以使用“自定义类型变量·方法”的形式来访问自定义类型的方法。
提示
上面的示例实际上实现了在SQL Server中定义数据结构的功能。Microsoft通过这种方式大大提高了Transact-SQL的编程逻辑能力。
【责任编辑：
董书 TEL：（010）68476606】
