SQL Server 2005 User-Defined Types

SQL Server 2005 has the .NET Framework embedded within it, which allows the use of managed languages for creation of objects such as stored procedures, function, aggregates, and types. This book is not dedicated to SQLServer 2005, so most of these topics are not covered, but the use of userdefined types (UDTs) affects client applications such as ASP.NET pages, so is worthy of coverage here.

By "custom types," we mean any object defined within .NET. SQL Server already has support for native types, such as string, dates, numbers, and so on, but there may be other types you want to store. For example, you might want to store dates and times as separate columns, or perhaps you have a more complex object such as Longitude or Latitude for a mapping operation. You can create these UDTs as .NET structures and store them within SQL Server 2005 as a native typeso the column type is your custom type.

Listing 5.24. Using Asynchronous Tasks

private WebRequest _request; protected void DetailsView1_DataBound(object sender, EventArgs e) {   if (GridView1.SelectedIndex != -1)   {     PageAsyncTask task = new PageAsyncTask(       new BeginEventHandler(BeginAsyncOperation),       new EndEventHandler(EndAsyncOperation),       new EndEventHandler(TimeoutAsyncOperation),       null);     RegisterAsyncTask(task);   } } IAsyncResult BeginAsyncOperation(object sender, EventArgs e,   AsyncCallback cb, object state) {   string city = DetailsView1.Rows[6].Cells[1].Text;   string qry =     string.Format("http://xml.weather.yahoo.com/forecastrss?p={0}",     city);   _request = WebRequest.Create(qry);   return _request.BeginGetResponse(cb, state); } void EndAsyncOperation(IAsyncResult ar) {   using (WebResponse response = _request.EndGetResponse(ar))   {     using (StreamReader reader = new       StreamReader(response.GetResponseStream()))     {       DataSet ds = new DataSet();       ds.ReadXml(reader);       WeatherDisplay.DataSource = ds.Tables["forecast"];       WeatherDisplay.DataBind();     }   } } void TimeoutAsyncOperation(IAsyncResult ar) {   NoWeather.Visible = true; }

Let's take a look at location-based storage with Longitude and Latitude classes; with the increasing popularity of GPS systems, especially among sports enthusiasts (see http://www.expansys.com/p_sportsdo.asp for some cool PocketPC applications), and the uptake of free mapping services such as Virtual Earth, the storage of trip information can provide a great way to document your activities. Using SQL Server 2005, you can natively store the GPS details as a UDT, allowing them to be easily used in mapping applications.

A UDT is simply a structure in .NET that follows a set of rules:

• It is decorated with the SqlUserDefinedType attribute, to allow automatic type creation when deployed from Visual Studio 2005.

• The structure must implement the INullable interface, because SQL Server types can be null.

• You must implement the Parse method, which parses string values and converts them to the internal storage types.

• You must implement the ToString method, which converts the internal storage into a readable form.

Other properties and methods can be implemented, depending upon the requirements of the UDT. For the Latitude type, you could store the latitude in a decimal format, along with properties for Degrees, Minutes, Seconds, and the Hemisphere. This is shown in Listing 5.25, which details the minimum requirements for a SQL UDT. An equivalent UDT for Longitude is included in the downloadable samples, but is not discussed in depth; you'll see that it is very similar to the latitude.

Let's break this code down to see exactly what each section does, and why.

UDT Attributes

The first thing to notice is that the structure is attributed, first with Serializable; all UDTs must be serializable, because SQL Server serializes the data for storage internally in the database. The second attribute, SqlUserDefinedType, does two things. First, it defines that this class is a UDT, which Visual Studio uses when deploying the assembly. Second, it defines characteristics of the type. The two in use here are Format. Native, which indicates that only native types are used and that SQL Server can perform the serialization, and IsByteOrdered, which indicates that the serialized data can be used for ordering and comparison.

Listing 5.25. The Latitude User Defined Type

[Serializable] [SqlUserDefinedType(Format.Native, IsByteOrdered = true)] public struct Latitude : INullable {   internal float _latitude;   internal short _degrees;   internal short _minutes;   internal short _seconds;   internal bool _hemisphere;   Latitude(float latitude)   {     _latitude = latitude;     _degrees = (short)latitude;     float m = (float)_latitude - _degrees;     _minutes = (short)(m * 60);     float s = (float)m - _minutes;     _seconds = (short)(s * 60);     _hemisphere = (latitude >= 0);   }   public static Latitude Parse(SqlString s)   {     string val = s.Value.Trim().ToLower();     // if null string or the string "null" is     // passed in the return a null Longitude instance     if (s.IsNull || (val == "null"))       return Null;     float lat = float.Parse(val);     if (lat < -90 || lat > 90)       throw new ArgumentException("Latitude must be between           -90 and 90");     return new Latitude(lat);   }   public override string ToString()   {     return _latitude.ToString();   }   public bool IsNull   {     get {return _latitude == float.MinValue;}   }   public static Latitude Null   {     get {return new Latitude(float.MinValue);}   }   public short Degrees   {     get { return _degrees; }   }   public short Minutes   {     get { return _minutes; }   }   public short Seconds   {     get { return _seconds; }   }   public string Hemisphere   {     get { return _hemisphere ? "N" : "S"; }   } }

The SqlUserDefinedType attribute supports the following properties:

• Format, which can be Native or UserDefined. If Native, SQL Server performs the serialization. For native serialization, all the fields must themselves be serializable types. If UserDefined is set, then you must implement IBinarySerialize and the Read and Write methods to serialize the fields yourself.

• IsByteOrdered, which when true allows SQL Server to use the serialized data for ordering and comparison, and to use the column for indexing.

• IsFixedLength, which when true indicates that all instances of the UDT have the length equal to MaxByteSize. This is only relevant when using UserDefined serialization.

• MayByteSize is the maximum size of the UDT instance when serialized. This is only relevant when using UserDefined serialization.

• Name, which indicates the name of the UDT and is only used by Visual Studio.

• ValidationMethodName, which is the name of the method used to validate instances of the UDT. This is used when the UDT has been deserialized from an untrusted source.

Serializable Types

One important point to notice is the types you can use as fields, which must be serializable types. This means that your fields must be one of the following types: bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney.

Take a look at the internal fields used in the UDT:

internal float _latitude; internal short _degrees; internal short _minutes; internal short _seconds; internal bool _hemisphere;

The degrees, minutes, and seconds are as expecteda shortbecause they only store small values. The value for latitude would perhaps be stored as a decimal type, but this isn't serializable, so float has been used. You could ignore this entirely, because the latitude can easily be recalculated from the other fields, but it's been included here to make the rest of the UDT simpler. The hemisphere (which would be N or S) is also an unexpected typebool instead of string or char, because string and char are not serializable. Instead, a boolean flag is set to true for the Northern Hemisphere and false for the Southern Hemisphere.

Constructors

The constructor of a UDT is just like the constructor of any other type, and serves to initialize the type. In this example, a decimal (as in a description of the value, rather than the data type) latitude is used as the parameter, and the internal values are calculated from that.

Latitude(float latitude) {   _latitude = latitude;   _degrees = (short)latitude;   float m = (float)_latitude - _degrees;   _minutes = (short)(m * 60);   float s = (float)m - _minutes;   _seconds = (short)(s * 60);   _hemisphere = (latitude >= 0); }

Multiple constructors are allowed, so it would be perfectly possible to have the following:

Latitude(short degrees, short minutes, short seconds,          string hemisphere) {   _latitude = DMStoDecimal(degrees, minutes, seconds);   _degrees = degrees;   _minutes = minutes;   _seconds = seconds;   _hemisphere = (hemisphere == "N"); }

This is useful if the type is being used outside of SQL Server, but it is less useful within SQL because the constructor isn't used directly when values are created. Instead, the values are parsed.

Parsing Values

To enter values into the SQL table, you use the INSERT statement, and that doesn't change just because a column is a UDT. For UDT columns, the INSERT statement calls the Parse method of the UDT, passing into it a SqlString type of the value to be inserted. Parse must then break this string apart, converting it into the internal types.

public static Latitude Parse(SqlString s) {   string val = s.Value.Trim().ToLower();   // if null string or the string "null" is   // passed in the return a null Longitude instance   if (s.IsNull || (val == "null"))     return Null;   float lat = float.Parse(val);   if (lat < -90 || lat > 90)     throw new ArgumentException("Latitude must be between         -90 and 90");   return new Latitude(lat); }

It is important to remember that SQL types can be NULL, so you must plan for this. The Parse method just seen takes the string value, removes any white space, and converts it to lower case before checking for a null value on the stringSqlString is not a standard string, because it can be null. The string is then converted to a float and validated before being used to return a new instance of the UDT.

Outputting Values

To extract values from a UDT column, you specify the column name in SQL, which in turn calls the ToString method of the type. For the latitude type, this is simplejust returning a string value of the internal latitude.

public override string ToString() {   return _latitude.ToString(); }

You can, of course, output the value in any form you like, perhaps converting it to its individual degrees, minutes, and seconds.

Handling Null Values

SQL can store null values, so the UDT must cope with this, by having two properties with defined names. The first, IsNull, indicates whether the UDT is null; in the latitude UDT, this compares an internal _degrees field with a known value that is invalid for a legal value of degrees. You could easily store a flag to indicate the null status, but that requires an additional field, so an existing field is used.

The second property is Null, which returns a UDT instance, initialized with the value defined as our null value. Like IsNull, you could easily do this another way, perhaps having another constructor that takes a flag indicating that a null instance should be created.

public bool IsNull {   get { _latitude == float.MinValue;} } public static Latitude Null {   get {return new Latitude(float.MinValue);} }

Adding Properties

Properties aren't a required part of creating a UDT, but they do allow flexibility in its use. Properties of a UDT are exactly the same as class properties.

public short Degrees {   get { return _degrees; } } public short Minutes {   get { return _minutes; } } public short Seconds {   get { return _seconds; } } public string Hemisphere {   get { return _hemisphere ? "N" : "S"; } }

For example, when selecting a UDT column, the ToString method is used to return the value. For example, if Lat was a column of type Latitude, you could do the following:

SELECT Lat FROM TripDetails

If properties are supported in the UDT, these can be added directly to the column.

SELECT Lat.Degrees FROM TripDetails

SQL Server doesn't use these directly, but they are useful when accessing the UDT from client applications.