User-Defined Types

User-defined types (UDTs) can be used similarly to normal SQL Server data types to define the type of a column in a table. With older versions of SQL Server it was already possible to define UDTs. Of course, these UDTs could only be based on SQL types, such as the ZIP type shown in the following code. The stored procedure sp_addtype allows you to create user-defined types. Here the user-defined type ZIP is based on the CHAR data type with a length of 5. NOT NULL defines that NULL is not allowed with the ZIP data type. By using ZIP as a data type, it is no longer necessary to remember that it should be 5 char long and not null:

 EXEC sp_addtype ZIP 'CHAR(5)', 'NOT NULL' 

With SQL Server 2005, UDTs can be defined with CLR classes. However, this feature is not meant to create a Person class to have a Person data type. SQL Server is a relational data store, and this is still true with UDTs. You cannot create a class hierarchy of UDTs, and it is not possible to reference a part of a UDT type with a SELECT statement. If parts of a person must be accessed or sorted (for example, the Firstname or Lastname), it is still better to define columns for first name or last name or to use the XML data type.

UDTs are meant for very simple data types. Before .NET, it was also possible to create custom data types, for example, the ZIP data type. With UDTs it is not possible to create a class hierarchy, and they are not meant to get complex data types to the database. One requirement of a UDT is that it must be convertible to a string, because the string representation is used to display the value. How the data is stored within SQL Server can be defined: either an automatic mechanism can be used to store the data in a native format, or you can convert the data to a byte stream to define how the data should be stored.

Creating UDTs

Here you look at how to create a user-defined type with Visual Studio 2005. Using the database project and the template for a User-Defined Type with Visual Studio 2005, the base functionality of a custom type is already defined:

 using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)] public struct Type1 : INullable { public override string ToString() { // Replace the following code with your code return ""; } public bool IsNull { get { // Put your code here return m_Null; } } public static Type1 Null { get { Type1 h = new Type1(); h.m_Null = true; return h; } } public static Type1 Parse(SqlString s) { if (s.IsNull) return Null; Type1 u = new Type1(); // Put your code here return u; } // This is a place-holder method public string Method1() { //Insert method code here return "Hello"; } // This is a place-holder static method public static SqlStringMethod2() { // Insert method code here return new SqlString("Hello"); } // This is a place-holder field member public int var1; // Private member private bool m_Null;  } 

You change the implementation to represent the type Coordinate. Coordinate is used to represent the world coordinates longitude and latitude.

The struct Coordinate implements the interface INullable. The interface INullable is required for UDTs because database types can also be null. The attribute [SqlUserDefinedType] is used for automatic deployment with Visual Studio for UDTs. The argument Format.Native defines the serialization format to be used. Two serialization formats are possible: Format.Native and Format.UserDefined. Format.Native is the simple serialization format where the engine performs serialization and deserialization of instances. This serialization allows only blittable data types. With the Coordinate class the data types to serialize are of type int and bool, which are blittable data types. A string is not a blittable data type. Using Format.UserDefined requires the interface IBinarySerialize to be implemented. With this interface Read() and Write() methods must be implemented for serialization of the data to a BinaryReader and a BinaryWriter.

Note

Blittable data types have the same memory representation in both managed and unmanaged memory. Conversion is not needed with blittable data types. Blittable data types are byte, sbyte, short, ushort, int, uint, long, ulong, and combinations of these data types such as arrays and structs that only contain these data types.

Important

If wrong data types are used with the native format, you will not get an error during compile time; instead you will get an error during deployment.

 [Serializable] [SqlUserDefinedType(Format.Native)] public struct Coordinate : INullable { private int longitude; private int latitude; private bool isNull; 

The attribute [SqlUserDefinedType] allows setting several properties, which are shown in the following table.

SqlUserDefined- TypeAttribute Property	Description
Format	The property Format defines how the data type is stored within SQL Server. Currently supported formats are Format.Native and Format.UserDefined.
IsByteOrdered	If the property IsByteOrdered is set to true, it is possible to create an index for the data type, and it can be used with GROUP BY and ORDER BY SQL statements. The disk representation will be used for binary comparisons. The default is false.
IsFixedLength	If the disk representation of all instances is of the same size, IsFixedLength can be set to true.
MaxByteSize	The maximum number of bytes needed to store the data is set with MaxByteSize. This property is specified only with a user-defined serialization.
Name	With the Name property, a different name of the type can be set. By default the name of the class is used.
ValidationMethodName	With the ValidationMethodName property a method name can be defined to validate instances when the deserialization takes place.

To represent the direction of the coordinate, the enumeration Orientation is defined:

 public enum Orientation { NorthEast, NorthWest, SouthEast, SouthWest } 

This enumeration can only be used within methods of the struct Coordinate, not as a member field because enumerations are not blittable.

The struct Coordinate specifies some constructors to initialize the longitude, latitude, and isNull variables. The variable isNull is set to true if no values are assigned to longitude and latitude, which is the case in the default constructor. A default constructor is needed with UDTs.

With the worldwide coordination system, longitude and latitude are defined with degrees, minutes, and seconds. Vienna, Austria has the coordinates 48 14' longitude and 16 20' latitude. The symbols , ', and " represent degrees, minutes, and seconds, respectively.

With the variables longitude and latitude, the longitude and latitude values are stored using seconds. The constructor with seven integer parameters converts degrees, minutes, and seconds to seconds, and sets the longitude and latitude to negative values if the coordinate is based in the South or West:

 public Coordinate(int longitude, int latitude) { isNull = false; this.longitude = longitude; this.latitude = latitude; } public Coordinate(int longitudeDegrees, int longitudeMinutes,  int longitudeSeconds, int latitudeDegrees, int latitudeMinutes, int latitudeSeconds, Orientation orientation) { isNull = false; this.longitude = longitudeSeconds + 60 * longitudeMinutes + 3600 *  longitudeDegrees; this.latitude = latitudeSeconds + 60 * latitudeMinutes + 3600 *  latitudeDegrees; switch (orientation) { case Orientation.SouthWest: longitude = -longitude; latitude = -latitude; break; case Orientation.SouthEast: longitude = -longitude; break; case Orientation.NorthWest: latitude = -latitude; break; } } 

The INullable interface defines the property IsNull, which must be implemented to support nullability. The static property Null is used to create an object that represents a null value. In the get accessor a Coordinate object is created, and the isNull field is set to true:

 public bool IsNull { get { return isNull; } } public static Coordinate Null { get { Coordinate c = new Coordinate(); c.isNull = true; return c; } } 

A UDT must be converted from and to a string. For conversion to a string, the ToString() method of the Object class must be overridden. Here the variables longitude and latitude are converted for a string representation to show the degrees, minutes, and seconds notation:

 public override string ToString() { string s; if (isNull) s = null; else { char northSouth; char eastWest; if (longitude > 0) northSouth = 'N'; else northSouth = 'S'; if (latitude > 0) eastWest = 'E'; else eastWest = 'W'; int longitudeDegrees = Math.Abs(longitude) / 3600; int remainingSeconds = Math.Abs(longitude) % 3600; int longitudeMinutes = remainingSeconds / 60; int longitudeSeconds = remainingSeconds % 60; int latitudeDegrees = Math.Abs(latitude) / 3600; remainingSeconds = Math.Abs(latitude) % 3600; int latitudeMinutes = remainingSeconds / 60; int latitudeSeconds = remainingSeconds % 60; s = longitudeDegrees + "" + longitudeMinutes longitudeSeconds + "\"" + northSouth + "," + latitudeDegrees + "" + latitudeMinutes latitudeSeconds + "\"" + eastWest; } return s; } 

The string that is entered from the user is represented in the SqlString parameter of the static method Parse(). First the Parse() method checks if the string represents a null value, in which case the Null property is invoked to return an empty Coordinate object. If the SqlString s does not represent a null value, the text of the string is converted to pass the longitude and latitude values to the Coordinate constructor:

 public static Coordinate Parse(SqlString s) { if (s.IsNull) return Null; try { string[] coordinates = s.Value.Split(','); char[] separators = { '', string[] longitudeVals = coordinates[0].Split(separators); string[] latitudeVals = coordinates[1].Split(separators); Orientation orientation; if (longitudeVals[3] == "N" && latitudeVals[3] == "E") orientation = Orientation.NorthEast; else if (longitudeVals[3] == "S" && latitudeVals[3] == "W") orientation = Orientation.SouthWest; else if (longitudeVals[3] == "S" && latitudeVals[3] == "E") orientation = Orientation.SouthEast; else orientation = Orientation.NorthWest; Coordinate coordinate = new Coordinate( int.Parse(longitudeVals[0]), int.Parse(longitudeVals[1]), int.Parse(longitudeVals[2]), int.Parse(latitudeVals[0]), int.Parse(latitudeVals[1]), int.Parse(latitudeVals[2]), orientation); return coordinate; } catch (Exception ex) { throw new ArgumentException( "Argument has a wrong syntax. " + "This syntax is required: 3747\'0\"N,12226\'0\"W", ex); } } 

Using UDTs

After building the assembly, it can be deployed with SQL Server 2005. Configuration of the UDT in SQL Server 2005 can either be done with Visual Studio 2005 using the Build Deploy Project menu, or using these SQL commands:

 CREATE ASSEMBLY SampleTypes FROM 'c:\ProCSharp\SQL2005\SampleTypes\SampleTypes.dll'  CREATE TYPE Coordinate EXTERNAL NAME SampleTypes.Coordinate 

Now it is possible to create a table called Cities that contains the data type Coordinate as shown in Figure 20-2 and Figure 20-3.

Figure 20-2

Figure 20-3

Using UDTs from Client-Side Code

The assembly of the UDT must be referenced to use the UDT from client-side code. Then it can be used like any other type on the client.

In the sample code, the SELECT statement of the SqlCommand object references the columns of the Cities table that contains the Location column, which is of type Coordinate. Calling the method ToString() invokes the ToString() method of the Coordinate class to display the coordinate value in a string format:

 using System; using System.Data; using System.Data.SqlClient; class Program { static void Main(string[] args) { string dsn = @"server=localhost;database=MyTest;trusted_connection=true"; SqlConnection connection = new SqlConnection(dsn); SqlCommand command = connection.CreateCommand(); command.CommandText = "SELECT Id, Name, Location FROM Cities"; connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { Console.WriteLine("{0}: {1}", reader[1].ToString(), reader[2].ToString()); } reader.Close(); Console.ReadLine(); } } 

Of course, it is also possible to cast the returned object from the SqlDataReader to a Coordinate type for using any other implemented methods of the Coordinate type:

 Coordinate c = (Coordinate)reader[2];