ADO.NET 3.5 Cookbook, 2nd Edition

10.16. Reading and Writing a SQL Server User-Defined Type (UDT)

Problem

You need to retrieve the value of a user-defined type that you have created using a CLR routine from a SQL Server database.

Solution

The following example creates, registers, and uses a user-defined type that defines a point—the UDT is called Point. Follow these steps:

1. Enable CLR integration by following the instructions in the Introduction to Chapter 12, if necessary.

2. Create a new SQL Server project in Visual Studio and name it ClrPointType. Press the Cancel button to dismiss the New Database Reference dialog that is displayed.

3. Create a user-defined type item in the project. Name the item Point.cs.

The C# code in Point.cs in the project ClrPointType is shown in Example 10-26.

Example 10-26. File: Point.cs for ClrPointType solution

using System; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)] public struct Point : INullable { private bool isNull; private double x; private double y; public override string ToString() { if (this.isNull) return "null"; else return string.Format("(x, y) = ({0}, {1})", x, y); } public bool IsNull { get { return isNull; } } public static Point Null { get { Point p = new Point(); p.isNull = true; return p; } } public Point(double x, double y) { this.x = x; this.y = y; isNull = false; } public static Point Parse(SqlString s) { if (s.IsNull || s.Value.ToLower().Equals("null")) return Null; string[] sa = ...