Edit

Share via


Retrieve user-defined type (UDT) data in ADO.NET

Applies to: SQL Server

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 don't 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 might 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 can't be found by the calling application. For more information, see Diagnose Errors with Managed Debugging Assistants.

Access 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.

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=AdventureWorks2022"
                       + "Integrated Security=SSPI";
            }
        }
    }
}

Bind UDTs as bytes

In some situations, you might want to retrieve the raw data from the UDT column. Perhaps the type isn't available locally, or you don't 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 don't 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.

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=AdventureWorks2022"
            + "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.

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=AdventureWorks2022"
            + "Integrated Security=SSPI";
    }
  }
}

Work with UDT parameters

UDTs can be used as both input and output parameters in your ADO.NET code.

Use 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. You should use the fully qualified name to avoid ambiguity in your code.

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.

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=AdventureWorks2022"
            + "Integrated Security=SSPI";
    }
  }
}