Listing 5 shows the C# code you use to implement the ToString() method, which checks to see whether the Latitude value is null and returns "null" if it is. If the Latitude value isn't null, the GetParts() method decodes the value_ field, then uses the results to build a Latitude string. Note that the string "\xB0" is the way you represent the degree character (°) in C#.
The complement of the ToString() method is the Parse() method, which Listing 6 shows. The Parse() method parses a string and returns a Latitude value that represents it—in this example, all four fields. This method must also validate the string passed in to it to be sure that the string is properly formatted. The Parse() method's input parameter is a SqlString, which is similar to the string type in C# except that SQLString represents a null string through its IsNull property.
The first thing the Parse() method does is determine whether SQL Server passed in a null string or a string whose text is "null" when it called Parse(). If so, the method returns a null for the Latitude value. Otherwise, Parse() uses a regular expression to make sure that the string is properly formatted. If the formatting isn't correct, SQL Server throws an error.
If SQL Server passed in a valid string, the code uses the same regular expression to parse the string and extract the values of the fields that make up the Latitude value, then returns the Latitude value. The code checks each extracted value to make sure that it's in the proper range; if any value is outside its specified range, the code throws an error.
The use of a regular expression greatly simplifies the implementation of the Parse() method. The regular expression is a private static member of the Latitude class, which means the regular expression is precompiled when you load the code, and SQL Server uses it in the compiled form repeatedly. Also note that the static member is marked as readonly. Without this designation, the Latitude.dll assembly could compromise SQL Server's security and reliability.
Compiling and Cataloging
You use the C# compiler that comes with SQL Server 2005's PDC bits to compile the Latitude.cs file you downloaded and produce an assembly. You have to add that assembly and the Latitude data type to a database before you can use them. This step is called cataloging. Listing 7 shows a T-SQL batch that catalogs the Latitude type. In this listing, the CREATE ASSEMBLY command loads the latitude.dll file into the database. PERMISSION_SET=SAFE means that this assembly contains only managed code and generally performs only computational kinds of operations. SQL Server will analyze the assembly when it loads it, and if the assembly doesn't meet the criteria for PERMISSION_SET=SAFE, SQL Server will raise an error and won't load the assembly. Assemblies that SQL Server loads with PERMISSION_SET=SAFE are as secure and reliable as any T-SQL batch.
Once SQL Server has cataloged the assembly, it catalogs the Latitude type. In Listing 7's code, the string following the CREATE TYPE command is the name of the type. The EXTERNAL NAME clause specifies an assembly and a class in that assembly. The string Latitude: means that the type is located in the assembly named Latitude that Listing 7's code added to SQL Server. The string [Cartography.Latitude] refers to the class name Latitude in that assembly's Cartography namespace. The names of the namespace and class are case-sensitive and must match the case in the implementation, even if you implemented the code in a language such as Visual Basic .NET, which ignores case.
This article shows how to implement a simple UDT in SQL Server 2005. Remember, for your UDT to work, you need a few basic elements: a class that has an internal representation of the type, a way to represent an instance of the type as a string, and a way to handle nulls in the data. SQL Server 2005 offers other innovations for UDTs such as ways to create a user-defined sort order, expose fields in the type directly to T-SQL, include arithmetic functions, and implement user-defined serialization. In addition, the next release of Visual Studio .NET, code-named Whidbey, directly supports creating UDTs. I hope this introduction will inspire you to explore these innovations.
End of Article
Prev. page
1
2
[3]
next page -->
