Sdílet prostřednictvím


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:

  1. The Main method creates a new SqlDataReader object and retrieves the values from the Points table, which has a UDT column named Point.

  2. The Point UDT exposes X and Y coordinates defined as integers.

  3. The UDT defines a Distance method and a GetDistanceFromXY method.

  4. The sample code retrieves the values of the primary key and UDT columns in order to demonstrate the capabilities of the UDT.

  5. The sample code calls the Point.Distance and Point.GetDistanceFromXY methods.

  6. 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";
    }
  }
}

See Also

Other Resources