Click here to Skip to main content
Click here to Skip to main content

Creating User-Defined Data Types in SQL Server 2005

, 20 Sep 2006
Rate this:
Please Sign up or sign in to vote.
An article on how to create user defined data types using .NET languages for SQL Server 2005.

Introduction

Microsoft SQL Server 2005 supports Microsoft .NET Framework 2.0 CLR (Common Language Runtime) integration, which means something like coding by .NET (Managed code) within MS SQL Server 2005. You can write stored procedures, functions, data types, and triggers using .NET languages (I will describe a sample with C#). Yeah, you can do this with unmanaged code too, but I'm not going to describe it Smile | :) - I am assuming here that you are familiar with XPs (Extended Stored Procedures). I think both have their advantages and disadvantages. Here, I'm going to describe creating a user defined data type using C#.

Let's do it!

  • Step 1 - Write some managed code (sets of classes including methods, properties, ...) and compile it into an assembly.
  • Step 2 - Load the assembly into a SQL Server database and register the assembly and data type.
  • Step3 - Nothing, just use your code.

Consider that we want to use and store complex numbers in our project, the steps are easy:

When you want a complex number field, just create two columns x and y, and use/store them in x + yi format. But what if you want to use them in a normal way? Just insert x + iy and get x + iy? SQL Server has the solution. First, we are going to use the easy way.

  1. Open Visual Studio, and create a new project. Under C#, choose Database, and then from the available templates (usually, it's the only template that exists there), choose "SQL Server Project", and then enter a name or just accept the default name.
  2. Choose a database or create a new connection, or leave it for later (get the Properties of the project and choose the Database tab).
  3. From the Project menu, choose Add user-defined type (or right click on project in Solution Explorer, and point to Add), and name it "ComplexNumber" or whatever you want Smile | :)
  4. Most of the work is done. The null values, and the ToString() and Parse() methods are created now.

    Let's take a look:

    [Serializable] 
    [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)] 
    public struct ComplexNumber : INullable 
    {
        //....
    }

    It should be a public class or a struct that implements INullable and has the [SqlUserDefinedType] attribute. Null values will defined like this: your type must have a null value, therefore, select a null value according to the data type; for example, a proper null value for a complex number can be (0, 0).

    public bool IsNull 
    { 
        get { return m_Null; } 
    }
    public static ComplexNumber Null 
    { 
        get 
        {
            ComplexNumber h = new ComplexNumber();
            h.m_Null = true; return h;
        }
    }

    The ToString() method will be called when you want to display the field value, and the Parse() method accepts values and makes it ready to use:

    public static ComplexNumber Parse(SqlString s) 
    {
       if (s.IsNull) return Null;
       ComplexNumber u = new ComplexNumber(); 
       // Put your code here
       return u;  
    }
    public override string ToString() 
    {
        // Replace the following code with your code 
        return ""; 
    }
  5. Delete Method1(), Method2(), and var1, and then write the complex number code. We will keep it as simple as possible.

    First, the members:

  6. There is a private bool called m_Null, leave it and type following:
    private double _x; //real part
    private double _y; //imaginary part

    Properties:

    public double RealPart
    {
        get { return _x; }
        set { _x = value; }
    }
        
    public double ImaginaryPart
    {
        get { return _y; }
        set { _y = value; }
    }

    The constructor:

    public ComplexNumber(double x,double y)
    {
       _x = x;
       _y = y;
        m_Null = false;
    }

    The constructor of the structure does not accept empty parameters, therefore I used the following for a null constructor:

    public ComplexNumber(bool nothing)
    {   
        this._x = this._y = 0;
        this.m_Null = true;
    }

    Now, we will do the ToString() method:

    public override string ToString()
    {
        return _x.ToString() + "+" + _y.ToString() + "i";
    }

    Next comes the Parse method. The parsing code is terrible, as it does not handle invalid input and etc. Regex will do great here, but for simplicity, I will not cover that here:

    public static ComplexNumber Parse(SqlString s)
    {
       string value = s.Value;
       if (s.IsNull || value.Trim()=="")
           return Null;
       string xstr = value.Substring(0, value.IndexOf('+'));
       string ystr = value.Substring(value.IndexOf('+') + 1, 
                     value.Length - xstr.Length - 2);
       double xx = double.Parse(xstr);
       double yy = double.Parse(ystr);
       return new ComplexNumber(xx,yy);
    }

    Finally, a method to do something:

    public static ComplexNumber Add(ComplexNumber c1,ComplexNumber c2)
    {
        return new ComplexNumber(c1._x + c2._x, c1._y + c2._y);
    }
  7. And now, you are ready to test your work. Build the project, and then from the Build menu, select Deploy. It's done.

    Note: The CLR integration feature is turned off by default in Microsoft SQL Server, and must be enabled using the following command:

    sp_configure 'clr enabled', 1
    GO
    RECONFIGURE
    GO

    Execute the above code in the query window.

  8. Open "SQL Server Management Studio" and expand your database (I used AdventureWorks), then select Programmability > Types > User-Defined Types. Your data type must be there, if not refresh the list.
  9. Add a new table and call it "test" or whatever, and inside the table, add a column and name it "complexField" (or whatever again).
  10. After saving, right click on the table and select "Open Table" and type a complex number like "253.85+10i". And now, let's do it with a query.

    In "SQL Server Management Studio", open a new query window (CTRL+N) and type the following. Make sure the proper database is selected (your table and field names can differ from mine):

    --insert command & etc
    insert into test (complexField) values('25+52i')
  11. Using the type's method in the query window:
    -- using add method of object
    insert into test values(ComplexNumber::[Add]( 
           convert(complexnumber,'12+25i' ),'25+12i'))

    You have already done it.

I told that it was easy way, didn't I? OK, let's try another way:

  1. The thing we need is a public class or struct that has a [SqlUserDefinedType] attribute and implements INullable. Now, compile it with whatever you want, for example, a csc command.
  2. Now, we must register our DLL (assembly) in SQL Server, like this:
      CREATE ASSEMBLY ComplexNumber
      FROM '\\PersianGulf\Hamed\complexNumber.dll'  --an example path
      WITH PERMISSION_SET = SAFE;

    Oops... I forgot permissions. Permissions are of three types:

    • Safe: compute, access local data
    • External Access: files, registry, network
    • Unsafe: full trust, unmanaged code, verification

    An additional thing is, you can use a byte stream from a file (instead of a path as in the previous code) or a varbinary variable.

  3. The next step is creating the type like this:
    CREATE TYPE dbo.ComplexNumber 
    EXTERNAL NAME ComplexNumber .[ComplexNumber ];

    The EXTERNAL NAME is specified using the two-part naming syntax of AssemblyName.UDTName.

    Note: DROP is Applicable to types and assemblies, and you can ALTER the assembly of your type or change it.

    DROP TYPE dbo.ComplexNumber ;
    DROP ASSEMBLY ComplexNumber ;
    
    ALTER ASSEMBLY ComplexNumber 
    FROM '\\PersianGulf\Hamed\complexNumber.dll'
    
    ALTER ASSEMBLY ComplexNumber 
    ADD FILE FROM '\\PersianGulf\Hamed\complexNumber.cs' AS something;

    The next step is just using it:

    CREATE TABLE test
    complexField ComplexNumber)
    
    /*....and so on...*/

    You will find wonderful code samples in MSDN about this topic, even a sample of a complex number.

History

  • First release - done in rush hour.

References

  • The great and powerful MSDN.
  • A PPT file: SQL Server 2005 what do you need to know? - Randy Holloway - Microsoft Corporation.
  • A PPT file: SQL Server 2005 CLR integration - Matthew Roche

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

subai
Software Developer
Iran (Islamic Republic Of) Iran (Islamic Republic Of)
My name is Hamed Mojarad Satari (actually حامد مجرد ستاری ) I like to learn new things specially computer technologies , play computer games,watching movies , going out and have more friends all over the world Smile | :)

Comments and Discussions

 
GeneralMy vote of 5 PinmvpKanasz Robert25-Sep-12 23:42 
QuestionHow can i pass this userdefined datatype as sqlparameter from frontend Pinmemberganeshsundaram1-Jul-10 19:05 
GeneralError PinmemberMember 44308093-Feb-09 2:44 
GeneralExcellent work Pinmemberi_islamian21-Dec-08 23:19 
QuestionError while deploying the application PinmemberutksThedeveloper16-May-07 2:10 
hi Subai,
In creating user define data types in SQL server 2005 , i have done all the things you have given in the procedure.
I have succeeded in building the application.
But when i tried the deploy option for deploying my application, i faced an error message which i have mentioned on the next line.
 
Error message :-
"Incorrect syntax near 'SqlServerProject1'"
 
"SqlServerProject1" is my project name.
So please help me to solve this problem.
waiting for your reply,

 
Gerard

AnswerRe: Error while deploying the application Pinmembersubai17-May-07 3:44 
GeneralRe: Error while deploying the application PinmemberAsghar Panahy9-Jul-09 0:36 
GeneralRe: Error while deploying the application Pinmemberslice2716-Oct-09 9:52 
GeneralRe: Error while deploying the application Pinmemberslice2716-Oct-09 10:31 
GeneralIncorrect syntax near 'SqlServerProject1'. PinmemberBDisp2-Oct-06 14:25 
GeneralRe: Incorrect syntax near 'SqlServerProject1'. Pinmembersubai2-Oct-06 20:35 
GeneralRe: Incorrect syntax near 'SqlServerProject1'. PinmemberBDisp3-Oct-06 14:03 
GeneralRe: Incorrect syntax near 'SqlServerProject1'. Pinmembersubai4-Oct-06 0:27 
GeneralRe: Incorrect syntax near 'SqlServerProject1'. PinmemberBDisp9-Oct-06 13:17 
GeneralRe: Incorrect syntax near 'SqlServerProject1'. PinmemberBen Saucer24-Mar-08 5:45 
GeneralRe: Incorrect syntax near 'SqlServerProject1'. PinmemberBDisp24-Mar-08 15:01 
GeneralGood Article PinmemberEmre Onyurt25-Sep-06 4:30 
GeneralRe: Good Article Pinmembersubai25-Sep-06 18:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411023.1 | Last Updated 20 Sep 2006
Article Copyright 2006 by subai
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid