Creating User-Defined Data Types in Yukon The next version of SQL Server (code name Yukon) has extensive support of the Common Language Runtime (CLR).Previous versions of SQL Server (2000 and earlier) had a mechanism for creating custom data types. These data types were nothing more than aliases to system data types. In Yukon, you can create your own fully functional custom data types. One key feature of CLR support is the ability to create User Defined Data Types (UDT). These UDTs are implemented using Visual Studio .NET and the .NET Framework. This article demonstrates how to create your own UDT using Yukon and the Whidbey-release of Visual Studio .NET (still in Alpha at this writing). Getting Started The process of creating a UDT begins with creating a .NET class that supports the proper API. Creating and using a UDT comes in two phases: creating the library and registering the UDT with SQL Server. The first step is to create your new data type in Visual Studio .NET. The listing for this article shows how to create a Social Security Number data type. When you create your UDT you will do the following: - Create a new class library
- Import assembles
- Add two attributes to your class (Serializable() and SqlUserDefinedDataType)
- Implement the INullable interface
- Add required methods and properties to your class
- Compile your class
- Register your class library with SQL Server
Creating A Class Library The initial phase of this process is to create a new class library. You create a new class by selecting New and then Project from the Visual Studio .NET menu. Select Class Library from either the C# or Visual Basic.NET (VB.NET) Projects list (this article demonstrates using VB.NET). After creating your class library, you need to import the following assemblies - System
- System.Data.SQL
- System.Data.SQLTypes
Adding Attributes After adding the appropriate references, you need to "decorate" the class with two attributes. These attributes are Serializable() and SqlUserDefinedType. Serializable gives the CLR the ability to take the class and serialize it or to turn it into XML. SQL Server uses this capability to store and retrieve the class from its data store. The SqlUserDefineType attribute is used by SQL Server to determine how it should manage your class. This attribute has a number of properties. The two most important are Format and MaxByteSize. The Format property instructs SQL Server how to store the object. The MaxByteSize property determines how many bytes your data type can consume. | & | | 
By: Rod Paddock Rod Paddock is the editor of CoDe Magazine. Rod has been a software developer for more than 10 years and has worked with tools like Visual Studio .NET SQL Server, Visual Basic, Visual FoxPro, Delphi and numerous others.
Rod is president of Dash Point Software, Inc. Dash Point is an award winning software development firm that specializes in developing applications for small to large businesses. Dash Point has delivered applications for numerous corporations like: Six Flags, First Premier Bank, Intel, Microsoft and the US Coast Guard.
Rod is also VP of Development for SQL Server tools maker, Red Matrix Technologies. (www.redmatrix.com).
|