Section 5.5. User-Defined Types


5.5. User-Defined Types

In addition to supporting native and simple types as in previous versions of SQL Server, SQL Server 2005 lets you define CLR user-defined types (UDTs ). This lets you extend the built-in data types and define complex data types. A CLR UDT can be used in all contexts where a SQL Server system type can be used.

A CLR UDT is implemented as a class in a .NET Framework assembly. Identify a CLR UDT by annotating the class that implements the UDT with the SqlUserDefinedType attribute, which indicates that a class should be registered as a UDT. The SqlUserDefinedType attribute has the following syntax:

     SqlUserDefinedType [ ( udt-property [,...] ) ]     udt-property::=         Format = { Native | UserDefined }       | MaxByteSize  = n       | IsByteOrdered  = { true | false }       | ValidationMethod   = string       | IsFixedLength = { true | false }       | Name = string 

where:


Format = { Native | UserDefined }

The serialization format of the UDT. For more information about these two values, see the Format property for the SqlUserDefinedAggregate attribute in the "User-Defined Aggregate Functions" section earlier in this chapter.

If the UDT is defined in a class rather than a structure, and if the Format property is Native, a StructLayout attribute must be specified and set to LayoutKind.Sequential. This forces the members in the class to be serialized in the same order in which they appear in the class.


MaxByteSize

Specifies the maximum size of an instance of the UDT between 1 and 8000 bytes. You must specify MaxByteSize if the Format property is set to UserDefined. Do not specify MaxByteSize if the Format property is set to Native.


IsByteOrdered

Specifies how binary comparisons are performed on the UDT by SQL Server. When IsByteOrdered is TRue, the UDT is ordered in the same way as its serialized binary representation and can be used to order the data. The following features are supported on the UDT column in a table when IsByteOrdered is true:

  • Creating an index on the column

  • Creating primary and foreign key constraints, and CHECK and UNIQUE constraints on the column

  • Using the column in T-SQL ORDER BY, GROUP BY, and PARTITION BY clauses

  • Using comparison operators in T-SQL statements on the column


ValidationMethod

Specifies the method used to validate instances of the UDT when the data is deserialized from a binary value. The converted method returns a Boolean indicating whether the UDT instance is valid.

The database engine automatically converts binary values to UDT values. The database engine prevents invalid values in the database by checking whether values are appropriate for the serialization format of the type and that the value can be deserialized. Default checking might be inadequate when, for example, UDT values are constrained by a value set or a range.


IsFixedLength

Specifies whether all instances of the UDT are the same length. If the IsFixedLength property is true, all instances of the UDT must have the length, in bytes, specified by the MaxByteSize property. The property is used only when the Format property is set to UserDefined.


Name

Specifies the name of the type.

When a field, method, or property is referenced as part of a query, the T-SQL type of the return value is inferred from the return type. The SqlFacet attribute can be used to return additional information about the return type of a non-void UDT expressionthe SqlFacet attribute does not constrain the specific values that can be stored in the type. The syntax of the SqlFacet attribute is as follows:

     SqlFacet[(facet-attribute [,...])]     facet-attribute::=         IsFixedLength = { true | false }       | MaxSize  = { n }       | Precision   = { n }       | Scale   = { n }       | IsNullable   = { true | false } 

where:


IsFixedLength

Specifies whether the return type is a fixed length. IsFixedLength must be set to false if the MaxSize property is set to -1. The default value is false.


MaxSize

Specifies the maximum size of the return type in bytes for binary types and characters for character field types. The default is 4000 for Unicode character types and 8000 for binary types. The value -1 indicates a large character or binary type.


Precision

Specifies the precision (number of digits in the number) of the return type as a value from 1 to 38. This property is used only with numeric types. Scale must be specified if Precision is specified. The default value is 38.


Scale

Specifies the scale (number of digits to the right of the decimal point) of the return type as a value from 0 to 38. This property is used only with numeric types. Precision must be specified if Scale is specified. The default value is 0.


IsNullable

Indicates whether the value of the return type can be null. The default is TRue.

The properties specified for the SqlFacet attribute must be compatible with the return type. Table 5-1 shows SqlFacet properties that can be specified for each return type.

Table 5-1. Allowable SqlFacet properties by return type

Type

IsFixedLength

MaxSize

Precision

Scale

IsNullable

SqlBoolean

N

N

N

N

Y

SqlByte

N

N

N

N

Y

SqlInt16

N

N

N

N

Y

SqlInt32

N

N

N

N

Y

SqlInt64

N

N

N

N

Y

SqlSingle

N

N

N

N

Y

SqlDouble

N

N

N

N

Y

SqlDateTime

N

N

N

N

Y

SqlMoney

N

N

N

N

Y

SqlGuid

N

N

N

N

Y

SqlDecimal

N

N

Y

Y

Y

SqlString

Y

Y

N

N

Y

SqlBinary

Y

Y

N

N

Y

SqlXml

N

N

N

N

Y

SqlBytes

Y

Y

N

N

Y

SqlChars

Y

Y

N

N

Y

SqlUtcDateTime

N

N

N

N

Y

SqlDate

N

N

N

N

Y

SqlTime

N

N

N

N

Y

Embedded UDTs

N

N

N

N

Y

string

Y

Y

N

N

Y

Byte[]

Y

Y

N

N

Y

Char[]

Y

Y

N

N

Y

decimal

N

N

Y

Y

N


You must do the following when you define a CLR UDT:

  • Annotate the class with the SqlUserDefinedType attribute.

  • Specify the Serializable attribute, indicating that the UDT can be serialized.

  • Implement the System.Data.SqlTypes.INullable interface so that the UDT can recognize a null value. This means that the UDT must implement a static IsNull property that returns a Boolean indicating whether the instance of the UDT is null.

  • Implement a public static property named Null that returns a null instance of the UDT.

  • Implement public static ToString( ) and Parse( ) methods to convert to and parse from a string representation of the type. The Parse( ) method takes a single argument of type SqlString.

  • Implement the IXmlSerializable interface if all public fields and properties are XML serializable or marked with the XmlIgnore attribute. The IXmlSerializable interface provides custom XML serialization and deserialization by explicitly defining how an object is serialized and deserialized by the XmlSerializer class. The IXmlSerializable interface has three methods: GetSchema( ), ReadXml( ), and WriteXml( ).

  • Implement Read( ) and Write( ) methods if user-defined serialization is specified by implementing the IBinarySerialize interface.

A CLR UDT has the following restrictions:

  • Public names cannot exceed 128 characters in length and must conform to SQL Server naming rules for identifiers.

  • Only fields, properties, and methods defined in the type are callable from T-SQL. SQL Server is not aware of the inheritance hierarchy among UDTs .

  • Members other than the class constructor cannot be overloaded.

  • Static members must be declared either as constants or as read-only when the assembly permission is specified as SAFE or EXTERNAL_ACCESS.

The SqlMethod attribute is used to define characteristics of a UDT method or property. The syntax of the SqlMethod attribute is as follows:

     SqlMethod [ ( method-attribute [ ,... ] ) ]     method-attribute::=         function_attribute        | IsMutator   = { true | false }       | OnNullCall = { true | false }       | InvokeIfReceiverIsNull= { true | false } 

where:


function_attribute

The SqlMethod attribute inherits all properties of the SqlFunction attribute discussed in the "Scalar-Valued Functions" section earlier in this chapter.


IsMutator

Specifies whether the method can modify the UDT instance. SQL Server looks for the IsMutator property of the SqlMethod attribute on void public methods in the UDT. If the IsMutator property is true on a void method, SQL Server marks the method as a mutatora method that causes state change in the instance. Mutator methods are not allowed in queriestheir use is restricted to assignment statements or data modification statements. The default value of the IsMutator property is false.


OnNullCall

Specifies whether the method is evaluated if one or more null arguments are supplied. If false, the method returns null without evaluating the method if one or more of the arguments are null. If true, the method is evaluated regardless of whether arguments are null. The default value is true.


InvokeIfReceiverIsNull

Specifies whether SQL Server should invoke the method on a null reference. A value of true invokes the method on a null reference. The default value is false.

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

  1. Using the Visual Studio 2005 IDE, create a new SQL Server project named PolygonUdt.

  2. Create an aggregate item in the project. Name the item Polygon.cs. Empty code blocks are created for the four required methods.

  3. Replace the code in Polygon.cs with the following code:

         using System;     using System.Data;     using System.Data.Sql;     using System.Data.SqlTypes;     using Microsoft.SqlServer.Server;     [Serializable]     [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)             {                 return Null;             }         }         public int NumberSides         {             get { return numberSides; }             set             {                 if (value > 2)                 {                     numberSides = value;                     isNull = false;                 }                 else                     isNull = true;             }         }         public double SideLength         {             get { return sideLength; }             set             {                 if (value > 0)                 {                     sideLength = value;                     isNull = false;                 }                 else                     isNull = true;             }         }         [SqlMethod]         public double Area(  )         {             if (!isNull)                 return .25 * numberSides * Math.Pow(sideLength, 2) *                      (1 / Math.Tan(Math.PI / numberSides));             else                 return 0;         }         [SqlMethod(IsMutator = true, OnNullCall = false)]         public void SetValue(int numberSides, double sideLength)         {             if (numberSides > 2 && sideLength > 0)             {                 this.numberSides = numberSides;                 this.sideLength = sideLength;                 this.isNull = false;             }             else                 isNull = true;         }     } 

    The UDT is implemented as a struct marked with both a Serializable attribute and a SqlUserDefinedType attribute specifying Native serialization. A UDT must support both XML and binary serialization.

    The UDT contains two private fieldsnumberSides and sideLength. The NumberSides and SideLength properties are used to get and set the value of these fields.

    The UDT implements the INullable interface with the method IsNull( ), which simply returns the value of a private field, isNull, that keeps track of whether the polygon UDT is null. The UDT also implements the Null( ) method, which instantiates and returns a null instance of the Polygon UDT.

    The UDT implements the required ToString( ) and Parse( ) methods. The ToString( ) method displays the value of the polygon as a string. The Parse( ) method converts a string to the polygon UDT and is used by the SQL Server CONVERT and CAST functions.

    The UDT implements two methods. The Area( ) method returns the area of the polygon. The SetValue( ) method changes the number of sides and the length of the sides in the polygon UDT.

  4. Build the solution.

  5. Register the assembly and create the polygon UDT by executing the following T-SQL statement in SQL Server Management Studio:

         USE ProgrammingSqlServer2005     GO     CREATE ASSEMBLY Polygon     FROM 'C:\PSS2005\PolygonUdt\PolygonUdt\bin\Debug\PolygonUdt.dll'     GO     CREATE TYPE Polygon     EXTERNAL NAME Polygon 

  6. Execute the following T-SQL statements:

         DECLARE @p Polygon     SET @p = CONVERT(Polygon, '5, 4.2')     PRINT @p.IsNull     PRINT @p.ToString(  )     PRINT @p.NumberSides     PRINT @p.SideLength     PRINT @p.Area(  )     SET @p.SetValue(7, 3)     PRINT @p.ToString(  )     PRINT @p.Area(  ) 

    The results are shown in Figure 5-8.



Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net