IMPLEMENTING LDIM case 2 : units = ft ;

IMPLEMENTING LDIM case 2 : units = ft ; break; case 3 : units = yd ; break; } } Read starts by just skipping over the part of the stream that is used for sorting. Next it reads in the value of the value field. Lastly it reads the token for the unitsfield and fills it accordingly. User-Defined Type Properties and Fields Public fields and properties in the class that implements a user-defined type are exposed in SQL Server. The fields and properties are exposed by name. A variable or column can access a public field or property of a user- defined type by adding a suffix that consists of a . followed by the name of the field or property. The field or property can then be referenced in the same way as any other variable or column. Listing 5-15 shows this syntax in a SQL batch. Listing 5-15: Field/Property Access Syntax DECLARE @L as Longitude SET @L = CONVERT(Longitude, 50 3 32 W ) PRINT @L.degrees 50 PRINT @L.minutes 3 PRINT @L.seconds 32 PRINT @L.direction W SET @L.direction= E Longitudeis a user-defined type that has degrees, minutes, seconds, and direction as public fields in its implementation. The SQL batch in Listing 5-15 prints each of these fields and then changes the direction to E . Listing 5-16 shows a modified implementation of LDim that makes unitsa public field.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

USER-DEFINED TYPES AND AGGREGATES point numbers does not

USER-DEFINED TYPES AND AGGREGATES point numbers does not sort as we would want. There is no need to understand these implementations. The reason for showing them is twofold. One is to show a complete implementation of Format.UserDefined. The other is to emphasize that using Format.Native is always preferable whenever possible. Write and Read are not implemented when Format. Nativeis used. The major part of the code in BinaryWriter is converting the value field to a UInt64. This is done by creating a UInt64 that is proportional to the log of the value field. This not used to store the value of the field; it is just used as a value to sort the field. The actual serialization is done at the end of the Write method, where BinaryWriterthat was passed in as a parameter was used. The first thing that it serialized is the logarithmic value that will be used to order LDims. When SQL server attempts to sort an LDimcolumn, it will do so by sorting its binary representation, starting with the first byte that was passed to the stream into the Writemethod. After the logarithmic value is written, the value of the value field is written using the native format for a double. After that has been done, the units field is serialized. The technique used here is to tokenize the unitsfield by saving a 1 for inches, 2 for feet, 3 for yards, and 0 for nullvalues. The Read implementation is shown in Listing 5-14. It is much easier than the Write implementation because it can just throw away the part of the implementation used for sorting. Listing 5-14: Implementation of Read public void Read(System.IO.BinaryReader r) { // skip over order marker r.BaseStream.Seek(8L, SeekOrigin.Current); value = r.ReadDouble(); byte c = r.ReadByte(); switch (c) { case 0: SetNull(); break; case 1 : units = in ; break;

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

IMPLEMENTING LDIM { 110 = (UInt64.MaxValue >>1) -

IMPLEMENTING LDIM { 110 = (UInt64.MaxValue >>1) – (UInt64)l >> 2; // result is in upper quarter of lower half of positive numbers } else { l10 = ( UInt64.MaxValue >>1) – ( UInt64.MaxValue >>2) ( UInt64)(-l >> 2); // result is in lower quarter of lower half of positive numbers } } } // serialize out the high byte first // ordering and sorting starts with the // first byte output for (int index = 7; index >= 0; index ) { w.Write((byte)(l10 >> (8 * index))); } w.Write(value); if (units == in ) { w.Write((byte)1); } if (units == ft ) { w.Write((byte)2); } if (units == yd ) { w.Write((byte)3); } if (units == null) { w.Write((byte)0); } } The implementations of Write and Read determine the MaxByteSize. It is the number of bytes written by Write. Write first outputs 8 bytes, which are used to sort LDim values. Next it writes out the actual value, which is a double another 8 bytes. This is followed by a single byte used to represent the units. MaxByteSize= 8 + 8 + 1, which is 17. The implementations of Write and Read are tedious and complicated. They must take into account the physical representation of values in memory. Most of this is due to the fact that the binary representation of floating

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

USER-DEFINED TYPES AND AGGREGATES w.Write((byte)0xFF); } } else

USER-DEFINED TYPES AND AGGREGATES w.Write((byte)0xFF); } } else { Dim v; v.value = value; v.units = units; // normalize to inches if (v.units == ft ) { v.value *= 12; } if (v.units == yd ) { v.value *= 36; } v.units = in ; // serialize the approx log10 of value first // so you can squeeze it into a long // note that this order is only approximate UInt64 l10 = UInt64.MaxValue >> 1; // there is a bit of a dance here // to make sure that the negative and // small number come out in the // right order if (v.value != 0.0) { if (v.value > 0.0) { Int64 l = (Int64)(Math.Log10(v.value) * 29921268260570000.0); if (l >= 0) { // divide by 4 l10 = (UInt64)l >> 2; // add half of max l10 += (UInt64.MaxValue >>2) + (UInt64.MaxValue >>1); // result is in upper quarter of positive numbers } else { // divide by 2 l10 = (UInt64)(-l >> 2); l10 += (UInt64.MaxValue >>2); // result is in lower quarter of upper half of positive numbers } } else { Int64 l = (Int64)(Math.Log10(-v.value) * 29921268260570000.0); if (l >= 0)

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Tomcat Web Hosting services

IMPLEMENTING LDIM The format used by SQL Server

IMPLEMENTING LDIM The format used by SQL Server for the byte stream that it saves on disk is determined by the implementation of IBinarySerialize. The value field of LDim is a double. A detailed explanation of the representation of floating point numbers is beyond the scope of this chapter, but suffice it to say that the sort order of the representation in memory of a floating point number is not the same as the sort order of the numbers they represent. Listing 5-12 shows a SQL batch file that demonstrates this. Listing 5-12: Order of Floating Point Numbers create table Numbers ( F float ) go Insert into S values (1) Insert into S values (0) Insert into S values (-1) go select * from Numbers order by F go F -1 0 1 select * from Numbers order by Convert(binary, F) go F 0 1 -1 When the rows in the Numbers table are sorted by the F column, the rows are ordered as we would expect them to be. However, if the rows are ordered according to their binary representation, 0 comes before 1 and 1 comes before 1 not what you would expect. Listing 5-13 shows the implementation of Write. Listing 5-13: Implementation of Write public void Write(System.IO.BinaryWriter w) { if (IsNull) { for (int index = 0; index < = 9; index++) { Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

USER-DEFINED TYPES AND AGGREGATES } // parse the

USER-DEFINED TYPES AND AGGREGATES } // parse the input Match m = vu.Match(s.Value); LDim d = new LDim(); // get the units // the string for units will have the proper // value because it was checked by the regex d.units = m.Result( ${u} ); // get the value. Again it will be a // floating point number, the regex // checked it d.value = double.Parse(m.Result( ${v} )); return d; } Notice that the regular expression used to test and parse the input is held in a static read-only member of LDim. There is a cost in constructing a regular expression, and putting it into a static member of the class means it need be constructed only once and can then be reused every time Parseis called. Once the input string has been validated as having the proper format, the regular expression is used again to crack out the value and the units. Notice that the double.Parse at the end of the implementation can never fail, because its input has already been validated as being a floating point number. Implementing IBinarySerialize Implementing IBinarySerialize is the most difficult part of creating a user-defined type. The implementation not only determines how an instance of a user-defined type will be saved on disk, it also determines how it will be sorted. IBinarySerialize is not implemented for user- defined types that set the SqlUserDefinedTypeAttribute Format property to Format.Native, which is the prime reason to use Nativewhen it is possible. The IBinarySerializeinterface has two methods: Read, which passes in a System.IO.BinaryReader, and Write, which passes in a System. IO.BinaryWriter. When SQL Server needs to read the data it has stored on disk for a user- defined type, it calls the Read method. The implementation of the Read method reads the stream, passes it through the BinaryReader, and reconstructs the state of the user-defined type. When SQL Server needs to write a user-defined type to disk, it calls the Write method, and the implementation of the Write method writes into the BinaryWriterthe state of the user-defined type.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

IMPLEMENTING LDIM user-defined type. Whenever SQL Server needs

IMPLEMENTING LDIM user-defined type. Whenever SQL Server needs to convert a string to an LDim, it will call the Parsemethod. The following is some T-SQL code that will call the Parsemethod of LDim. CREATE TABLE Lengths { Length LDim } 3 ft must be converted to an LDIM to be inserted SQL Server will call the Parse method on LDim to do this INSERT INTO Lengths VALUES (N 3 ft ) Before it converts the string, the Parse method must first check to be sure that the string is formatted validly. If it is not, Parsemust produce an error that indicates this. Manually writing code that will completely check the format of a string and parse it typically is a very tedious task. The .NET Framework, however, has the RegEx class, which supports regular expressions as used in the Perl language. Taking advantage of this capability greatly reduces the effort to implement Parse. The RegExclass has the ability to match a string to a regular expression. When used this way, the regular expression is, in effect, a set of rules that define the format of a string. When a string matches the regular expression, it is validly formatted according to those rules. The same regular expression that is used to check the format of a string can be used to parse the string. A full discussion of regular expressions is beyond the scope of this chapter, but they are documented in MSDN. Listing 5-11 shows the implementation of Parsefor LDim. Listing 5-11: Implementation of Parse // regular expression to test and parse static readonly string fp = @ -?([0-9]+(.[0-9]*)?|.[0-9]+) ; static readonly Regex vu = new Regex(@ (? + fp + @ ) (?in|ft|yd) ); public static LDim Parse(SqlString s) { // null in, null out if (s.IsNull || s.Value.ToLower() == null ) return Null; // check to make sure input is // properly formatted if (!vu.IsMatch(s.Value)) { throw new ApplicationException( Bad format , null);

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

USER-DEFINED TYPES AND AGGREGATES Listing 5-9: SQL Server

USER-DEFINED TYPES AND AGGREGATES Listing 5-9: SQL Server Calling ToString create a Table that uses LDim CREATE TABLE Lengths ( Length LDim ) select something from that table the CONVERT function will call ToString on the implementation of LDim SELECT CONVERT(CHAR, Length) FROM Lengths ToString is a virtual function in the System.Object class. Every CLR class inherits System.Object whether or not it is explicitly specified. An implementation of a user-defined type must override this method. ToString returns a string that is considered the lexical representation of the value. Listing 5-10 shows the LDimimplementation of ToString. Listing 5-10: Implementation of ToString public struct LDim : INullable { private double value; private string units; public override string ToString() { // check to see if units is null // if it is this instance is // representing a null if(null == units) { return null ; } return String.Format( {0:d} {s} , value, units); } // rest of implementation } Note that when LDimis representing a null, it returns the string null . Otherwise, it formats a string that consists of a number, whitespace, and the unitsstring. Implementing Parse Parseis the functional complement of ToString. It must be a public static method. It takes a string as input and converts it into an instance of the

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

IMPLEMENTING LDIM guaranteed to have all its fields

IMPLEMENTING LDIM guaranteed to have all its fields initialized to zeros. This produces an instance of LDim where units contain all zeros that is, a null reference which is just what we want. In order to ensure that nulls sort properly, it is important that all instances of LDim that are representing null have identical values. However, because LDim is a struct, we are also guaranteed that value will be all zeros too. A class that implements a user-defined type as a reference type that is, a class in C# or VB.NET should use a different technique for implementing Null. The implementation shown for LDim would not work well for a class that is a reference type, because each time the Nullproperty was used, a different instance of the class would be allocated on the heap. This would not impair the functionality of Null, but it would make a lot of extra work for the garbage collector because it has to clean up things allocated on the heap. This is not an issue for LDim, because structs are not allocated on the heap. Listing 5-8 shows an implementation of Null for a class that is a reference type. Listing 5-8: Implementation of the Null Property for a C# Class public class PartNumber : INullable { static readonly PartNumber nullPart = new PartNumber(); public LDim Null { get{ return nullPart; } } // rest of implementation } This implementation of Null always returns the same instance of PartNumber, so there is no cleanup work for the garbage collector to do. Also, as noted in Chapter 2, static members of a class must be marked as read-only to be loaded as SAFEor EXTERNAL_ACCESS. Implementing ToString SQL Server will call the ToString method on a class whenever it needs a string representation of it. Listing 5-9 shows a query that uses the T-SQL CONVERT function on an LDim type column to convert it to a CHAR. The CONVERT function will call ToString on the class that implements LDim.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services

USER-DEFINED TYPES AND AGGREGATES we will see later.

USER-DEFINED TYPES AND AGGREGATES we will see later. In addition, classes that do not implement IBinary Serialize that is, those that set Format to Native in the SqlUser DefinedTypeAttribute can also take advantage of the smaller byte stream on disk. Since the units field can hold either a null or a reference to a string, an LDim class can put a null in the units field to indicate that it is representing a null. Note that encoding a representation like this has been used many times before. For example, a floating point number that is infinity or NaN just uses an encoding that can never occur when it is representing an actual number. Listing 5-6 shows the LDim implementation of INullable that uses this encoding technique. Listing 5-6: Encoded Implementation of INullable public struct LDim : INullable { private double value; private string units; // read only property public bool IsNull { get { return units == null;} } // rest of implementation } Implementing the Null Property LDimmust implement a public, static property named Nullthat returns an instance of LDim that represents a null. To do this, LDim can just return a new instance of the LDimclass. Listing 5-7 shows the LDimimplementation of the Nullproperty. Listing 5-7: Implementation of the Null Property public struct LDim : INullable { public static LDim Null { get{ return new LDim();} } // rest of implementation } This implementation is simple because LDim is a struct that is, it derives from System.ValueType. A new instance of a ValueType is

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Java Web Hosting services