Large UDTs in SQL Server 2008 (ADO.NET)

User-defined types (UDTs) introduced in SQL Server 2005 allow a developer to extend the server's scalar type system by storing common language runtime (CLR) objects in a SQL Server database. 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.

Note

You must install the .NET Framework 3.5 SP1 (or later) to take advantage of the enhanced SqlClient support for large UDTs.

Previously, UDTs were restricted to a maximum size of 8 kilobytes. In SQL Server 2008, this restriction has been removed for UDTs that have a format of UserDefined.

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 2005	SQL Server 2008
CLR User-Defined Types	CLR User-Defined Types

Retrieving UDT Schemas Using GetSchema

The GetSchema method of SqlConnection returns database schema information in a DataTable. For more information, see SQL Server Schema Collections (ADO.NET).

GetSchemaTable Column Values for UDTs

The GetSchemaTable method of a SqlDataReader returns a DataTable that describes column metadata. The following table describes the differences in the column metadata for large UDTs between SQL Server 2005 and SQL Server 2008.

SqlDataReader column	SQL Server 2005	SQL Server 2008
ColumnSize	Varies	Varies
NumericPrecision	255	255
NumericScale	255	255
DataType	Byte[]	UDT instance
ProviderSpecificDataType	SqlTypes.SqlBinary	UDT instance
ProviderType	21 (SqlDbType.VarBinary)	29 (SqlDbType.Udt)
NonVersionedProviderType	29 (SqlDbType.Udt)	29 (SqlDbType.Udt)
DataTypeName	SqlDbType.VarBinary	The three part name specified as Database.SchemaName.TypeName.
IsLong	Varies	Varies

SqlDataReader Considerations

The SqlDataReader has been extended in SQL Server 2008 to support retrieving large UDT values. How 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. For more information, see the "Type System Version Changes" section in New Features in SQL Server 2008 (ADO.NET).

The following methods of SqlDataReader will return a SqlBinary instead of a UDT instance in earlier versions of SQL Server, or when the Type System Version does not specify SQL Server 2008:

• GetProviderSpecificFieldType

• GetProviderSpecificValue

• GetProviderSpecificValues

• GetSqlValue

• GetSqlValues

The following methods will return an array of Byte[] instead of a UDT instance in earlier versions of SQL Server, or when the Type System Version does not specify SQL Server 2008:

• GetValue

• GetValues

Note that no conversions are made for the current version of ADO.NET.

Specifying SqlParameters

The following SqlParameter properties have been extended to work with large UDTs.

SqlParameter Property	Description
Value	Gets or sets an object that represents the value of the parameter. The default is null. The property can be SqlBinary, Byte[], or a managed object.
SqlValue	Gets or sets an object that represents the value of the parameter. The default is null. The property can be SqlBinary, Byte[], or a managed object.
Size	Gets or sets the size of the parameter value to resolve. The default value is 0. The property can be an integer that represents the size of the parameter value. For large UDTs, it can be the actual size of the UDT, or -1 for unknown.

Retrieving Data Example

The following code fragment demonstrates how to retrieve large UDT data. 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

Concepts

Configuring Parameters and Parameter Data Types (ADO.NET)

SQL Server Data Type Mappings (ADO.NET)

Other Resources

Retrieving Database Schema Information (ADO.NET)

SQL Server Binary and Large-Value Data (ADO.NET)

ADO.NET Managed Providers and DataSet Developer Center