CLR Integration

Requirements for Implementing UDTs

To run in SQL Server, your UDT must implement the following requirements in the UDT definition:

The UDT must specify the Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute. The use of the System.SerializableAttribute is optional, but recommended.

• The UDT must implement the System.Data.SqlTypes.INullable interface in the class or structure by creating a public static (Shared in Microsoft Visual Basic) Nullmethod. SQL Server is null-aware by default. This is necessary for code executing in the UDT to be able to recognize a null value.
• The UDT must contain a public static (or Shared) Parse method that supports parsing from, and a public ToString method for converting to a string representation of the object.
• A UDT with a user-defined serialization format must implement the System.Data.IBinarySerialize interface and provide a Read and a Write method.
• The UDT must implement System.Xml.Serialization.IXmlSerializable, or all public fields and properties must be of types that are XML serializable or decorated with the XmlIgnore attribute if overriding standard serialization is required.
• There must be only one serialization of a UDT object. Validation fails if the serialize or deserialize routines recognize more than one representation of a particular object.
• SqlUserDefinedTypeAttribute.IsByteOrdered must be true in order to ensure that the server uses byte-ordered comparisons for UDT values.
• A UDT defined in a class must have a public constructor that takes no arguments. You can optionally create additional overloaded class constructors.
• The UDT must expose data elements as public fields or property procedures.
• Public names cannot be longer than 128 characters, and must conform to the SQL Server naming rules for identifiers as defined in Identifiers.
• sql_variant columns cannot contain instances of a UDT.
• Inherited members are not accessible from Transact-SQL because the SQL Server 2005 type system is not aware of the inheritance hierarchy among UDTs. However, you can use inheritance when you structure your classes and you can call such methods in the managed code implementation of the type.
• Members cannot be overloaded, except for the class constructor. If you do create an overloaded method, no error is raised when you register the assembly or create the type in SQL Server. Detection of the overloaded method occurs at run time, not when the type is created. Overloaded methods can exist in the class as long as they are never invoked. Once you invoke the overloaded method, an error is raised.
• Any static (or Shared) members must be declared as constants or as read-only. Static members cannot be mutable.
• The serialized UDT cannot be larger than 8000 bytes

Sample:

using System;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
     IsByteOrdered=true, ValidationMethodName = "ValidatePoint")]
public struct Point : INullable
{
    private bool is_Null;
    private Int32 _x;
    private Int32 _y;

    public bool IsNull
    {
        get
        {
            return (is_Null);
        }
    }

    public static Point Null
    {
        get
        {
            Point pt = new Point();
            pt.is_Null = true;
            return pt;
        }
    }

    // Use StringBuilder to provide string representation of UDT.
    public override string ToString()
    {
        // Since InvokeIfReceiverIsNull defaults to 'true'
        // this test is unneccesary if Point is only being called
        // from SQL.
        if (this.IsNull)
            return "NULL";
        else
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(_x);
            builder.Append(",");
            builder.Append(_y);
            return builder.ToString();
        }
    }

    [SqlMethod(OnNullCall = false)]
    public static Point Parse(SqlString s)
    {
        // With OnNullCall=false, this check is unnecessary if 
        // Point only called from SQL.
        if (s.IsNull)
            return Null;

        // Parse input string to separate out points.
        Point pt = new Point();
        string[] xy = s.Value.Split(",".ToCharArray());
        pt.X = Int32.Parse(xy[0]);
        pt.Y = Int32.Parse(xy[1]);

        // Call ValidatePoint to enforce validation
        // for string conversions.
        if (!pt.ValidatePoint()) 
            throw new ArgumentException("Invalid XY coordinate values.");
        return pt;
    }

    // X and Y coordinates exposed as properties.
    public Int32 X
    {
        get
        {
            return this._x;
        }
        // Call ValidatePoint to ensure valid range of Point values.
        set 
        {
            Int32 temp = _x;
            _x = value;
            if (!ValidatePoint())
            {
                _x = temp;
                throw new ArgumentException("Invalid X coordinate value.");
            }
        }
    }

    public Int32 Y
    {
        get
        {
            return this._y;
        }
        set
        {
            Int32 temp = _y;
            _y = value;
            if (!ValidatePoint())
            {
                _y = temp;
                throw new ArgumentException("Invalid Y coordinate value.");
            }
        }
    }

    // Validation method to enforce valid X and Y values.
    private bool ValidatePoint()
    {
        // Allow only zero or positive integers for X and Y coordinates.
        if ((_x >= 0) && (_y >= 0))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    // Distance from 0 to Point method.
    [SqlMethod(OnNullCall = false)]
    public Double Distance()
    {
        return DistanceFromXY(0, 0);
    }

    // Distance from Point to the specified point method.
    [SqlMethod(OnNullCall = false)]
    public Double DistanceFrom(Point pFrom)
    {
        return DistanceFromXY(pFrom.X, pFrom.Y);
    }

    // Distance from Point to the specified x and y values method.
    [SqlMethod(OnNullCall = false)]
    public Double DistanceFromXY(Int32 iX, Int32 iY)
    {
        return Math.Sqrt(Math.Pow(iX - _x, 2.0) + Math.Pow(iY - _y, 2.0));
    }
}

http://chiragrdarji.wordpress.com/2008/03/11/clr-stored-procedure-in-sql-server-2005/