大型 UDTLarge UDTs

通过用户定义类型 (UDT),开发人员可通过在 SQL Server 数据库中存储公共语言运行时 (CLR) 对象来扩展服务器的标量类型系统。User-defined types (UDTs) allow a developer to extend the server's scalar type system by storing common language runtime (CLR) objects in a SQL Server database. UDT 可以包含多个元素并可具有多种行为,与传统别名数据类型不同,它们由单一 SQL Server 系统数据类型组成。UDTs can contain multiple elements and can have behaviors, unlike the traditional alias data types, which consist of a single SQL Server system data type.

备注

必须安装 .NET Framework 3.5 SP1(或更高版本)才能利用针对大型 UDT 增强的 SqlClient 支持。You must install the .NET Framework 3.5 SP1 (or later) to take advantage of the enhanced SqlClient support for large UDTs.

以前,UDT 的最大大小限制为 8KB。Previously, UDTs were restricted to a maximum size of 8 kilobytes. 在 SQL Server 2008 中,对于具有 UserDefined 格式的 UDT,此限制已被取消。In SQL Server 2008, this restriction has been removed for UDTs that have a format of UserDefined.

有关用户定义类型的完整文档,请参见与您所使用的 SQL Server 版本对应的 SQL Server 联机丛书。For the complete documentation for user-defined types, see the version of SQL Server Books Online for the version of SQL Server you are using.

SQL Server 联机丛书SQL Server Books Online

  1. CLR 用户定义的类型CLR User-Defined Types

使用 GetSchema 检索 UDT 架构Retrieving UDT Schemas Using GetSchema

GetSchemaSqlConnection 方法可返回 DataTable 中的数据库架构信息。The GetSchema method of SqlConnection returns database schema information in a DataTable. 有关详细信息,请参阅SQL Server 架构集合For more information, see SQL Server Schema Collections.

UDT 的 GetSchemaTable 列值GetSchemaTable Column Values for UDTs

GetSchemaTableSqlDataReader 方法可返回描述列元数据的 DataTableThe GetSchemaTable method of a SqlDataReader returns a DataTable that describes column metadata. 下表介绍了 SQL Server 2005 与 SQL Server 2008 中大型 UDT 的列元数据的差异。The following table describes the differences in the column metadata for large UDTs between SQL Server 2005 and SQL Server 2008.

SqlDataReader 列SqlDataReader column SQL Server 2005SQL Server 2005 SQL Server 2008 及更高版本SQL Server 2008 and later
ColumnSize 不定Varies 不定Varies
NumericPrecision 255255 255255
NumericScale 255255 255255
DataType Byte[] UDT 实例UDT instance
ProviderSpecificDataType SqlTypes.SqlBinary UDT 实例UDT instance
ProviderType 21 (SqlDbType.VarBinary)21 (SqlDbType.VarBinary) 29 (SqlDbType.Udt)29 (SqlDbType.Udt)
NonVersionedProviderType 29 (SqlDbType.Udt)29 (SqlDbType.Udt) 29 (SqlDbType.Udt)29 (SqlDbType.Udt)
DataTypeName SqlDbType.VarBinary 指定为SchemaName的三部分名称。The three part name specified as Database.SchemaName.TypeName.
IsLong 不定Varies 不定Varies

SqlDataReader 注意事项SqlDataReader Considerations

从 SQL Server 2008 开始,SqlDataReader 已得到扩展,可支持检索大型 UDT 值。The SqlDataReader has been extended beginning in SQL Server 2008 to support retrieving large UDT values. SqlDataReader 处理大型 UDT 值的方式取决于您所使用的 SQL Server 版本以及连接字符串中指定的 Type System VersionHow large UDT values are processed by a SqlDataReader depends on the version of SQL Server you are using, as well as on the Type System Version specified in the connection string. 有关详细信息,请参阅 ConnectionStringFor more information, see ConnectionString.

SqlDataReader 设置为 SQL Server 2005 时,SqlBinary 的以下方法将返回 Type System Version 而不是 UDT:The following methods of SqlDataReader will return a SqlBinary instead of a UDT when the Type System Version is set to SQL Server 2005:

Byte[] 设置为 SQL Server 2005 时,以下方法将返回 Type System Version 的数组而不是 UDT:The following methods will return an array of Byte[] instead of a UDT when the Type System Version is set to SQL Server 2005:

请注意,不会对 ADO.NET 的当前版本进行任何转换。Note that no conversions are made for the current version of ADO.NET.

指定 SqlParametersSpecifying SqlParameters

下面的 SqlParameter 属性已得到扩展,可以使用大型 UDT。The following SqlParameter properties have been extended to work with large UDTs.

SqlParameter 属性SqlParameter Property 描述Description
Value 获取或设置表示参数值的对象。Gets or sets an object that represents the value of the parameter. 默认值为 null。The default is null. 此属性可以是 SqlBinaryByte[] 或一个托管对象。The property can be SqlBinary, Byte[], or a managed object.
SqlValue 获取或设置表示参数值的对象。Gets or sets an object that represents the value of the parameter. 默认值为 null。The default is null. 此属性可以是 SqlBinaryByte[] 或一个托管对象。The property can be SqlBinary, Byte[], or a managed object.
Size 获取或设置要解析的参数值的大小。Gets or sets the size of the parameter value to resolve. 默认值为 0。The default value is 0. 此属性可以是表示参数值大小的一个整数。The property can be an integer that represents the size of the parameter value. 对于大型 UDT,此属性可以是 UDT 的实际大小,也可以是 -1 以表示未知大小。For large UDTs, it can be the actual size of the UDT, or -1 for unknown.

检索数据示例Retrieving Data Example

下面的代码段演示如何检索大型 UDT 数据。The following code fragment demonstrates how to retrieve large UDT data. connectionString 变量假定已与 SQL Server 数据库建立有效的连接,commandString 变量假定存在首先列出主键列的有效 SELECT 语句。The connectionString variable assumes a valid connection to a SQL Server database and the commandString variable assumes a valid SELECT statement with the primary key column listed first.

using (SqlConnection connection = new SqlConnection(   
    connectionString, commandString))  
{  
  connection.Open();  
  SqlCommand command = new SqlCommand(commandString);  
  SqlDataReader reader = command.ExecuteReader();  
  while (reader.Read())  
  {  
    // Retrieve the value of the Primary Key column.  
    int id = reader.GetInt32(0);  
  
    // Retrieve the value of the UDT.  
    LargeUDT udt = (LargeUDT)reader[1];  
  
    // You can also use GetSqlValue and GetValue.  
    // LargeUDT udt = (LargeUDT)reader.GetSqlValue(1);  
    // LargeUDT udt = (LargeUDT)reader.GetValue(1);  
  
    Console.WriteLine(  
     "ID={0} LargeUDT={1}", id, udt);  
  }  
reader.close  
}  
Using connection As New SqlConnection( _  
    connectionString, commandString)  
    connection.Open()  
    Dim command As New SqlCommand(commandString, connection)  
    Dim reader As SqlDataReader  
    reader = command.ExecuteReader  
  
    While reader.Read()  
      ' Retrieve the value of the Primary Key column.  
      Dim id As Int32 = reader.GetInt32(0)  
  
      ' Retrieve the value of the UDT.  
      Dim udt As LargeUDT = CType(reader(1), LargeUDT)  
  
     ' You can also use GetSqlValue and GetValue.  
     ' Dim udt As LargeUDT = CType(reader.GetSqlValue(1), LargeUDT)  
     ' Dim udt As LargeUDT = CType(reader.GetValue(1), LargeUDT)  
  
      ' Print values.  
      Console.WriteLine("ID={0} LargeUDT={1}", id, udt)  
    End While  
    reader.Close()  
End Using  

请参阅See also