Overview

In this post I would like to demonstrate how to create a simple yet non-trivial CLR table-valued function (TVF). There are several examples of CLR TVFs on the net but the majority of them show only a trivial example. It is very difficult, in some cases impossible, to apply the principles and code in those examples to your own CLR TVF. The TVF outlined in this post is used to return the IP address for a given host name. At my shop we needed a way to log the IP a user connects from when they perform certain actions in the database. .NET can readily perform this work so why not create a TVF to return the IP addresses for a user? Sometimes this function will get called on a machine with two or more IPs (wireless and physical LAN) so a scalar function will not work here. What makes this TVF non-trivial in my opinion is that it does not just output an already formatted table; you have to build the output.

Without further ado, let’s dive in. The following code is full C# source code for the function. I will first divulge the entire code and then explain the pieces.

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;                       // Bring in classes necessary for working with IPs
using System.Collections;               // Bring in the IEnumberable defintion
using System.Collections.Generic;       // Bring in the List<T> class

public class Class_mfn_dnslookup
{
    [SqlFunction(Name = "mfn_dnslookup", FillRowMethodName = "FillRow", TableDefinition = "IP NVARCHAR(20)")]
    public static IEnumerable mfn_dnslookup(SqlString hostname)
    {
        string c_hostname = hostname.ToString();
        IPHostEntry host;
        host = Dns.GetHostEntry(c_hostname);

        List<object[]> resultItems = new List<object[]>();

        foreach (IPAddress ip in host.AddressList)
        {
            if (!IPAddress.IsLoopback(ip) && ip.ToString().IndexOf(".") != -1)
            {
                object[] item = new object[1];
                item.SetValue(ip.ToString(), 0);

                resultItems.Add(item);
            }
        }
        return resultItems;
    }

    public static void FillRow(Object obj, out SqlString IP)
    {
        object[] item = (object[])obj;
        IP = (SqlString)item[0].ToString();
    }
};

Code Guts

In line 10 we define the FillRowMethodName which will dictate how the return table is built. We also define the TableDefinition for the output table. Note that Line 11 uses the output type IEnumerable. This type interacts closely with the FillRow method specified earlier. This also allows for the table to be returned as rows are filled, instead of waiting for all rows to be populated and then returned. This is particularly handy when returning large data sets that you want to begin viewing right away. To build a result set, I assign the IP address to an index within an object array of static length (lines 23 and 24). This array only has the one slot, which is fine since each adapter has only one IP address. I then add this array to the resultItems list (line 26). Line 29 returns the resultItems list. But to where? Or what? This is where the IEnumerable class comes back into play. For CLR TVFs, the data being returned (resultItems list in this case) is operated on by the FillRow method, row by row.

So, the resultItems list, when returned, is passed through the FillRow method. Lines 34 and 35 show how values are set for one output row. The IP output in these lines corresponds to the [IP] column in the table definition. This happens for each output row in the resultItems list that is passed into the FillRow method. The results are sent back to the calling client as they are returned.

Conclusion

While there are several postings about CLR TVFs, I felt none of them explained their code well enough, namely the FillRow method. Jonathan Kehayias has a decent post at SQLCLR.net. I took the concepts from his post and applied them to my situation. Hopefully my explanation will help you better understand how to create a meaningful CLR TVF.