Share via

Whidbey ADO.NET 2.0 User Defined Types (UDTs) with Sql Server 2005

  • Article

You can now create your own objects in managed code and install them into Sql Server 2005 as data types!

I am going to spend the next week blogging about the new Sql Server 2005 User Defined Types (UDTs). This is a very powerful feature that allows you to define new data types for Sql Server in managed code, these data types can have methods that you can call directly on the database and you can use these objects on the client. The only problem I have with this feature is that I am not sure of how it is going to be used, please leave me feedback if you are currently working on integrating UDTs into a project.

So what are User Defined Types for Sql Server 2005?

  • User Defined Types behave just like any other data type on the server. You can create tables with UDT columns, use them as variables to stored procedures, pass them as parameters etc.

  • They are defined using any of the languages supported by .NET.

  • They expose properties and methods that can be invoked using TSQL on the server.

  • On the client you can use a UDT as you would any other object.

UDT Requirements: Unfortunately you can’t convert just any object you create into a UDT, there is actually a long list of requirements, I will go over these requirements with a magnifying glass over the next few blogs. The basic requirements are that you specify a SqlUserDefinedTypeAttribute (this will handle most of the magic), implement INullable and expose Parse and ToString methods.

UDT Restrictions: Again, a long list of restrictions to blog about. The biggest ones are that your UDT can’t go over 8k total of serialized data and that we don’t support inheritance.

UDT Serialization Formats: There are two serialization formats, Native and User Defined. Native is where we do the serialization for you, this requires that your user defined type only contains fields that are value types (this means that you can’t use fields of type string for example). User Defined gives you full control over the serialization of the object to you (this is just a fancy way of saying that you need to implement IBinarySerialize and write the serialization code for your object yourself).

Rambling out,

Standard Disclaimer: The information posted here is provided “AS IS” and confers no rights.

Comments

  • Anonymous
    July 20, 2004
    Check out my article for CoDe Magazine on this subject.

    http://www.code-magazine.com/Article.aspx?quickid=0303052
  • Anonymous
    July 21, 2004
    Is this a departure from a relational model and/or SQL? For instance, if you had an object of type Contact with properties FirstName, LastName and PhoneNum, how would you get to those properties using SQL?
  • Anonymous
    July 21, 2004
    Rod,
    That is a very good article, do you mind if I point people to it?

    Lerch,
    UDTs are integrated in the SQL model, here would be an example of using a UDT of type Contact with a Parse method that takes FirstName,LastName,PhoneNum.

    CREATE TABLE mycontacts (id int primary key, mycontact Contact)
    Insert into mycontacts values (1, CONVERT(Contact, 'Angel,Saenz-Badillos,425222222')
    //the convert function will call Contact.Parse with the string provided, it will be up to this method to sort the information into the appropriate fields in the UDT.

    //now you can use TSQL to get/set properties directly:
    SELECT * from mycontacts where mycontact.FirstName='Angel'

    //if your Contact udt had a method you can invoke it the same way.

    Hope this helps,
  • Anonymous
    July 22, 2004
    Angel,
    Thanks for the compliment!

    Go ahead and point people at that article.