Creating User-Defined Data Types in 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 - 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
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