he next version of SQL Server (code name Yukon) has extensive support for 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.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. The Serializable attribute 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.
Implement the Object Interface
After decorating the class, you need to implement the .NET Framework INullable Interface. Interfaces are implemented using the Implements statement. The following snippet demonstrates how to implement the INullable interface.
Public ReadOnly Property _ IsNull() As Boolean Implements _ System.Data.SqlTypes_ .INullable.IsNull Get Return Me._IsNull End Get End Property
After implementing the INullable interface, you are required to implement the ToString() and Parse() methods and the Null() property
The ToString method is a common function found on most .NET classes. This function returns a representation of your data type as a string. Remember when you return the value from the ToString function you need to include a representation of NULL values. The following snippet shows the ToString method for this article’s example.
Public Overrides Function_ ToString() As String If Me.IsNull Then Return "NULL" Else Return Me._cInternalValue End If End Function
The other method that needs attention is the Parse() method. This is the method that SQL Server calls whenever someone inserts or updates data defined as your data type. The API for this method is as follows:
Parse(cData as SqlString) _ as
As you can see, this function receives a parameter declared as type SqlString. Upon receiving this code, you perform a number of tasks. The first task is to determine whether or not the data passed in is NULL. If it is, return Nothing from this method.
The second task is to convert the passed-in data to a .NET data type. This is done with the Convert class. After converting the passed-in data to a .NET data type, you can validate the data. If the data passed in does not conform to your rules, you can throw an exception. Figure 1 shows what an error thrown from a custom data type looks like.
Registering the Class After you have created and compiled the class into a DLL, you need to go to SQL Server and perform two tasks.
The following SQL code demonstrates how to perform these tasks:
Using the Class
There are a couple of items to note from the above example. The first item is the use of the Cast() statement. In order to insert data into a custom data type, you need to cast the SQL Server data types into your own data types. The next item to note is the syntax for querying data from a custom data type. The syntax for querying data from a custom data type is As you can see, creating custom data types in SQL Server Yukon is pretty simple stuff. Finally, you can create your own “real” data types with validation rules and all. I hope you can see now how to take advantage of this feature with minimal effort. About Our Editorial ProcessAt DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere. See our full editorial policy. About Our JournalistCharlie Frank Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience. View Author Anticipating Bitcoin’s halving: impacts and strategies Rashan Dixon April 19, 2024 Nothing startup integrates ChatGPT into devices Rashan Dixon April 19, 2024 Google Maps enhances search for EV charging stations Cameron Wiggins April 19, 2024 Upcoming social security payments scheduled by birth dates Rashan Dixon April 19, 2024 Retirees to receive double Social Security payments soon Noah Nguyen April 19, 2024 Proposed Act Aims to Adjust Social Security for Seniors Rashan Dixon April 19, 2024 Anticipating Bitcoin’s halving: impacts and strategies Rashan Dixon April 19, 2024 5:39 PM Nothing startup integrates ChatGPT into devices Rashan Dixon April 19, 2024 5:06 PM Google Maps enhances search for EV charging stations Cameron Wiggins April 19, 2024 1:28 PM Upcoming social security payments scheduled by birth dates Rashan Dixon April 19, 2024 1:07 PM Retirees to receive double Social Security payments soon Noah Nguyen April 19, 2024 11:59 AM Proposed Act Aims to Adjust Social Security for Seniors Rashan Dixon April 19, 2024 8:33 AM USD/JPY stability holds amidst market ambiguities Cameron Wiggins April 19, 2024 8:27 AM Silver prices surge amid increasing demand Rashan Dixon April 19, 2024 7:52 AM Is Online Bookkeeping the Ideal Side Gig for Remote Workers? Kyle Lewis April 18, 2024 2:49 PM Global stock stability amid rising US interest rates Rashan Dixon April 18, 2024 1:44 PM Rivos Inc. raises $250 million pre-launch for AI chips Noah Nguyen April 18, 2024 1:19 PM Goldman Sachs advises caution in predicting Bitcoin prices Cameron Wiggins April 18, 2024 1:14 PM Predicted rise in social security benefits amid inflation Noah Nguyen April 18, 2024 1:09 PM Gold prices dip amid assertive fiscal comments Johannah Lopez April 18, 2024 11:46 AM Vorlon raises $15.7M to strengthen API security Noah Nguyen April 18, 2024 11:28 AM Last April social security disbursement approaching Johannah Lopez April 18, 2024 11:16 AM Rippling to pursue substantial funding round Johannah Lopez April 18, 2024 8:33 AM Simpson’s lawyer battles to protect estate from Goldmans Noah Nguyen April 18, 2024 8:31 AM April’s Social Security retirement payments underway Cameron Wiggins April 17, 2024 5:54 PM Rivian develops software to rank EV chargers Rashan Dixon April 17, 2024 5:50 PM Simpson’s will leaves Goldman family uncompensated April Isaacs April 17, 2024 5:26 PM Framework grapples with software sustainability issues Noah Nguyen April 17, 2024 3:44 PM Surprising economic growth attributed to China’s robust exports and domestic demand Rashan Dixon April 17, 2024 3:04 PM COBOL at 65: still a powerhouse in the tech industry Cameron Wiggins April 17, 2024 1:59 PM California’s minimum wage hike worries restaurateurs Noah Nguyen April 17, 2024 1:05 PM |