SQL CLR Split UDF中的“标识"列 [英] Identity column in SQL CLR Split UDF

问题描述

如何用标准的SQL CLR拆分UDF返回一个身份列，例如下面的代码将返回一个表，其中的字符串值由定界符拆分，我还需要以某种方式返回一个身份列.

How can I return a identity column with a standard SQL CLR Split UDF for example the code below will return a table with the string value split by delimiter, I need to somehow return an identity column as well.

<SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="value nvarchar(4000)")> _
Public Shared Function GetStrings(ByVal str As SqlString, ByVal delimiter As SqlString) As IEnumerable
    If (str.IsNull OrElse delimiter.IsNull) Then
        Return Nothing
    Else
        Return str.Value.Split(CChar(delimiter))
    End If
End Function
Public Shared Sub FillRow(ByVal row As Object, ByRef str As String)
    str = CType(row, String).Trim()
End Sub

推荐答案

对于CLR UDF，身份"没有任何特殊含义，因为您自己生成所有行.您只需要一个柜台.

In terms of a CLR UDF, "identity" doesn't have any particular meaning, since you are generating all of the rows yourself. All you want is a counter.

简单的答案是只在现场生成索引，然后发送复合值的 IEnumerable .像这样:

Simple answer would be to just generate the indexes on the spot and then send an IEnumerable of composite values. So something like this:

[SqlFunction(FillRowMethodName = "FillMyRow",
    TableDefinition = "ID int, Value nvarchar(4000)")]
public static IEnumerable GetStrings(SqlString str, SqlString delimiter)
{
    if (str.IsNull || delimiter.IsNull)
    {
        return null;
    }

    string[] values = str.Value.Split(delimiter.Value.ToCharArray());
    StringPair[] results = new StringPair[values.Length];
    for (int i = 0; i < values.Length; i++)
    {
        results[i] = new StringPair(i + 1, values[i]);
    }
    return results;
}

public static void FillMyRow(object row, ref int id, ref string value)
{
    StringPair pair = (StringPair)row;
    id = pair.ID;
    value = pair.Value;
}

public class StringPair
{
    public StringPair(int id, string value)
    {
        this.id = id;
        this.value = value;
    }

    public int ID { get; private set; }
    public string Value { get; private set; }
}

与标识列完全相同；您只是从数字1开始增加ID的计数器.

It's the exact same thing as an identity column; you're just incrementing a counter for the ID, starting from the number 1.

您可能还想考虑可以使用 ROW_NUMBER 在SQL本身中生成代理ID，因此根本不需要这样做.就我个人而言，那将是我的选择，但是如果您需要在CLR输出中执行此操作，则上面的代码应该可以解决问题.

You might also want to consider that a surrogate ID can be generated in SQL itself with ROW_NUMBER, so it may not be necessary to do this at all. That would be my choice, personally, but if you need to do it in the CLR output, the above code should do the trick.

这篇关于SQL CLR Split UDF中的“标识"列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！