Creating a C# User-Defined Type
Before we get into the code involved in creating your own user-defined type in SQL Server 2005, it should be pointed out that CLR UDTs are extremely different from any type of custom data structure you may have created for previous versions of SQL Server. A CLR UDT not only has fields for storing data, but it also has behaviors that are defined by C# methods on the UDT class. The larger and more complex a UDT, the more overhead it consumes. As a result, the most efficient use for UDTs is storing simple data over which you want tight control. For example, you could create a UDT in C# that provides a highly customized type of date/time storage, or you could even create one that stores encrypted data. You can provide behaviors (methods) on that type to gain access to the decrypted values.
The SQL Books Online reference that you can install with SQL Server 2005 illustrates the use of CLR UDTs with a type called Point. What we want to do is to create a table in SQL Server that is storing the positions of all the objects in a fictitious strategy game. This fictitious game uses three-dimensional space, so the UDT that we will create will support three-dimensional points, and will contain a method for determining the distance between two 3D points.
To get started, add a user-defined type called Point3D to your project using the same method used to create a user-defined procedure and a user-defined function.
Next, change the default class definition so that it looks like the one shown in Listing 21.2.
Listing 21.2. Point3D, a User-Defined Type
It looks pretty much like any other C# struct, with a few minor differences. The SqlMethod attribute controls the behavior of the method when invoked within a SQL statement.
To use this new data type, first run your project to complete the deployment and registration of your new type. Then, you can use SQL Server 2005's management console or Visual Studio 2005's server browser for creating a new table. Figure 21.2 shows the table editor from within Visual Studio 2005. Note that the Point3D data type appears just like any other type in SQL.
Figure 21.2. Creating a table based on the Point3D data type.
Two of the key methods that make this type work are the Parse method and the ToString method. The Parse method is what allows you to enter textual data in a grid or send textual data in a SQL statement and have SQL properly create an instance of the Point3D type. By default, SQL Server 2005 doesn't invoke the ToString method on your type to display it; it displays the raw serialization data.
Create a new table, GameObjects, with a column for the object's ID, the object name, and the object's location. Then, add in some rows of data, making sure to enter the location points in the format x,y,z.
With the data in place, you can start issuing SQL statements to examine the contents of the data, as shown in the following code:
DECLARE @refPoint Point3D SELECT @refPoint = Location FROM GameObjects WHERE ObjectId=2 select ObjectID, Name, Location.ToString() as CurrentLocation, Location.DistanceFromPoint(@refPoint) FROM GameObjects
This SQL statement will give you a list of all the objects in the game, as well as each object's distance from the object with ID 2. The following are the results from the author's sample data:
1 Battleship Intrepid 0,0,0 21.6101827849743 2 Starship SAMS 1,5,21 0 3 Battlecruiser DotNettica 9,7,3 19.7989898732233
As expected, the distance from object 2 to object 2 is 0. The other objects are displaying the results of the DistanceFromPoint method on the Point3D struct itself.
With this kind of power in hand, you can do a lot of really interesting things. For example, let's say that the Starship SAMS has a radar radius of 20 units. We can easily issue a query that will return all game objects within radar range, as shown in the following lines:
select ObjectID, Name, Location.ToString() as CurrentLocation FROM GameObjects WHERE Location.DistanceFromPoint(@refPoint) < 20 AND ObjectID != 2
You can also access the user-defined type from within your regular .NET code using ADO.NET. All you need to do is add a reference in your client code to the assembly that contains the UDT. Create a console application and then add a reference to SqlProjectDemo.dll by browsing for it. Then you can write the code shown in Listing 21.3.
Listing 21.3. ADO.NET Client Consuming a SQL Server 2005 UDT
When you run this application, you receive the following output:
1 (Battleship Intrepid) is at 0,0,0. Distance from Origin 0 2 (Starship SAMS) is at 1,5,21. Distance from Origin 21.6101827849743 3 (Battlecruiser DotNettica) is at 9,7,3. Distance from Origin 11.7898261225516
Remember that CLR UDTs are designed specifically for small, simple types that might expose a few behaviors. If you attempt to store complex or hierarchical data in a UDT, you might actually be slowing your application down.