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)] publicstruct 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).

   publicbool IsNull { get { return m_Null; } } publicstatic 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:

   publicstatic ComplexNumber Parse(SqlString s) { if (s.IsNull) return Null; ComplexNumber u = new ComplexNumber(); // Put your code herereturn u; } publicoverridestring 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 privatebool called m_Null, leave it and type following:

   privatedouble _x; //real partprivatedouble _y; //imaginary part

   Properties:

   publicdouble RealPart { get { return _x; } set { _x = value; } } publicdouble 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:

   publicoverridestring 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:

   publicstatic 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); returnnew ComplexNumber(xx,yy); }

   Finally, a method to do something:

   publicstatic ComplexNumber Add(ComplexNumber c1,ComplexNumber c2) { returnnew 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', 1GO 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 & etcinsertinto test (complexField) values('25+52i')

11. Using the type's method in the query window:

    -- using add method of objectinsertinto 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 pathWITH 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:

   CREATETABLE 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