Managed Code @ SQL Server – Part 2: User-defined Types

In the previous post of this series I covered how to write stored procedures in .NET (“managed stored procedures”) that will execute on an instance of CLR hosted at SQL Server. In this post I’ll cover another feature of using the CLR with SQL Server that you might find more useful: the definition of custom types.

A user-defined type (UDT) can be either a reference type or a value type (class or struct, in C#) and has to be decorated with the SqlUserDefinedType attribute. In addition, a UDT has the following requirements, which are validated when the type is deployed to SQL Server:

• Be serializable, either with native or custom serialization, so that its instances can be stored on disk;
• Implement the INullable interface in order to determinate if an instance represents (or not) the SQL NULL value;
• Have a default constructor (no arguments) to construct instances when reading from disk;
• Have a static property named Null, used to get a “NULL-representative” instance;
• Have a static method named Parse, used to build an instance of the type from a string representation;
• Override the ToString method, accordingly to the Parse method.

Given these requirements, it’s time to define a type. Lets say we want to have rectangles that are represented by strings like “(width, height)”. We define a type named Rectangle, which base structure might be:

[Serializable]
[SqlUserDefinedType(Format.Native, Name = "Rectangle")]
public struct Rectangle : INullable
{
    private const string NULL_STR = "null";

    /**/

    // INullable member
    public bool IsNull { get; private set; }

    static readonly Rectangle nullInstance = new Rectangle() { IsNull = true };
    public static Rectangle Null
    {
        get
        {
            return nullInstance;
        }
    }

    public override string ToString(){…}

    public static Rectangle Parse(SqlString s){…}
}

About now our type doesn’t have much of a rectangle, besides the name. If you think from a usage perspective, you would want to be able to select the dimensions of the rectangles that are stored in some table of your database. So, lets add Width and Height properties. These will actually allow us to do something like select r.Width, r.Height from table! Having the dimensions we can also complete the Parse and ToString methods. (In the Parse method I used a regular expression to match the string representation. To keep it simple the rectangle dimensions are integers).

    private static readonly Regex regExpr = new Regex(@"(\((?<w>[1-9]+)\,(?<h>[1-9]+)\))");

    public int Width { get; set; }
    public int Height { get; set; }

    public override string ToString()
    {
        return this.IsNull ? NULL_STR : String.Format("({0},{1})", Width, Height);
    }

    public static Rectangle Parse(SqlString s)
    {
        if (s.IsNull || s.Value.ToLower().Equals(NULL_STR))
            return Null;

        Match m = regExpr.Match(s.Value, 0);
        if (!m.Success) throw new FormatException("Invalid format on input string");

        int w, h;
        w = int.Parse(m.Groups["w"].Value);
        h = int.Parse(m.Groups["h"].Value);
        return new Rectangle() { Width = w, Height = h };
    }

Now we just need to load the type into SQL Server and use it! Again, to do that you can deploy through Visual Studio or use the appropriate T-SQL statements:

CREATE ASSEMBLY CLRSQLSample FROM '…\CLRSQLSample.dll'
CREATE TYPE Rectangle external name CLRSQLSample.Rectangle

Using the new type couldn’t be simpler:

create table rectanglesTable
(
    r Rectangle
)

insert into rectanglesTable values('(1,2)')
insert into rectanglesTable values('(3,4)')

select r.Width as width, r.Height as heigth
from rectanglesTable

select cast(r as varchar(5))
from rectanglesTable

select r
from rectanglesTable

It’s important to note that, even though we defined Rectangle in an object oriented fashion, we’re not turning SQL Server into an object oriented database. It’s all just bit streams with a user-defined meaning on top. This becomes clear if you execute the last select statement:

Another point to notice is the definition of other methods on the UDT. First of all, we’re defining a data type; we shouldn’t have any logic other than manipulating the data. Nevertheless, it can be useful to define some methods. Suppose that we wanted to grow our Rectangle’s size with some width and height factors. We can define a method, lets say GrowSize, that gets those two factors as input and adjusts the rectangle dimensions. Methods that you want to use to update the type’s internal state must return void and be decorated with the SqlMethod attribute having its IsMutator property set to true. If not, you wont be able to use them. By the way, properties’ setters are always considered mutators.

[SqlMethod(IsMutator = true)]
public void GrowSize(int wFactor, int hFactor)
{
    Width *= wFactor; Height *= hFactor;
}

update rectanglesTable set r.GrowSize(2,2)
select r.Width, r.Height from rectanglesTable

On the other hand, you can also define methods to be used in queries. An example would be a method to calculate the rectangle’s area. Naturally, these methods have to return some value, which makes it impossible to use them as mutators.

And that’s it for user-defined types. As a final note, the usage of UDT is not restricted to tables; you can also use them on stored procedures (managed or unmanaged) and so on. On the next (and last) post of this series I’ll cover another aspect that comes out of UDT. What if you want to do something like select MAX( r ) from rectanglesTable?

Advertisement

Recepção ao Caloiro 2009 @ ISEL

Managed Code @ SQL Server – Part 1: Stored Procedures

One of the facilities of the Common Language Runtime (CLR) is hosting. The CLR is implemented as a COM server (with a known interface) than can be instantiated from within any Windows application. In that case, the application is the CLR host. Examples of possible hosts are Internet Explorer, the ISAPI filter of ASP.NET and SQL Server.

If SQL Server is the host, it will be executing managed code. In this case, we can use any .NET language to define the “programmability objects” that we usually have on databases: stored procedures, functions and triggers. In this post i’ll be giving an example with stored procedures, but the process is quite similar for functions and triggers.

Any static method with a return type of void or int can be a “managed stored procedure”. Naturally, its parameters need to be of T-SQL compatible types. To get going, we need to create a class library that somewhere inside has an appropriate static method, put the right imports in and compile it. As you would expect, there is some Visual Studio goodness. To make the work easy, you can create a new project under Database Projects\Microsoft SQL Server\CLR SQL. Then, if you add a new item you’ll see the "Stored Procedure” option. VS will create a class with a static method that is decorated with the SqlProcedure attribute. In this case, the attribute is used only by VS to identify the “managed stored procedures” when deploying the project but with functions and triggers there are some important options on their attributes (SqlTrigger and SqlFunction).

To access the database and execute SQL statements we use the ADO.NET API as in other data access application. The following example ilustrates this scenario.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SampleSP(int id)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand cmd = new SqlCommand(
                "select description from CLRSQLSample where id = @id", conn);
            cmd.Parameters.AddWithValue("@id", id);
            conn.Open();
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
    }
};

In this example I’ve created a sample stored procedure that has an integer value as parameter (if you need to handle NULL values, use the ADO.NET SQL data types, like SQLInt32 and SQLDecimal). Besides the common ADO.NET code there are two points to notice:

• “context connection=true” – this connection string indicates that we want to connect to “self” from within the managed stored procedure.The context connection lets you execute T-SQL statements in the same context (connection) that your code was invoked in the first place by using an “in-proc” provider.
• SqlContext class – represents an abstraction of the caller’s context. The Pipe property (SqlPipe) gets the pipe object that allows the caller to send result sets, messages, and the results of executing commands back to the client.

Having defined the managed stored procedure, we need to associate it to SQL Server. You can do it easily from VS by selecting the “Deploy” option on the “Build” menu. You can also do it with T-SQL statements.

First we need to configure the server to be “CLR enabled”.

exec sp_configure 'clr enabled', 1
reconfigure

Then, we create the assembly object.

CREATE ASSEMBLY CLRSQLSampleAssembly
FROM 'C:\Users\Luís\Desktop\CLR_SQL\CLR_SQL\bin\Debug\CLRSQLSample.dll'
WITH PERMISSION_SET = SAFE

Even though we’re writing managed code, we can’t use all the functionalities of .NET. The “PERMISSION_SET” option specifies the permission set that we wan’t to grant to the assembly and has impact on the types and members that it can use (more info).

Finally, we create the stored procedure object in SQL Server based on the previously defined static method.

CREATE PROC CLRSQLSampleSP @id int
AS EXTERNAL NAME CLRSQLSampleAssembly.StoredProcedures.SampleSP

If default parameter values  are needed we specify them on the create proc statement and their values will be defined by SQL Server before we get to managed code.

And that’s it! We can execute our managed stored procedure the same way we do with regular ones:

exec CLRSQLSampleSP @id = 1

In the next post I’ll be covering another aspect of using managed code in SQL Server that might be much more useful: user-defined types.