Retrieving UDT Data
In order to create a user-defined type (UDT) on the client, the assembly that was registered as a UDT in a SQL Server database must be available to the client application. The UDT assembly can be placed in the same directory with the application, or in the Global Assembly Cache (GAC). You can also set a reference to the assembly in your project.
Requirements for Using UDTs in ADO.NET
The assembly loaded in SQL Server and the assembly on the client must be compatible in order for the UDT to be created on the client. For UDTs defined with the Native serialization format, the assemblies must be structurally compatible. For assemblies defined with the UserDefined format, the assembly must be available on the client.
You do not need a copy of the UDT assembly on the client in order to retrieve the raw data from a UDT column in a table.
Note
SqlClient may fail to load a UDT in the event of mismatched UDT versions or other problems. In this case, use regular troubleshooting mechanisms to determine why the assembly containing the UDT cannot be found by the calling application. For more information, see "Diagnosing Errors with Managed Debugging Assistants" in the .NET Framework.
Accessing UDTs with a SqlDataReader
A System.Data.SqlClient.SqlDataReader can be used from client code to retrieve a result set that contains a UDT column, which is exposed as an instance of the object.
Example
This example shows how to use the Main method to create a new SqlDataReader object. The following actions occur within the code example:
The Main method creates a new SqlDataReader object and retrieves the values from the Points table, which has a UDT column named Point.
The Point UDT exposes X and Y coordinates defined as integers.
The UDT defines a Distance method and a GetDistanceFromXY method.
The sample code retrieves the values of the primary key and UDT columns in order to demonstrate the capabilities of the UDT.
The sample code calls the Point.Distance and Point.GetDistanceFromXY methods.
The results are displayed in the console window.
Note
The application must already have a reference to the UDT assembly.
Option Explicit On
Option Strict On
Imports System
Imports System.Data.Sql
Imports System.Data.SqlClient
Module ReadPoints
Sub Main()
Dim connectionString As String = GetConnectionString()
Using cnn As New SqlConnection(connectionString)
cnn.Open()
Dim cmd As New SqlCommand( _
"SELECT ID, Pnt FROM dbo.Points", cnn)
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader
While rdr.Read()
' Retrieve the value of the Primary Key column
Dim id As Int32 = rdr.GetInt32(0)
' Retrieve the value of the UDT
Dim pnt As Point = CType(rdr(1), Point)
' You can also use GetSqlValue and GetValue
' Dim pnt As Point = CType(rdr.GetSqlValue(1), Point)
' Dim pnt As Point = CType(rdr.GetValue(1), Point)
' Print values
Console.WriteLine( _
"ID={0} Point={1} X={2} Y={3} DistanceFromXY={4} Distance={5}", _
id, pnt, pnt.X, pnt.Y, pnt.DistanceFromXY(1, 9), pnt.Distance())
End While
rdr.Close()
Console.WriteLine("done")
End Using
End Sub
Private Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=(local);Initial Catalog=AdventureWorks2008R2;" _
& "Integrated Security=SSPI;"
End Function
End Module
using System;
using System.Data.Sql;
using System.Data.SqlClient;
namespace Microsoft.Samples.SqlServer
{
class ReadPoints
{
static void Main()
{
string connectionString = GetConnectionString();
using (SqlConnection cnn = new SqlConnection(connectionString))
{
cnn.Open();
SqlCommand cmd = new SqlCommand(
"SELECT ID, Pnt FROM dbo.Points", cnn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
// Retrieve the value of the Primary Key column
int id = rdr.GetInt32(0);
// Retrieve the value of the UDT
Point pnt = (Point)rdr[1];
// You can also use GetSqlValue and GetValue
// Point pnt = (Point)rdr.GetSqlValue(1);
// Point pnt = (Point)rdr.GetValue(1);
Console.WriteLine(
"ID={0} Point={1} X={2} Y={3} DistanceFromXY={4} Distance={5}",
id, pnt, pnt.X, pnt.Y, pnt.DistanceFromXY(1, 9), pnt.Distance());
}
rdr.Close();
Console.WriteLine("done");
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
return "Data Source=(local);Initial Catalog=AdventureWorks2008R2;"
+ "Integrated Security=SSPI";
}
}
Binding UDTs as Bytes
In some situations, you may want to retrieve the raw data from the UDT column. Perhaps the type is not available locally, or you do not wish to instantiate an instance of the UDT. You can read the raw bytes into a byte array using the GetBytes method of a SqlDataReader. This method reads a stream of bytes from the specified column offset into the buffer of an array starting at a specified buffer offset. Another option is to use one of the GetSqlBytes or GetSqlBinary methods and read all of the contents in a single operation. In either case, the UDT object is never instantiated, so you do not need to set a reference to the UDT in the client assembly.
Example
This example shows how to retrieve the Point data as raw bytes into a byte array using a SqlDataReader. The code uses a System.Text.StringBuilder to convert the raw bytes to a string representation to be displayed in the console window.
Option Explicit On
Option Strict On
Imports System
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Text
Module GetRawBytes
Sub Main()
Dim connectionString As String = GetConnectionString()
Using cnn As New SqlConnection(connectionString)
cnn.Open()
Dim cmd As New SqlCommand( _
"SELECT ID, Pnt FROM dbo.Points", cnn)
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader
While rdr.Read()
' Retrieve the value of the Primary Key column
Dim id As Int32 = rdr.GetInt32(0)
' Retrieve the raw bytes into a byte array
Dim buffer(31) As Byte
Dim byteCount As Integer = _
CInt(rdr.GetBytes(1, 0, buffer, 0, 31))
' Format and print bytes
Dim str As New StringBuilder
str.AppendFormat("ID={0} Point=", id)
Dim i As Integer
For i = 0 To (byteCount - 1)
str.AppendFormat("{0:x}", buffer(i))
Next
Console.WriteLine(str.ToString)
End While
rdr.Close()
Console.WriteLine("done")
End Using
End Sub
Private Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=(local);Initial Catalog=AdventureWorks2008R2;" _
& "Integrated Security=SSPI;"
End Function
End Module
using System;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
class GetRawBytes
{
static void Main()
{
string connectionString = GetConnectionString();
using (SqlConnection cnn = new SqlConnection(connectionString))
{
cnn.Open();
SqlCommand cmd = new SqlCommand("SELECT ID, Pnt FROM dbo.Points", cnn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
// Retrieve the value of the Primary Key column
int id = rdr.GetInt32(0);
// Retrieve the raw bytes into a byte array
byte[] buffer = new byte[32];
long byteCount = rdr.GetBytes(1, 0, buffer, 0, 32);
// Format and print bytes
StringBuilder str = new StringBuilder();
str.AppendFormat("ID={0} Point=", id);
for (int i = 0; i < byteCount; i++)
str.AppendFormat("{0:x}", buffer[i]);
Console.WriteLine(str.ToString());
}
rdr.Close();
Console.WriteLine("done");
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
return "Data Source=(local);Initial Catalog=AdventureWorks2008R2;"
+ "Integrated Security=SSPI";
}
}
}
Example Using GetSqlBytes
This example shows how to retrieve the Point data as raw bytes in a single operation using the GetSqlBytes method. The code uses a StringBuilder to convert the raw bytes to a string representation to be displayed in the console window.
Option Explicit On
Option Strict On
Imports System
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Text
Module GetRawBytes
Sub Main()
Dim connectionString As String = GetConnectionString()
Using cnn As New SqlConnection(connectionString)
cnn.Open()
Dim cmd As New SqlCommand( _
"SELECT ID, Pnt FROM dbo.Points", cnn)
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader
While rdr.Read()
' Retrieve the value of the Primary Key column
Dim id As Int32 = rdr.GetInt32(0)
' Use SqlBytes to retrieve raw bytes
Dim sb As SqlBytes = rdr.GetSqlBytes(1)
Dim byteCount As Long = sb.Length
' Format and print bytes
Dim str As New StringBuilder
str.AppendFormat("ID={0} Point=", id)
Dim i As Integer
For i = 0 To (byteCount - 1)
str.AppendFormat("{0:x}", sb(i))
Next
Console.WriteLine(str.ToString)
End While
rdr.Close()
Console.WriteLine("done")
End Using
End Sub
Private Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=(local);Initial Catalog=AdventureWorks2008R2;" _
& "Integrated Security=SSPI;"
End Function
End Module
using System;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
class GetRawBytes
{
static void Main()
{
string connectionString = GetConnectionString();
using (SqlConnection cnn = new SqlConnection(connectionString))
{
cnn.Open();
SqlCommand cmd = new SqlCommand(
"SELECT ID, Pnt FROM dbo.Points", cnn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
// Retrieve the value of the Primary Key column
int id = rdr.GetInt32(0);
// Use SqlBytes to retrieve raw bytes
SqlBytes sb = rdr.GetSqlBytes(1);
long byteCount = sb.Length;
// Format and print bytes
StringBuilder str = new StringBuilder();
str.AppendFormat("ID={0} Point=", id);
for (int i = 0; i < byteCount; i++)
str.AppendFormat("{0:x}", sb[i]);
Console.WriteLine(str.ToString());
}
rdr.Close();
Console.WriteLine("done");
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
return "Data Source=(local);Initial Catalog=AdventureWorks2008R2;"
+ "Integrated Security=SSPI";
}
}
}
Working with UDT Parameters
UDTs can be used as both input and output parameters in your ADO.NET code.
Using UDTs in Query Parameters
UDTs can be used as parameter values when setting up a SqlParameter for a System.Data.SqlClient.SqlCommand object. The SqlDbType.Udt enumeration of a SqlParameter object is used to indicate that the parameter is a UDT when calling the Add method to the Parameters collection. The UdtTypeName property of a SqlCommand object is used to specify the fully qualified name of the UDT in the database using the database.schema_name.object_name syntax. Although it is not required, using the fully qualified name removes ambiguity from your code.
Note
A local copy of the UDT assembly must be available to the client project.
Example
The code in this example creates SqlCommand and SqlParameter objects to insert data into a UDT column in a table. The code uses the SqlDbType.Udt enumeration to specify the data type, and the UdtTypeName property of the SqlParameter object to specify the fully qualified name of the UDT in the database.
Option Explicit On
Option Strict On
Imports System
Imports system.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim ConnectionString As String = GetConnectionString()
Dim cnn As New SqlConnection(ConnectionString)
Using cnn
Dim cmd As SqlCommand = cnn.CreateCommand()
cmd.CommandText = "INSERT INTO dbo.Points (Pnt) VALUES (@Point)"
cmd.CommandType = CommandType.Text
Dim param As New SqlParameter("@Point", SqlDbType.Udt)
param.UdtTypeName = "TestPoint.dbo.Point"
param.Direction = ParameterDirection.Input
param.Value = New Point(5, 6)
cmd.Parameters.Add(param)
cnn.Open()
cmd.ExecuteNonQuery()
Console.WriteLine("done")
End Using
End Sub
Private Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=(local);Initial Catalog=AdventureWorks2008R2;" _
& "Integrated Security=SSPI;"
End Function
End Module
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
class Class1
{
static void Main()
{
string ConnectionString = GetConnectionString();
using (SqlConnection cnn = new SqlConnection(ConnectionString))
{
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandText =
"INSERT INTO dbo.Points (Pnt) VALUES (@Point)";
cmd.CommandType = CommandType.Text;
SqlParameter param = new SqlParameter("@Point", SqlDbType.Udt);
param.UdtTypeName = "TestPoint.dbo.Point";
param.Direction = ParameterDirection.Input;
param.Value = new Point(5, 6);
cmd.Parameters.Add(param);
cnn.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("done");
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
return "Data Source=(local);Initial Catalog=AdventureWorks2008R2;"
+ "Integrated Security=SSPI";
}
}
}