我们有一个第三方 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 捆绑所有源数据并将其传递给函数。
额外问题 - 假设这完全有效，它是否也适用于多个输入表？
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.
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.
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?"
e.g.
/* 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) */
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.
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(...)
{
//...
}
}
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.
Bonus question - assuming this works at all, would it also work with more than one input table?
发布评论
评论（2）
事实证明，SQLCLR 函数上有一个固定的有效输入列表，由可用的 确定.NET 数据类型和 SQL 数据类型之间的映射
SQL 数据类型“表”通过 CLR 显式调用为无映射。
因此，无法将表值数据作为方法参数传递到表值 CLR 函数中。
替代方案
似乎确实可以通过
select ... for xml扭曲来获取表格数据，并将其输入到SqlXml参数中。我已成功在 .NET 代码中使用
SqlConnection conn = new SqlConnection("context connection = true");让 TVF 查询数据库以获取所需的表格数据。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 Datatype "table" is explicitly called out as having no mapping through the CLR.
Ergo, it's not possible to pass table-valued data INTO a table-valued CLR function as method parameters.
Alternatives
It does seem possible to get tabular data in via
select ... for xmlcontortions to feed into aSqlXmlparameter.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.这个问题似乎（大部分）重复：
CLR 表值函数带有数组参数
作为快速说明，在该问题中我建议：分隔列表、XML 或 CLR UDT。
函数中还可以选择填充表格并从中加载数据表。可能不建议使用真实的表，因为它需要额外的努力来使其“线程安全”（不与其他 SPID 交叉数据），并且需要额外的清理过程，因为该函数无法执行 DML语句在处理完数据后对其进行清理。在某些情况下，这可能是首选，但可能不适用于这种特殊情况。幸运的是，临时表可以在 SQLCLR 函数中访问（只读，但在 T-SQL 函数中根本无法访问）。使用临时表具有与使用永久表相同的优点，但没有与其他 SPID 发生冲突或需要单独清理的缺点。唯一的要求是您使用上下文连接，因为这是访问基于会话的对象（即临时表）的唯一方法。
因此，对于这种特殊情况，我建议尝试临时表或 XML 选项。
This question seems to be (mostly) a duplicate of:
CLR Table-valued function with array argument
As a quick note, in that question I recommended: delimited list, XML, or CLR UDT.
There is also the option of filling a table and loading the DataTable from it in the function. Using a real Table is likely not recommended as it would require extra effort to make it "thread safe" (to not cross data with other SPIDs) and would require an additional clean-up process since the Function would not be able to do a DML statement to clean it up once it was done with the data. In certain situations maybe this is preferred but probably not for this particular case. Fortunately, Temporary Tables are accessible within SQLCLR Functions (as read-only, but they are not accessible at all in T-SQL functions). Using Temp Tables would have the same advantages as using permanent Tables but not the disadvantages of collisions with other SPIDs or needing to be cleaned up separately. The only requirement is that you use the Context Connection as that is the only way to access session-based objects (i.e. Temp Tables).
So for this particular case, I would recommend trying either the Temp Table or XML options.