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:
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 aGetDistanceFromXY
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
andPoint.GetDistanceFromXY
methods.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";
}
}
}