通过表作为参数来SQLCLR TV-UDF [英] Pass table as parameter to SQLCLR TV-UDF

问题描述

我们有一个第三方的DLL可以在源信息的DataTable工作并产生了一些有用的价值，我们正在试图通过SQLCLR把它挂可以被调用为表值UDF中的SQL Server 2008。

We have a third-party DLL that can operate on a DataTable of source information and generate some useful values, and we're trying to hook it up through SQLCLR to be callable as a table-valued UDF in SQL Server 2008.

以概念的 一步，我想编写一个 CLR表值函数是从DB源数据的工作表

Taking the concept here one step further, I would like to program a CLR Table-Valued Function that operates on a table of source data from the DB.

我敢肯定，我明白需要对事物的T-SQL侧发生;但是，又该方法签名看起来像在.NET（C＃）代码？什么是为参数的数据类型从SQL Server表中的数据？

I'm pretty sure I understand what needs to happen on the T-SQL side of things; but, what should the method signature look like in the .NET (C#) code? What would be the parameter datatype for "table data from 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处理一个愚蠢的例子;但我希望它说明表中的总的意图 - >表出来，通过绑在SQLCLR组装表值UDF

The lat/lon -> distance thing is a silly example that should of course be better handled entirely in SQL; but I hope it illustrates the general intent of table-in -> table-out through a table-valued UDF tied to a SQLCLR assembly.

我不能肯定这是可能;将在SQLCLRInitMethod方法签名看起来像在C＃？

I am not certain this is possible; what would the SQLCLRInitMethod method signature look like in the C#?

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

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

如果这是不可能的，我知道我可以用一个上下文连接=真正的C＃代码中的SQL连接的have的CLR组件查询所需数据的给出的相关键;但是这在数据库架构的变化很敏感。所以我希望只是有SQL包了所有的源数据，并将其传递给函数

If it's not possible, I know I can use a "context connection=true" SQL connection within the C# code to have the CLR component query for the necessary data given the relevant keys; but that's sensitive to changes in the DB schema. So I hope to just have SQL bundle up all the source data and pass it to the function.

奖金问题 - 假设这一切工作时，将它也与<工作STRONG>多个输入表？

Bonus question - assuming this works at all, would it also work with more than one input table?

推荐答案

原来，有一个SQLCLR的有效投入固定列表功能，通过.NET数据类型和SQL数据类型之间的可用映射确定

Turns out that there's a fixed list of valid inputs on a SQLCLR function, determined by the available mapping between .NET datatypes and SQL datatypes

SQL数据类型表中明确提出了具有不映射通过CLR。

SQL Datatype "table" is explicitly called out as having no mapping through the CLR.

人机工程学，这是不可能通过表值数据到表值CLR函数的方法参数。

Ergo, it's not possible to pass table-valued data INTO a table-valued CLR function as method parameters.

替代

它似乎可以通过来得到表格数据SELECT ... FOR XML 扭曲喂成 SQLXML 参数

It does seem possible to get tabular data in via select ... for xml contortions to feed into a SqlXml parameter.

我已经成功地使用 SqlConnection的康恩=新的SqlConnection（上下文连接=真正的）; 中的.NET代码，让TVF查询数据库为它所需要的表格数据。

I have successfully used SqlConnection conn = new SqlConnection("context connection = true"); in the .NET code to let the TVF query the DB for the tabular data it needs.

这篇关于通过表作为参数来SQLCLR TV-UDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！