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

Question

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

取概念here更进一步，我想编写一个 CLR Table-Valued Function对来自数据库的源数据表进行操作。

我很确定我了解在 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 来处理；但我希望它通过绑定(bind)到 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 连接到 have the CLR component query for the necessary data给定相关 key ；但这对数据库模式的变化很敏感。所以我希望只用 SQL 捆绑所有源数据并将其传递给函数。

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

Answer 1

事实证明，SQLCLR 函数上有一个有效输入的固定列表，由可用的 mapping between .NET datatypes and SQL datatypes 决定。

SQL 数据类型“表”在 CLR 中被显式调用为具有无映射。

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

备选方案

似乎可以通过 select ... for xml 扭曲输入 SqlXml 参数来获取表格数据。

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