将表作为参数传递给 SQLCLR TV-UDF

Question

我们有一个第三方 DLL，可以对源信息的 DataTable 进行操作并生成一些有用的值，并且我们尝试通过 SQLCLR 将其连接起来，以便在 SQL Server 2008 中作为表值 UDF 进行调用

。 这里更进一步，我想编写一个< a href="http://msdn.microsoft.com/en-us/library/ms131103.aspx" rel="noreferrer">CLR 表值函数，对数据库中的源数据表进行操作。

我非常确定我了解 T-SQL 方面需要发生什么；但是，.NET (C#) 代码中的方法签名应该是什么样子？“来自 SQL Server 的表数据”的参数数据类型是什么？

例如

/* Setup */
CREATE TYPE InTableType 
AS TABLE (LocationName VARCHAR(50), Lat FLOAT, Lon FLOAT)
GO 

CREATE TYPE OutTableType 
AS TABLE (LocationName VARCHAR(50), NeighborName VARCHAR(50), Distance FLOAT)
GO

CREATE ASSEMBLY myCLRAssembly 
FROM 'D:\assemblies\myCLR_UDFs.dll' 
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO
CREATE FUNCTION GetDistances(@locations InTableType)
RETURNS OutTableType
AS 
EXTERNAL NAME myCLRAssembly.GeoDistance.SQLCLRInitMethod
GO

/* Execution */

DECLARE @myTable InTableType
INSERT INTO @myTable(LocationName, Lat, Lon) VALUES('aaa', -50.0, -20.0)
INSERT INTO @myTable(LocationName, Lat, Lon) VALUES('bbb', -20.0, -50.0)
SELECT * FROM @myTable

DECLARE @myResult OutTableType
INSERT INTO @myResult
GetDistances @myTable /* SQLCLR Call: GeoDistance.SQLCLRInitMethod(@myTable) */

纬度/经度 ->距离问题是一个愚蠢的例子，当然最好完全用 SQL 来处理；但我希望它能说明 table-in -> 的总体意图通过绑定到 SQLCLR 程序集的表值 UDF 进行表输出。

我不确定这是否可能； SQLCLRInitMethod 方法签名在 C# 中是什么样子？

public class GeoDistance
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable SQLCLRInitMethod(<appropriateType> myInputData)
    {
      //...
    }

    public static void FillRow(...)
    {
      //...
    }
}

如果不可能，我知道我可以在 C# 代码中使用“context connection=true”SQL 连接到 让 CLR 组件查询给定相关键的必要数据；但这对数据库模式的更改很敏感。所以我希望只让 SQL 捆绑所有源数据并将其传递给函数。

额外问题 - 假设这完全有效，它是否也适用于多个输入表？

Answer 1

事实证明，SQLCLR 函数上有一个固定的有效输入列表，由可用的 确定.NET 数据类型和 SQL 数据类型之间的映射

SQL 数据类型“表”通过 CLR 显式调用为无映射。

因此，无法将表值数据作为方法参数传递到表值 CLR 函数中。

替代方案

似乎确实可以通过 select ... for xml 扭曲来获取表格数据，并将其输入到 SqlXml 参数中。

我已成功在 .NET 代码中使用 SqlConnection conn = new SqlConnection("context connection = true"); 让 TVF 查询数据库以获取所需的表格数据。

Answer 2

这个问题似乎（大部分）重复：

CLR 表值函数带有数组参数

作为快速说明，在该问题中我建议：分隔列表、XML 或 CLR UDT。

函数中还可以选择填充表格并从中加载数据表。可能不建议使用真实的表，因为它需要额外的努力来使其“线程安全”（不与其他 SPID 交叉数据），并且需要额外的清理过程，因为该函数无法执行 DML语句在处理完数据后对其进行清理。在某些情况下，这可能是首选，但可能不适用于这种特殊情况。幸运的是，临时表可以在 SQLCLR 函数中访问（只读，但在 T-SQL 函数中根本无法访问）。使用临时表具有与使用永久表相同的优点，但没有与其他 SPID 发生冲突或需要单独清理的缺点。唯一的要求是您使用上下文连接，因为这是访问基于会话的对象（即临时表）的唯一方法。

因此，对于这种特殊情况，我建议尝试临时表或 XML 选项。