带数组参数的 CLR 表值函数 [英] CLR Table-valued function with array argument

问题描述

我有一个像这样的 SQL CLR 函数:

I have a SQL CLR function like this one:

public partial class UserDefinedFunctions {
    [Microsoft.SqlServer.Server.SqlFunction(TableDefinition = "number int", FillRowMethodName = "FillRow")]
    public static IEnumerable MyClrFunction(object obj) {
        // read obj array input and then
        var result = new ArrayList();
        result.Add((SqlInt32)1);
        result.Add((SqlInt32)2);
        result.Add((SqlInt32)3);
        return result;
    }

    public static void FillRow(object obj, out SqlInt32 number) {
        number = (SqlInt32)obj;
    }

}

我想这样使用它:

DECLARE @x arrayOfInt

INSERT INTO @x VALUES (10)
INSERT INTO @x VALUES (20)
INSERT INTO @x VALUES (30)

SELECT * FROM dbo.MyClrFunction(@x)

arrayOfInt 是:

CREATE TYPE [dbo].[arrayOfInt] AS TABLE(
[item] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
    (
    [item] ASC
    ) WITH (IGNORE_DUP_KEY = OFF)
)

我遇到的问题是 arrayOfInt 与 sql_variant 不兼容.是否可以编写具有数组(表)参数的 CLR 表值函数?

Problem I have is that arrayOfInt is incompatible with sql_variant. Is possible to write CLR Table-valued function which has an array (table) argument?

推荐答案

SQLCLR 不支持表值参数 (TVP):

SQLCLR does not support Table-Valued Parameters (TVPs):

• 创建程序

[ type_schema_name.] 数据类型
...
CLR 过程指南:

• 表值或游标数据类型不能用作参数.

• Table-valued or cursor data types cannot be used as parameters.

创建功能

[ type_schema_name.] 参数数据类型
... 对于 CLR 函数，除了 text、ntext、image、user-定义的表类型和时间戳数据类型.

[ type_schema_name. ] parameter_data_type
... For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, user-defined table types and timestamp data types.

但是，您确实有多种选择:

However, you do have several options:

1. 如果数组是一个简单的数字和/或字符串列表，您始终可以发送一个带分隔符的值列表(通过 SqlString/NVARCHAR(MAX))并使用 String.Split() 对其进行解包.

1. If the array is a simple list of numbers and/or strings you can always send in a delimited list of values (via SqlString / NVARCHAR(MAX)) and use String.Split() to unpack it.

如果数组更复杂(即多个字段)，那么您可以将数据包装在 XML 中并将其作为 SqlXml 传入.

If the array is more complex (i.e. multiple fields), then you can wrap the data in XML and pass that in as SqlXml.

或者，如果您有一个复杂的数组，那么您可以创建一个 CLR UDT 作为您喜欢的任何结构并将其传递给函数.不过，这需要更多的努力.

Or, if you have a complex array then you can create a CLR UDT to be whatever structure you like and pass that into the Function. This requires a bit more effort, though.

另外，请记住，表值参数只是表(表变量)，而不是诸如数组和集合之类的内存数据结构.TVP 的主要优点和用例是在从应用程序向 SQL Server 发送数据时降低复杂性并提高性能.如果您已经在 SQL Server 内部创建了一个表变量，然后想要将它传递给 CLR 存储过程(或者可能是函数)，那么您需要做的就是以稍微不同的方式看待问题，然后您可以完成相同的基本事情:

Also, keep in mind that Table-Valued Parameters are just that, tables (table variables), and not in-memory data structures such as Arrays and Collections. The main benefit and use-case of TVPs are in reducing complexity and increasing performance when sending data to SQL Server from an application. If you are already inside SQL Server such that you are creating a table variable and then wanting to pass it to a CLR stored procedure (or maybe function), then all you need to do is look at the problem in a slightly different way and you can accomplish the same basic thing:

• 使用临时表代替表变量
• 将临时表名传递给 CLR 存储过程
• 使用进程内连接(即连接字符串 = "Context Connection = true;")，因为它可以访问本地临时对象
• 您可以使用传入的表名在执行的任何 SQL 中使用临时表
• 您可以通过对传入的表名执行简单的 SELECT * 来将该表中的数据获取到 .NET 上下文中，然后通过 SqlCommand.ExecuteReader 读取每一行() 和 SqlDataReader.Read()

• Use a temporary table instead of a table variable
• Pass the temporary table name to the CLR stored procedure
• Use the in-process connection (i.e. connection string = "Context Connection = true;") because it can access local temporary objects
• You can use the temporary table in any SQL that you execute by using the table name that was passed in
• You can get the data from that table into the .NET context by doing a simple SELECT * on the table name that was passed in, and then read each row via SqlCommand.ExecuteReader() and SqlDataReader.Read()

这篇关于带数组参数的 CLR 表值函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！