ADO.NET 3.5 Cookbook, 2nd Edition

12.5. Creating a User-Defined Type

Problem

You need to create a CLR user-defined type.

Solution

The following example creates, registers, and uses a user-defined type that defines a polygon and implements a single method that returns the area of the polygon as a double. Follow these steps:

1. Create a new SQL Server project in Visual Studio and name it ClrType.

2. Create a user-defined type item in the project. Name the item Polygon.cs.

3. The C# code in Polygon.cs in the project ClrType is shown in Example 12-8.

   Example 12-8. File: Polygon.cs

   using System;
   using System.Data;
   using System.Data.SqlClient;
   using System.Data.SqlTypes;
   using Microsoft.SqlServer.Server;
   
   [Serializable]
   [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
   public struct Polygon : INullable
   {
       private bool isNull;
       private int numberSides;
       private double sideLength;
   
       public override string ToString()
       {
           if (this.isNull)
               return "null";
           else
               return string.Format("{0} sides each {1} units long",
                   numberSides, sideLength);
       }
   
       public bool IsNull
       {
           get
           {
               return isNull;
           }
       }
   
       public static Polygon Null
       {
           get
           {
               Polygon p = new Polygon();
               p.isNull = true;
               return p;
           }
       }
   
       public static Polygon Parse(SqlString s) { if (s.IsNull || s.Value.ToLower().Equals("null")) return Null; string[] sa = s.ToString().Split(','); if (sa.Length != 2) return Null; Polygon p = new Polygon(); try { p.numberSides = int.Parse(sa[0]); p.sideLength = double.Parse(sa[1]); if (p.numberSides > 2 && p.sideLength > 0) return p; else return Null; } catch (Exception) ...