Browse DevX
Sign up for e-mail newsletters from DevX


Breaking Type Barriers with UDTs in SQL Server 2005

Using SQL Server 2005 and the .NET 2.0 framework you can go beyond the standard data types recognized by SQL Server and create your own User Defined Types (UDTs) using any .NET Language—and then use them exactly the same way you use the built-in types.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

he integration of the common language runtime (CLR) into Microsoft SQL Server2005 gives developers much more flexibility than ever before. This new feature lets you author stored procedures, triggers, and user defined functions and types using managed languages such as VB.NET or C#. The managed-code approach provides a number of benefits such as increased productivity, significant performance gains, and the ability to leverage the features of .NET Code Access Security to prevent assembles from performing certain operations. This article shows how to use the new CLR integration feature to create user defined types (UDTs) in SQL Server using a managed language and use them from ADO.NET.

UDTs and the .NET CLR
A user-defined type is a custom collection of data values and methods. By default, SQL Server recognizes a large number of individual data types, but it's often useful to group these into a custom type with specific capabilities. Like other type structures in .NET, your SQL Server UDTs can have methods that you can call directly on the database server or on the client. User-defined data types are by far the most complex of all the database object types you can implement with the CLR. UDTs are not simple methods or types that have only predefined methods and attributes, but rather a very flexible way of extending the type system in SQL Server 2005.

In previous versions of SQL Server, database programmers were limited to using Transact-SQL when creating server side objects such as user defined types, stored procedures, triggers, user defined functions and so on. But the integration of SQL Server with .NET CLR opens up a whole new avenue of opportunities. Here are the important characteristics of UDTs. The following list summarizes some of the important characteristics of UDTs.

  • UDTs behave just like any other data type on the server. You can create tables with UDT columns, use UDTs as variables to stored procedures, pass them as parameters, and so on. You can define UDTs using any .NET language.
  • They expose properties and methods that can be invoked from T-SQL on the SQL Server.
  • On the client side, you can use a UDT as you would any other object. After referencing the assembly containing the UDT class, you can use that class from within your data access layer ADO.NET code.
Requirements for Managed-Code UDTs
You implement UDTs in SQL Server 2005 by designing a public class that has several well-known methods, properties, and attributes. The following code shows a skeleton definition of a UDT class with the methods and attributes that you must specify as part of the UDT implementation.

Namespace UDTNamespace <Serializable(), _ SqlUserDefinedType(Format.Native, _ IsByteOrdered := True)> Public class UDTClass Implements INullable Public Overrides Function _ ToString() As String '--Add your code here End Function Public Shared Function Parse _ (ByVal data as SqlString) _ As UDTClass '---Add your code here End Function Public ReadOnly Property IsNull() _ as Boolean _ Implements INullable.IsNull Get '---Add your code here End Get End Property Public Shared ReadOnly Property _ Null as UDTClass Get '--Add your code here End Get End Property End Class End Namespace

The SqlUserDefinedType attribute in the preceding code is a marker showing that the UDTClass implements a UDT. The ToString and Parse methods implement the string representation. Finally, the IsNull and Null properties implement the null representation. A class that implements a UDT must:

  • Be a public class that has its SqlUserDefinedType and Serializable attributes set to appropriate values.
  • Override the ToString method and return a string representation of the UDT.
  • Have a public Parse method that interprets the string representation and returns an instance of the UDT type.
  • Implement the IsNull property of the INullable interface.
  • Implement the public static Null property to produce a null instance.
Here are the steps to create a UDT using a managed language such as C# or VB.NET.

  • Create a .NET class and implement the functionality of the UDT within that class.
  • Compile the class to produce a .NET assembly.
  • Register that assembly in SQL Server using the Create Assembly statement.
  • Create UDT definitions using the Create Type statement. As part of this, you also associate the UDT with the actual class in the assembly.
After you complete these steps, you can use the UDT just like any other pre-defined type in SQL Server.

You must follow the requirements and steps shown above to create a UDT when you're working within SQL Server; however, in Visual Studio 2005, you can automate most of these steps by taking advantage of the seamless integration between Visual Studio 2005 and SQL Server 2005. This article uses Visual Studio 2005 to create the sample UDT.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date