Skip table as parameter for SQLCLR TV-UDF

Question

We have a third-party DLL that can work with the DataTable source information and generate some useful values, and we are trying to connect it through SQLCLR to be called as a table UDF in SQL Server 2008.

Taking the concept here is another step, I would like to program the CLR Table-Valued Function , which works with the source data table from the database.

I am sure I understand what should happen on the T-SQL side; but, what should the method signature look like in .NET code (C #)? What will be the parameter data type for "table data from SQL Server?"

eg.

/* 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 object is a stupid example, which, of course, should be best handled in SQL; but I hope this illustrates the general purpose of table-in-> table-out through a table-valued UDF bound to the SQLCLR assembly.

I am not sure if this is possible; What will be the signature signature of SQLCLRInitMethod in C #?

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

If this is not possible, I know that I can use the "context connection = true" SQL connection in C # code to have the CLR component query for the necessary data , taking into account the corresponding keys; but it is sensitive to changes in the database schema. Thus, I hope that just SQL will fill in all the source data and pass it to the functions.

Bonus question - if it works at all, will it work with the table more than ?

Answer 1

It turns out that there is a fixed list of valid inputs for the SQLCLR function, determined by the available mapping between .NET data types and SQL data types

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

Ergo, it is not possible to pass INTO table data in the CLR table function as method parameters.

Alternatives

It seems possible to get tabular data using select ... for xml outlines to feed into the SqlXml parameter.

I have successfully used SqlConnection conn = new SqlConnection("context connection = true"); in .NET code so that TVF queries DB for the required tabular data.

Answer 2

This question seems to be (mostly) a duplicate:

CLR table function with array argument

Be that as it may, in this question I recommended: delimited list, XML or CLR UDT.

It is also possible to populate a table and load a DataTable from it into a function. Using a real table is most likely not recommended, as it will require additional efforts to make it "thread safe" (so as not to cross data with other SPIDs) and would require an additional cleaning process, since the function will not be able to execute DML to clear it, as only this was done with the data. In certain situations, this may be preferable, but probably not for this particular case. Fortunately, temporary tables are available in SQLCLR functions (as read-only, but they are not available at all in T-SQL functions). Using Temp tables will have the same advantages as using persistent tables, but not the disadvantages of collisions with other SPIDs or they need to be cleaned separately. The only requirement is that you use a Context Connection, as this is the only way to access objects based on the session (i.e., Tempo Tables).

So, for this particular case, I would recommend trying either the Temp table or the XML options.