Post date: May 08, 2014 12:57:57 AM
Today, let's start with some simple methods. The simplest methods are the ones that fetching data from the list.
Before I start, I need to mention something very important about calling methods on a user-defined type. If you want to call any methods on the list to modify the data (for example, to start with a blank list and then append values to it), you must first create a blank list by setting it equal to [] or blank. You must set a list equal to something before you can modify it. This is not optional. You cannot call a mutator method (any method that modifies data) on NULL, even though I would love the ability to automatically initialize the object as soon as needed. There has been a bug about this on Microsoft Connect for nearly 10 years, so don't get your hope up that it will be fixed. So always start by setting your list equal to something. I would recommend doing it in the declaration.
>>> DECLARE @a List = ''
>>> PRINT CONVERT(nvarchar(max),@a)
[]
GetItem returns a single item. List indices start at 0.
>>> SET @a = '["spam", ''eggs'', 1234, """5678"""]'
>>> PRINT @a.GetItem(0)
spam
>>> PRINT @a.GetItem(2)
1234
Negative indices count back from the end.
>>> PRINT @a.GetItem(-1)
5678
You can't ask for items that don't exist, at least not with the default method....
>>> PRINT @a.GetItem(4)
Msg 6522, Level 16, State 1, Line 55
A .NET Framework error occurred during execution of user-defined routine or aggregate "List":
System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
System.ArgumentOutOfRangeException:
at System.Collections.Generic.List`1.get_Item(Int32 index)
at List.GetItem(SqlInt32 index)
.
But I decided to steal something from the Python dictionary, the ability to get an item with a default, if nothing could be found. The name had to change, because the SQL CLR does not allow method overloading, even though C# does (it will build, but not run).
>>> PRINT @a.GetItemWithDefault(4, 'Nothing There')
Nothing There
Lists can be sliced, using GetItems, in the Python fashion, where the first index is inclusive and the second index is exclusive.
>>> PRINT CONVERT(nvarchar(max),@a.GetItems(1, 2))
["eggs"]
When you slice a list, you get back another List.
>>> DECLARE @b List = @a.GetItems(1, 2)
>>> PRINT CONVERT(nvarchar(max),@b)
["eggs"]
If an index is negative, it counts back from the right.
>>> PRINT CONVERT(nvarchar(max),@a.GetItems(1, -1))
["eggs", "1234"]
You will return an empty list if you provide garbage indexes.
>>> PRINT CONVERT(nvarchar(max),@a.GetItems(4, 2))
[]
NULL for an index ignores that boundary and goes to the beginning or end. This is like a[:2].
>>> PRINT CONVERT(nvarchar(max),@a.GetItems(NULL, 2))
["spam", "eggs"]
And this is like a[2:] (the rest of the list).
>>> PRINT CONVERT(nvarchar(max),@a.GetItems(2, NULL))
["1234", "5678"]
As in Python, when slicing, if you ask for more data than there is available (normally index out of range), it doesn't raise an exception but silently deals with it.
>>> PRINT CONVERT(nvarchar(max),@a.GetItems(NULL, 1000))
["spam", "eggs", "1234", "5678"]
The C# GetRange method could also be useful from time to time, so I implemented it. Currently this does return an exception if you ask for something out of range, as normal for C# code.
>>> PRINT CONVERT(nvarchar(max),@a.GetRange(0, 2))
["spam", "eggs"]
That's all for today. Next time, I think I'll do adding, multiplying, etc.