UDT Basics
Each UDT needs a string representation, an internal representation, and a null representation. The first representation is obvious: You need to be able to describe a latitude as a string, such as 15°2'39"N. The internal representation is how SQL Server will store on disk the bits representing latitude. Last, because SQL Server scalar data types can be null, you need a way to handle that, too.
You implement UDTs in SQL Server 2005 by designing a public class that has several well-known methods, properties, and attributes. Listing 2 shows a skeleton implementation of the Latitude class that illustrates the basic features of a typical UDT.
The SqlUserDefinedType attribute in Listing 2 is a marker showing that the Latitude class is implementing a UDT. The angle_ field shows the internal representation that SQL Server will use to store the value. The ToString() and Parse() methods are for implementing the string representation. Finally, the IsNull and Null properties are for implementing the null representation. The minimum requirements for implementing a UDT are that the data type
- is a public class that has SqlUserDefinedType and Serializable attributes
- overrides the ToString() method to produce a string representation
- has a public Parse() method to interpret the string representation
- implements the IsNull property of the INullable interface
- implements the public static Null property to produce a null instance
- is added to the database by using CREATE ASSEMBLY and CREATE TYPE
The construction parameter of the SqlUserDefinedType attribute specifies the technique SQL Server will use to save and restore the internal representation on disk. You can choose one of several available formats, but Format.Native is more straightforward than the other available formats. This article doesn't cover using the other formats.
The SqlUserDefinedType attribute's IsByteOrdered parameter lets you know whether you can order the UDT. If IsByteOrdered is true, SQL Server lets you use the UDT in ORDER BY clauses and comparison expressions.
Implementing Latitude
Now, you can use the C# code in Listing 2 to implement the Latitude UDT. As I mentioned, the Latitude.cs file online contains the source code for implementing Latitude. Latitude.dll is the assembly that the compiler produces when you compile Latitude.cs.
The Int32 angle_ field in the Latitude class is SQL Server's internal representation of the Latitude UDT. You can see in Listing 2 that the SqlUserDefinedType attribute uses Format.Native. The SqlUserDefinedType attribute also specifies IsByteOrdered=true. This specification means that SQL Server will let you use the Latitude type in magnitude comparisons and ORDER BY clauses.
The Latitude UDT has a helper method named Angle(), which Listing 3 shows. You use the Angle() method to encode degrees, minutes, seconds, and direction into an Int32 data type so that SQL Server can store the complete value in the angle_ field.
The encoding is straightforward except for one part: The code converts degrees and minutes to seconds and adds them to the total seconds so that the values will be properly ordered and fit into an int data type in .NET. If the value you're specifying is supposed to represent a point south of the equator, the direction is negative (S) and the code converts the result to a negative number. Before the code converts the value, it adds 1 to it to ensure that 0°0'0"N has a different internal representation than 0°0'0"S. This distinction is important because, although these values are the same, their lexical representations are different.
Latitude has another helper method, GetParts(), which is the complement of the Angle() method and decodes the angle_ field into degrees, minutes, seconds, and direction. The following code shows the signature for GetParts():
internal void GetParts(Int32 angle, ref short degrees,
ref byte minutes,
ref byte seconds,
ref bool positive)
The source code in the Latitude.cs file contains the code you use to implement the GetParts() method.
The Latitude UDT's last helper method is called EncodeNullAngle(). This method sets the angle_ field to a value that represents a null. Latitude uses Int32.MinValue to represent a null; it can use this value because the Angle() method will never produce Int32.MinValue. You implement the EncodeNullAngle() method like this:
internal void EncodeNullAngle()
{
angle_ = Int32.MinValue;
}
The EncodeNullAngle() method makes implementing the IsNull and Null properties easy. Listing 4 shows the code you use to implement these properties. The IsNull property checks to see whether the angle_ field contains the value that represents a null, in this case, Int32.MinValue. The static Null property creates an instance of a Latitude UDT, then uses the EncodeNullAngle() method to set the instance's value to null. SQL Server internally uses the IsNull property to check for null Latitude values.
Prev. page
1
[2]
3
next page 
