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
MyCLRTVFunction @myTable --returns a table result calculated using the input

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?