In part I of this series, I showed you how to leverage SQLCLR to parse a comma delimited string of integer values back to as a table of values. There’s really no magic to it: you just need a simple with two classes: one that takes the string, calls the split method on it and returns it as IEnumerable. The second method takes each value from the enumeration, twiddles it into a string value and returns it back to SQL Server to populate values into a row. The whole process boils down to two statements:
return SourceList.Value.Split(',');
which does the splitting and returns an IEnumerable --
and
value = (string)(obj);
This sets the "value" of variable passed by reference into the function to current value from the source string from the split array.
This works great for turning one array into a table, but it doesn’t solve a more complex version of the problem very well. For example, what happens if you have three array of comma delimited values that you want to merge into a single table? The good news is that we can use the same type of solution with the same design for that. But the code is a bit more involved. Let’s take a look at it.
The first thing I’ll create a structure that I’ll use to hold the individual values from the three arrays as a set of values. I’m doing this so we can take advantage of .NET 2.0 generics support. Note that even though I’ve declared this as public, we won’t be leveraging it at the T-SQL level -- we’re just going to use it internally.
public struct IntegerTriplet
{
public int v1;
public int v2;
public int v3;
}
As we did before, we need a method that takes our three arrays and somehow builds those up into something that is IEnumerable. Thanks to generics, that’s actually really easy. We start by creating an instance of the generic List class that typed to our IntegerTriplet structure.
List<IntegerTriplet> list = new List<IntegerTriplet>();
We already know how easily parse our strings, right? Just call the Split method on the string. But note that the Split method returns an array of strings, not a List of IntegerTriplets. So we do need to write a bit of code that pushes values out of the arrays resulting from the Split into the List. That’s not hard either – looks just like this:
string[] list1, list2, list3;
list1 = List1.Value.Split(',');
list2 = List2.Value.Split(',');
list3 = List3.Value.Split(',');
Before we go on, we do need to perform a sanity check. We’ve made an assumption that each of the arrays involved has the same number of values represented. We need test that assumption and if the data doesn’t fit that neat form, we need to stop processing. Again, easy:
if (!(list1.Length == list2.Length) && (list3.Length == list2.Length))
throw new ArgumentException("Must have the same number of elements in each array.");
The actually marshalling of values is pretty straight forward, we start by creating a new instance of the IntegerTriplet structure, then we iterate through the arrays populating each value one at time:
IntegerTriplet t = new IntegerTriplet();
for (int index = 0; index < list1.Length; index++)
{
t.v1 = Convert.ToInt32(list1[index]);
t.v2 = Convert.ToInt32(list2[index]);
t.v3 = Convert.ToInt32(list3[index]);
list.Add(t);
}
So, cool, we’ve got our generic List populated with instances of the IntegerTriplet structure, but how do we return that to SQL Server as IEnumerable. Too easy, just return the instance – that’s part of the beauty of generics after all!
return (list);
Now it would be cool if that’s really all that method needed. I lied, kind of, there’s one other thing we need to pay attention to, but I couldn’t cover it until now and it only applies if you going to use Visual Studio to deploy the function to the server. In order for Visual Studio 2005 know how to generate the CREATE FUNCTION statement for cataloging the function, we some how have to tell it what the table being returned by the function looks like at the T-SQL level. This isn’t hard to do – we’ll just add another facet to the attribute on the method called TableDefinition. The string that follows is the T-SQL required to define the table. It must be in T-SQL Syntax. For our first method, the attribute and signature looks like this:
[SqlFunction(FillRowMethodName = "GetRecord",
TableDefinition = "v1 int,v2 int, v3 int",
Name = "TripletParser")]
public static IEnumerable InitMethod(SqlString List1,
SqlString List2, SqlString List3)
Since our method, eventually, needs to return a table three integer value columns, our TableDefinition can be as shown. Just remember, we’re writing T-SQL here. But why? Ah, great question. When you ask Visual Studio to deploy a table valued function to SQL Server 2005, it actually generates and sends a series of T-SQL statements to the server to that work. Correctly deploying a table value function to SQL Server requires Visual Studio correctly “catalog” a T-SQL User Defined Function (UDF) using a CREATE FUNCTION T-SQL statement. Part of that requires Visual Studio to describe the resulting table in T-SQL terms. So what you put into the TableDefintion facet gets directly emitted into the T-SQL statement Visual Studio generates. If you fire up SQL Servere 2005 profiler and watch the communication between Visual Studio and SQL Server during the deploy of this project, you’ll see this:
CREATE FUNCTION [TripletParser]
(
@List1 nvarchar(4000),
@List2 nvarchar(4000),
@List3 nvarchar(4000)
)
RETURNS TABLE(v1 int,v2 int, v3 int)
AS
EXTERNAL NAME [CommaDelimitedParser].[TripletParser].[InitMethod]
See that our “v1 int...” is part of the RETURNS TABLE part of this.
If you’re not using Visual Studio to deploy the function, you don’t need this facet since you’ll be writing this CREATE FUNCTION statement for yourself.
In total, then, the first method looks like this:
[SqlFunction(FillRowMethodName = "GetRecord",
TableDefinition = "v1 int,v2 int, v3 int",
Name = "TripletParser")]
public static IEnumerable InitMethod(SqlString List1,
SqlString List2, SqlString List3)
{
List<IntegerTriplet> list = new List<IntegerTriplet>();
string[] list1, list2, list3;
list1 = List1.Value.Split(',');
list2 = List2.Value.Split(',');
list3 = List3.Value.Split(',');
if (!(list1.Length == list2.Length)
&& (list3.Length == list2.Length))
throw new ArgumentException(
"Must have the same number of elements in each array.");
IntegerTriplet t = new IntegerTriplet();
for (int index = 0; index < list1.Length; index++)
{
t.v1 = Convert.ToInt32(list1[index]);
t.v2 = Convert.ToInt32(list2[index]);
t.v3 = Convert.ToInt32(list3[index]);
list.Add(t);
}
return (list);
}
That’s a fair amount of work and thankfully, our second method – the one that returns the enumerated values back to SQL Server – isn’t this complex. In fact, it looks like this:
public static void GetRecord(Object obj, out int v1, out int v2, out int v3)
{
IntegerTriplet t = (IntegerTriplet)(obj);
v1 = t.v1;
v2 = t.v2;
v3 = t.v3;
}
The only hard thing here is remember that the enumerated value passed into the function is passed as an object, not as a specifically typed object. Casting it into our IntegerTriplet structure is easy.
So let’s wrap up with a quick review of the key points of writing a more complex SQLCLR User Defined Table Valued Function:
- Any SQLCLR-base User Defined Table Valued Function has two methods: one method that does generates the meaningful work in the function and returns an IEnumerable collection.
- The other required method is a function that takes each of the enumerated values and returns the value or values as out parameter(s).
- The second method takes in an object an has n-many parameters, one for each column values.
- If you’re going to use Visual Studio 2005 to deploy a Table Valued function, you need to add the TableDescription facet to the SqlFunction attribute on the first method. Visual Studio uses this build a query that catalogs the T-SQL callable function.