CLR User-Defined Types - SQL Server 2008

Although user-defined types have been available in SQL Server for several years, they were not an especially powerful tool for DBAs and data modelers. T-SQL user-defined types are essentially synonyms for sized type declarations. For instance, you could define a type called ZipCode that maps to a char(5), to be used for representing US five-digit postal codes. Although this can be useful in some cases, it never really caught on as a widely accepted way of defining data. Most DBAs did not bother to use the feature, and since SQL Server 2005 this functionality has been deprecated.

Slated to replace the not-so-powerful T-SQL user-defined types is a new breed of CLR types that can represent virtually any data structure, as described in the sections that follow. These types are not mere wrappers over the intrinsic SQL Server types, as are T-SQL user-defined types. Rather, these types are full-featured class structures, complete with properties and methods. Implementation of these types can and should include such niceties as data verification and string formatting, which were not possible with T-SQL user-defined types.

Applications for User-Defined Types

User-defined types are excellent candidates for representing complex data that SQL Server’s intrinsic types don’t deal with well. For instance, a user might want to create a type to represent postal codes, instead of using one of SQL Server’s character datatypes. A postal code user-defined type could include logic for digesting and normalizing a variety of input string formats for various countries. In the United States, postal codes can be represented using five digits, with the format XXXXX, or nine digits, with the format XXXXX-YYYY. By defining logic within a postal code type to parse and process any type of input postal code format, we create a single library to deal with postal codes and thus eliminate repetition. To reduce repetition further, a series of properties or methods could be defined on the type for retrieving the postal code in various string formats.

Another application of CLR user-defined types is to extend the flexibility afforded by T-SQL from a software development perspective. For instance, many projects might benefit from an array datatype within SQL Server. In some situations it’s required that small, ordered sets of data be passed between stored procedures or user-defined functions. And although developers could use a variety of hacks to achieve functionality similar to arrays in previous versions of SQL Server, starting with SQL Server 2005, developers can leverage the .NET Framework to create an actual array that operates safely and efficiently within SQL Server.

Although the ability to create and use custom datatypes brings great power, developers should avoid the temptation to use SQL Server as a serialized object store. This is conceptually possible—business objects would simply need to be decorated with the correct attributes—but it would drive no real value within the database. Member properties of serialized objects cannot be efficiently queried, so any attempt to use the properties as predicates in queries would destroy server performance. Data represented by user-defined types should be atomic from a query perspective; no individual property of a type should be required in order to filter a row containing an instance of the type.

Adding a User-Defined Type to a SQL Server Project

To start a project for user-defined types in Visual Studio 2008, choose Database, and then select the SQL Server Project template, as shown in Figure. Set the reference to a development database and click OK to launch the project.

Opening a new project in Visual Studio 2008

Opening a new project in Visual Studio 2008

Once the project has been created, right-click the project’s name in Solution Explorer, and then select Add ➤User-Defined Type, as shown in Figure. The first sample type for this chapter will be called PhoneNumber.
Adding a user-defined type to the project

Adding a user-defined type to the project

Parts of a User-Defined Type

Upon adding a type to a project, Visual Studio 2008 will populate a stubbed version of the type with all of the pieces necessary to begin programming. The following code is the stubbed-out version of the PhoneNumber type as generated by Visual Studio 2008:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct PhoneNumber : 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 PhoneNumber Null
{
get
{
PhoneNumber h = new PhoneNumber();
h.m_Null = true;
return h;
}
}
public static PhoneNumber Parse(SqlString s)
{
if (s.IsNull)
return Null;
PhoneNumber u = new PhoneNumber();
// Put your code here
return u;
}
// This is a placeholder method
public string Method1()
{
//Insert method code here
return "Hello";
}
// This is a placeholder static method
public static SqlString Method2()
{
//Insert method code here
return new SqlString("Hello");
}
// This is a placeholder field member
public int var1;
// Private member
private bool m_Null;
}

This code is not as complex as it initially looks, and it can be broken down into a few different sections to make analysis easier.

SqlUserDefinedType Attribute

A class or structure will not be treated as a user-defined type unless it is decorated with the SqlUserDefinedType attribute, as shown in the stubbed code. This attribute has a few parameters that define the serialization behavior of the type:

  • Format: This is the only required parameter, and it determines the method that will be used for serializing the type (rendering the data as binary so that it can be sent across the network or written to disk). The two choices for this parameter are Native and UserDefined. A value of Native indicates that the CLR should automatically handle the serialization, whereas UserDefined indicates that the serialization is programmed by the developer implementing the IBinarySerialize interface. Native serialization will work only if the type is defined as a structure (as opposed to a class) and all members are value types. As such, there are very few nontrivial uses for native serialization of types. The vast majority of cases will be user defined. The complete list of .NET types eligible for native serialization is as follows: bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32,SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney, and SqlBoolean.
  • IsByteOrdered: For a type to be a candidate for indexing or comparison operations (equal to, less than, greater than, and so on), SQL Server must have a way of comparing one instance of the type to another instance of the type. This is implemented using byte ordering. If a type is byte-ordered, SQL Server will assume that comparing the raw bytes of the serialized instance of the type is equivalent to comparing the instances of the type. This is much faster than the alternative, which entails deserializing and using IComparable or a similar mechanism to compare instances of the type. Possible values for this parameter are true and false, and for a type to be considered for indexing or comparison, the value must be true. The default value is false.
  • IsFixedLength: This parameter can be set to true or false. A value of true tells SQL Server that every serialized instance of this type will always be exactly the same size. The default value is false.
  • MaxByteSize: This parameter tells SQL Server the maximum size the type can reach. For a fixed-length type, this parameter should indicate the length to which the type will always serialize. For other types, this size should reflect a realistic estimate on the part of the developer. The value for this parameter can be –1 or any integer between 1 and 8,000. Remember that although a value of 8,000 can work for every non-fixed-length type, this can end up hurting performance. The query optimizer can consider a type’s maximum length when determining query plans. Specifying a value of –1 means unlimited (actually the limit is now 2GB, so it’s practically unlimited) and is new to SQL Server 2008.

Note that the stubbed type is also decorated with the Serializable attribute. This attribute is also necessary; an instance of a type must be serialized any time it is written to disk or sent across the network.

INullable Interface

A user-defined type must implement the INullable interface. This interface defines the Null and IsNull properties.

The IsNull property returns true if the type is null, in the SQL Server sense of the term (as opposed to the .NET sense of the term). null in C# (Nothing in VB .NET) is used to indicate that a reference type does not reference an object. In SQL Server, NULL means something different; it is a token used for unknown data, as well as uninitialized variables. Although the two are similar, it’s important to remember the distinction when working between the two platforms.

The IsNull property is controlled internally in the stubbed type by the value of the m_Null private member, but developers are free to implement this property in any way appropriate to the type being developed.

The Null property returns a freshly instantiated instance of the type. This instance should be initialized such that the IsNull property will return true. The Null property will be used by SQL Server any time a new instance of the type is requested (for example, when a variable is declared of that type). SQL Server will not call new, or an equivalent of new, directly. This means that private member initialization code can be put within the Null property instead of the constructor, if appropriate.

It’s important that user-defined types behave similarly to the intrinsic SQL Server datatypes. Therefore, care should be taken to make sure that the Null and IsNull properties behave correctly. Developers should make sure that these properties do not incorrectly identify null instances as non-null or non-null instances as null—doing so could severely damage the type’s ability to interact properly with the SQL Server engine. This is mainly controlled within the Parse method. A simple way to handle the situation is to always return Null (that is, the Null property of the type) if the string passed in to Parse is NULL (which you can check using the IsNull property of the SqlString type).

ToString Method

Every user-defined type must override the ToString method (which is inherited from the object class by every type defined in .NET). The rationale for this is flexible client interoperability. Although some client libraries may be able to consume a serialized instance of the type, others will only be able to make sense of the type represented as a string.

It is recommended that developers code the ToString method such that the string returned is compatible with the Parse method, described next. If these two methods are compatible, the string generated by the ToString method can be later passed back to SQL Server if necessary, in order to reinstantiate the type.

Parse Method

The Parse method is the exact opposite of the ToString method. Instead of producing a string representation of an instance of the type, this method takes a string as input, returning an instance of the type generated based on the string.

Parse is quite important in the world of user-defined types, as it will be called any time a type’s value is set using the assignment operator (equal sign). Furthermore, public mutators (public members or public settable properties) cannot be set on null instances; any instance that is null must first be instantiated using Parse.

These concepts are best illustrated using a code example. Assume the presence of a user-defined type called PhoneNumber that has a public, settable property called Number. A user might attempt to define an instance of the type and set Number to a value using the following code:

DECLARE @phone PhoneNumber
--Set the number to the Apress business phone line
SET @phone.Number = '5105495930'

This code will fail with the following error message:

Mutator 'Number' on '@phone' cannot be called on a null value

The following code would not result in an error, as it calls Parse internally:

DECLARE @phone PhoneNumber
--Set the number to the Apress business phone line
SET @phone = '5105495930'

Unfortunately, the developer may have actually wanted to set the phone number using the property. That would require first calling Parse with a fake value to initialize the type, and then calling the property directly.

DECLARE @phone PhoneNumber
--Start with a dummy value
SET @phone = '0000000000'
--Set the number to the Apress business phone line
SET @phone.Number = '5105495930'

In most cases, it’s probably a good idea to assign the value only using the assignment operator (and, therefore, Parse), but in some cases it will be necessary to initialize the type to allow for more straightforward SQL coding. The StringArray type shown later in this chapter provides a good example to illustrate that kind of situation.

A Simple Example: The PhoneNumber Type

A common requirement in virtually every business application is storing contact information. This information usually includes, among other things, names, mailing addresses, e-mail addresses, and phone numbers. Unfortunately, problems can sometimes occur due to formatting irregularities. Some people like to write US phone numbers using parentheses and dashes, as in (510) 549-5930. Some prefer to use only dashes, as in 510-549-5930. Others feel that periods look cool and will input the number as 510.549.5930.

It’s not difficult to handle these differences in format, but properly dealing with them requires that every stored procedure in the system validate the input. There should be exactly ten numerals in any valid US phone number. And preferably, those phone numbers should be stored in the database in exactly the same string format, such that they can be indexed and uniformly formatted for output purposes.

Instead of handling this validation and formatting in every stored procedure that deals with external phone number data, a CLR type can be defined. If every stored procedure uses this type, there will be no need for duplicate logic; all formatting will be handled by one central piece of code. Likewise, it will be guaranteed that the data is stored on disk in a uniform format, and output can be coded however necessary to meet business needs.

Modifying the Stubbed Code

Using the stubbed version generated by Visual Studio as a basis for defining the type, there is surprisingly little work required to develop a complete prototype. The first step is to clean up a bit andstub out the correct members for the project at hand. The following code shows the result of initialmodifications:

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined,
IsByteOrdered=true,
IsFixedLength=false,
MaxByteSize=11)]
public struct PhoneNumber : INullable
{
public override string ToString()
{
return this.number;
}
public bool IsNull
{
get
{
if (this.number == "")
return true;
else
return false;
}
}
public static PhoneNumber Null
{
get
{
PhoneNumber h = new PhoneNumber();
h.number = "";
return h;
}
}
public static PhoneNumber Parse(SqlString s)
{
if (s.IsNull)
return Null;
PhoneNumber u = new PhoneNumber();
//Call the Number property for assigning the value
u.Number = s;
return u;
}
// Public mutator for the number
public SqlString Number
{
get
{
return new SqlString(this.number);
}
set
{
this.number = (string)value;
}
}

// The phone number
private string number;
}

The various placeholder members have been replaced with a single private member, number. This variable is a string, and it will hold the validated phone number for a given instance of the type. The public property Number has also been added. This property currently directly sets the private member to the input value; some validation logic will have to be added in order to make it workable. Parse also now internally calls the Number property; that way, any validation logic for setting numbers will need to reside in only one place.

The Null and IsNull properties have also been modified to reflect the removal of the private member m_Null. Since US phone numbers must be exactly ten digits long, the validation logic will ensure that any number persisted within the type consists of ten digits. Any other time, number will be empty, and this will represent a null value.

ToString has been modified to simply return the value of number, the member variable that contains the phone number data. Since the return type is System.String instead of SqlString, this method cannot return a SqlString.Null value if the type is null, which would be preferable to make the type behave more similarly to the intrinsic SQL Server datatypes.

Finally, the properties of the SqlUserDefinedType attribute are changed to reflect the code. The format will be UserDefined, since strings are not value types in .NET. The serialization will be byteordered, allowing indexing and comparison on the type (see the next section on IBinarySerialize).

The type will not be fixed-length, since the empty string (null) case will occupy a single byte in serializedform, whereas properly populated phone numbers will occupy 10 bytes (1 byte per character in the phone number). Since user-defined types occupy 1 byte of overhead, the MaxByteSize parameter is set to 11. Ten bytes are allocated for the member data, and 1 byte is allocated for the type.

IBinarySerialize

If you were to compile the code as listed in Visual Studio, it would compile cleanly. And if you were to manually deploy it (using CREATE ASSEMBLY), the resultant assembly would successfully register with SQL Server. However, CREATE TYPE (and, therefore, the Visual Studio deployment task) would fail with the following error:

Type "UserDefinedTypes.PhoneNumber" is marked for user-defined serialization, but does not implement the "System.Data.Microsoft.SqlServer.Server.IBinarySerialize" interface.

To implement the IBinarySerialize interface, add the name of the interface to the inheritance list in the class or structure declaration.

public struct PhoneNumber : INullable, IBinarySerialize

Visual Studio 2008 has a convenient feature to assist with implementation of interfaces. Rightclick the name of the interface after adding it, and a context menu will appear with an Implement Interface option. Click the suboption of the same name, as shown in Figure, to populate the type with the stubbed methods to implement the routine. Note that either Implement Interface or Implement Interface Explicitly will work. The latter explicitly prefixes methods and properties with the name of the interface to assist with multiple interface situations; however, this is not an issue with the PhoneNumber type.

Implementing an interface in the project

Implementing an interface in the project

After the interface is implemented, the code for the type will contain a new region similar to the following:

#region IBinarySerialize Members public void Read(System.IO.BinaryReader r)
{
throw new Exception("The method or operation is not implemented.");
}
public void Write(System.IO.BinaryWriter w)
{
throw new Exception("The method or operation is not implemented.");
}
#endregion

The Read method is responsible for reconstituting an instance of the type from its binary serialized state; the Write method handles the serialization. Although this sounds somewhat complex, the methods of the BinaryReader and BinaryWriter classes are very simple to work with.

The BinaryReader class contains methods that can automatically handle many of the .NET types. These include ReadString, ReadInt16, ReadInt32, and others. Since the PhoneNumber type only deals with a single string (the private member number), the ReadString method alone is sufficient to rebuild an instance of the type from serialized form. The following code is the full representation of the Read method for the type:

public void Read(System.IO.BinaryReader r)
{
this.number = r.ReadString();
}

The BinaryWriter class is even simpler than the BinaryReader class, with only a single method that developers will have to concern themselves with in most cases: Write. Several overloads are exposed for this method, such that what it offers is symmetrical to what is offered by the various Read methods of the BinaryReader. In the case of the PhoneNumber type, the overload that takes a string can be used.

public void Write(System.IO.BinaryWriter w)
{
w.Write(number);
}

Again, this is all that’s necessary for implementing the method. And since the string will be serialized as a simple binary stream, this implementation also produces the byte ordering necessary for indexing and comparison.

Although many types will end up with more complex implementations than these, the basic pattern to keep in mind is that each call to one of the BinaryReader methods should have a corresponding call to Write, and vice versa. If you keep this rule in mind when working with the IBinarySerialize interface, development can be simple and efficient.

Implementing the Validation Logic

The final step in defining the PhoneNumber type is to implement the logic to validate the input. For thesake of this exercise, the logic can be quite simplistic: strip out all nonnumeric characters from the input string. If the resultant string of numerals is exactly ten characters long, it will be considered valid. Otherwise, it will be rejected with an error.

The following code is the completed Number property for the type:

// Public mutator for the number
public SqlString Number
{
get
{
return new SqlString(this.number);
}
set
{
//If null, don't process any further
if (value == "")
{
this.number = "";
return;
}
//Match groups of 1 or more digits
Regex regex = new Regex("[0-9]*");
MatchCollection matches = regex.Matches((string)value);
StringBuilder result = new StringBuilder();
foreach (Match match in matches)
{
result.Append(match.Value);
}
if (result.Length == 10)
this.number = result.ToString();
else
throw new ArgumentException("Phone numbers must be 10 digits.");
}
}

Note that the Regex, Match, and Matches classes are in the System.Text.RegularExpressions namespace, and the StringBuilder class is in the System.Text namespace. Appropriate using declarations need to be added before the classes to facilitate their use.

The complete code for the PhoneNumber type is as follows:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Text.RegularExpressions;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType (Format.UserDefined,
IsByteOrdered = true,
IsFixedLength = false,
MaxByteSize = 11)]
public struct PhoneNumber : INullable, IBinarySerialize
{
public override string ToString()
{
return this.number;
}
public bool IsNull
{
get
{
if (this.number == "")
return true;
else
return false;
}
}
public static PhoneNumber Null
{
get
{
PhoneNumber h = new PhoneNumber();
h.number = "";
return h;
}
}
public static PhoneNumber Parse(SqlString s)
{
if (s.IsNull)
return Null;
PhoneNumber u = new PhoneNumber();
//Call the Number property for assigning the value
u.Number = s;
return u;
}
// Public mutator for the number
public SqlString Number
{
get
{
return new SqlString(this.number);
}
set
{
//If null, don't process any further
if (value == "")
{
this.number = "";
return;
}
//Match groups of 1 or more digits
Regex regex = new Regex("[0-9]*");
MatchCollection matches = regex.Matches((string)value);
StringBuilder result = new StringBuilder();
foreach (Match match in matches)
{
result.Append(match.Value);
}
if (result.Length == 10)
this.number = result.ToString();
else
throw new ArgumentException("Phone numbers must be 10 digits.");
}
}
// The phone number
private string number;
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
this.number = r.ReadString();
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(number);
}
#endregion
}

Deploying and Testing the Type

Once the type is written, it is ready to deploy and test. The type can be deployed for debugging purposesdirectly from Visual Studio 2008. Right-click the project name in Solution Explorer and click Deploy, as shown in Figure.

Deploying the user-defined type

Deploying the user-defined type

To try out the type, open SQL Server Management Studio and connect to the database that was specified when the project was created. User-defined types, once created, are instantiated using the DECLARE keyword, just like the built-in SQL Server datatypes. Recall the example from earlier in the chapter when we discussed the Parse method.

DECLARE @phone PhoneNumber
--Set the number to the Apress business phone line
SET @phone = '510-549-5930'

This code creates a variable called @phone of type PhoneNumber. It then sets the value of the variable to the number for the Apress business phone line. Remember that this code is actually calling Parse on a null instance of the type.

To return the string representation of the type (the ten-digit phone number), the ToString method must be called, as in the following code:

PRINT @phone.ToString()

Another important thing to remember is that methods and properties on user-defined types are case sensitive, even if the server or database isn’t. Note that the capitalization of ToString in the example is the same as the capitalization in the type’s definition.

Selecting the type without using ToString will return the type in binary serialized form. This form may be usable from a .NET application that has a reference to the assembly in which the type is compiled, but generally speaking, ToString will be a more commonly used way of getting a type’s data. Printing the type using the T-SQL PRINT statement is also possible and requires using either ToString or the CONVERT function, to convert the type into NVARCHAR.

Another Example: The StringArray Type

While the PhoneNumber type adequately illustrates the various programming nuances of working with user-defined types, it does not show off much of the power that can be gained from their use.

We’ll present here a more interesting example to satisfy a common requirement in SQL Server programming projects: representing data in an array format. This usually falls into the category of a developer needing to pass more than one value into a stored procedure, but arrays can also be used to make the T-SQL language more powerful from a programmatic standpoint. CLR user-defined types dramatically change the landscape such that these hacks are no longer necessary. In this example, the power of a .NET collection (the List class) will be exposed via a CLR user-defined type, resulting in a fully functional array that can be invoked directly from T-SQL.

Wrapping the Functionality of a Generic List

Starting with .NET 2.0, the framework includes support for containers called generics, which are strongly typed versions of the object-specific containers available in previous versions of the .NET Framework. Using the List<T> type (which is a generic version of the ArrayList) as a basis, a CLR user-defined type can be built to deal with collections of strings.

Generics allow developers to easily implement type-safe classes, such as collections. Most of the collection types in .NET 1.x were collections of objects. Since every type in .NET is derived from the object type, this means that every type can be cast as an object; therefore, every type could benefit from the collections. However, this also meant that a collection could not enforce what kinds of objects it stored. A collection might be incorrectly populated with both integers and strings, for instance. This could lead to exceptions when code meant to deal with integers suddenly encountered strings.

Generics solve this problem by allowing developers to specify a type to be used by a class (or collection) at object-creation time. This allows the CLR to enforce type-safety, allowing the object to use only the specified class. The syntax for this feature is a pair of angle brackets after the type name. For example, the following code creates an instance of List that can only use integers:

List<int> myList = new List<int>;

Note that when implementing generic classes, a using directive for the System.Collections.

Generic namespace should be included at the top of the source file for the class. The actual string data will be held in a collection of type List<string>. This container will be strongly typed such that it can hold only strings. The following code defines the member:

// The actual array
private List<string> arr;

Next, the important features that make an array usable should be exposed by properties or methods such that they are accessible from T-SQL. These features include getting a count of strings in the array, adding strings to the array, removing strings from the array, and getting a string at a specific index of the array. The following block of code defines each of those features:

public SqlInt32 Count
{
get
{
if (this.IsNull)
return SqlInt32.Null;
else
return (SqlInt32)(this.arr.Count);
}
}
public SqlString GetAt(int Index)
{
return (SqlString)(string)(this.arr[Index]);
}
public StringArray AddString(SqlString str)
{
if (this.IsNull)
this.arr = new List<string>(1);
this.arr.Add((string)str);
return (this);
}
public StringArray RemoveAt(int Index)
{
this.arr.RemoveAt(Index);
return this;
}

By simply wrapping the List<T>’s methods and properties, they are now accessible from T-SQL.

Implementing Parse and ToString

To instantiate an array, a developer will pass in a comma-delimited list of elements. The Parse method will handle the input, splitting up the list in order to populate the array. The ToString method will do the opposite, to return the contents of the array in a comma-delimited format.

The Parse method for the StringArray type uses the Split method of System.String. This method outputs an array of strings by splitting a delimited list. Once the array is produced, the method trims each element of preceding and trailing whitespace and puts any nonempty strings into the arr member variable. The following code implements the Parse method:

public static StringArray Parse(SqlString s)
{
if (s.IsNull)
return Null;
StringArray u = new StringArray();

string[] strings = ((string)s).Split(',');

for(int i = 0; i < strings.Length; i++)
{
strings[i] = strings[i].Trim();
}
u.arr = new List<string>(strings.Length);
foreach (string str in strings)
{
if (str != "")
u.arr.Add(str);
}
return u;
}

The ToString method does the reverse of Parse, using Join, which has the opposite behavior of Split. An array of strings is input, and a comma-delimited list is output.

public override string ToString()
{
// Replace the following code with your code
if (this.IsNull)
return "";
else
return String.Join(",", (string[])this.arr.ToArray());
}

Defining the SqlUserDefinedType Attribute

Because the private member data for this type will reside in a reference type (List<T>), the format must be user defined.

It doesn’t make a lot of sense to compare two arrays for the purpose of sorting. There is no clear way to define how two arrays should sort. Should they sort based on number of elements? Based on the elements themselves? As it is nearly impossible to define how arrays would be sorted—and probably not useful for many development challenges—it also does not make sense to index a column of an array type. Indexes are generally helpful for seeking ordered data, but it is unlikely that a developer would want to perform a seek using an array as a key. For these reasons, there is no need to worry about byte ordering, so IsByteOrdered should be set to false.

And since arrays can contain any number of elements, the type is clearly not of a fixed length, nor does it have a maximum byte size (SQL Server 2008 has a byte limit of 2GB).

The fully populated SqlUserDefinedType attribute for this type is as follows:

[Microsoft.SqlServer.Server.SqlUserDefinedType(
Format.UserDefined,
IsByteOrdered = false,
IsFixedLength = false,
MaxByteSize = 8000)]

Implementing IBinarySerialize

Determining how to serialize the data for this type will not be quite as simple as doing so for the PhoneNumber type. Instead of serializing a single piece of data, serialization for this type must deal with an array containing a variable number of elements.

A simple way of dealing with this situation is to first serialize a count of elements in the array, and then loop over and serialize each array element one by one. The only open issue with such a scheme is serialization of null-valued types. This can be easily taken care of using the following code, which serializes –1 as the count if the type is null:

if (this.IsNull)
{
w.Write(-1);
}

Non-null types, on the other hand, can be written using the following code, which first serializes the count of items and then each element in the array:

w.Write(this.arr.Count);
foreach (string str in this.arr)
{
w.Write(str);
}

Reading back the serialized data involves doing the exact opposite. First, the serialized count of items is read back. If it is –1, there is nothing else to do; the type will already be null. If the count is greater than –1, a loop will run to read each element from the serialized binary. Remember that 0 is also a valid count. Empty arrays are not the same as null arrays.

The entire code for implementing IBinarySerialize for the StringArray type is as follows:

#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
int count = r.ReadInt32();
if (count > -1)
{
this.arr = new List<string>(count);
for (int i = 0; i < count; i++)
{
this.arr.Add(r.ReadString());
}
}
}
public void Write(System.IO.BinaryWriter w)
{
if (this.IsNull)
{
w.Write(-1);
}
else
{
w.Write(this.arr.Count);
foreach (string str in this.arr)
{
w.Write(str);
}
}
}
#endregion

Defining the IsNull and Null Properties

Implementing the INullable interface for the StringArray type is necessary in order to complete development of the type. In keeping with the theme of thinly wrapping the functionality of the .NET List<T> type, the IsNull method can be coded to determine whether the type is NULL based on whether the private member array is null—that is, whether it has been instantiated yet. Due to the fact that the array is not instantiated until data is passed into the Parse method, the Null method can simply call the default constructor and return the instance of the type. The following code implements both of these properties:

public bool IsNull
{
get
{
return (this.arr == null);
}
}
public static StringArray Null
{
get
{
StringArray h = new StringArray();
return h;
}
}

Complete StringArray Class Sample

The complete code for the StringArray user-defined type is as follows:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(
Format.UserDefined,
IsByteOrdered = false,
IsFixedLength = false,
MaxByteSize = 8000)]
public struct StringArray : INullable, IBinarySerialize
{
public override string ToString()
{
// Replace the following code with your code
if (this.IsNull)
return "";
else
return String.Join(",", (string[])this.arr.ToArray());
}
public bool IsNull
{
get
{
return (this.arr == null);
}
}
public static StringArray Null
{
get
{
StringArray h = new StringArray();
return h;
}
}
public static StringArray Parse(SqlString s)
{
if (s.IsNull)
return Null;
StringArray u = new StringArray();
string[] strings = ((string)s).Split(',');
for(int i = 0; i < strings.Length; i++)
{
strings[i] = strings[i].Trim();
}
u.arr = new List<string>(strings.Length);
foreach (string str in strings)
{
if (str != "")
u.arr.Add(str);
}
return u;
}
public SqlInt32 Count
{
get
{
if (this.IsNull)
return SqlInt32.Null;
else
return (SqlInt32)(this.arr.Count);
}
}
public SqlString GetAt(int Index)
{
return (SqlString)(string)(this.arr[Index]);
}
public StringArray AddString(SqlString str)
{
if (this.IsNull)
this.arr = new List<string>(1);
this.arr.Add((string)str);
return (this);
}
public StringArray RemoveAt(int Index)
{
this.arr.RemoveAt(Index);
return this;
}
// The actual array
private List<string> arr;
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
int count = r.ReadInt32();
if (count > -1)
{
this.arr = new List<string>(count);
for (int i = 0; i < count; i++)
{
this.arr.Add(r.ReadString());
}
}
}
public void Write(System.IO.BinaryWriter w)
{
if (this.IsNull)
{
w.Write(-1);
}
else
{
w.Write(this.arr.Count);
foreach (string str in this.arr)
{
w.Write(str);
}
}
}
#endregion
}

Using the StringArray

The StringArray type can be used to solve development problems that might require the full power afforded by a CLR function or stored procedure, but are easier to handle using data structures that are not built into SQL Server. An instance of the StringArray can be initially populated from a comma-delimited list, as in the following code:

DECLARE @array StringArray
SET @array = 'a,b,c'

As a result of this code, the @array variable contains three elements, which can be retrieved or deleted using the GetAt or RemoveAt methods. An extension to the type might be to add a SetAt method to replace existing values in the array, but we’ll leave that as an exercise for interested readers.

Interestingly, the SQL CLR engine only blocks modification of null types via public mutators—public methods that happen to perform modification are allowed. So using the AddString method is an option at any time, whether or not the type is null. The following code will have the same end result as the previous code.

DECLARE @array StringArray
SET @array = @array.AddString('a')
SET @array = @array.AddString('b')
SET @array = @array.AddString('c')

Managing User-Defined Types

If an assembly has been loaded into the database using CREATE ASSEMBLY, types can be created or dropped without using the Visual Studio deployment task, as assumed in most examples in this chapter.

To manually create a type that is exposed in an assembly, without using the deployment task, use the T-SQL CREATE TYPE statement and specify the name of the assembly and name of the structure or class that defines the type. The following code creates the StringArray type from an assembly called StringArray:

CREATE TYPE StringArray
EXTERNAL NAME StringArray.StringArray

To drop a type, use DROP TYPE. A type cannot be dropped if it is referenced by a table or function. The following code drops the StringArray type:

DROP TYPE StringArray

The sys.types catalog view exposes information about both system and user-defined types. To enumerate the data for CLR user-defined types in the database, use the is_assembly_type column:

SELECT *
FROM sys.types
WHERE is_assembly_type = 1

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics