SQL CLR Table valued function to return table from DB

Justin Blake
Justin Blake used Ask the Experts™
on
Hi,

I am trying to create a CLR function to allow users to grab data from a table that changes names each month (name changes are outside my control).

I have everything compiling and running without any errors but I never get any rows back.

I have simplified things as much as possible to try to find the problem and am inserting a DataRow into the DataTable I want to return to rule out problems with the select but still don't get any rows.

C# code
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;


public class TableFunctions
{

    [SqlFunction(FillRowMethodName = "FillCLRTest")]
    public static IEnumerable CLRTest()
    {
        string query = "SELECT [id]" +
                "FROM table";

        DataTable results = new DataTable();

        try
        {
            using (SqlConnection conn = new System.Data.SqlClient.SqlConnection("context connection = true"))
            using (SqlCommand command = new SqlCommand(query, conn))
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                dataAdapter.Fill(results);

            DataRow dummyRow = results.NewRow();

            dummyRow[0] = "1234";
            results.Rows.Add(dummyRow);

            return results.Rows;

        }
        catch (Exception e)
        {

        }

        return null;


    }

    public static void FillCLRTest(Object obj
        , out SqlString id
        )
    {

        DataRow results = (DataRow)obj;

        id = new SqlString(results["id"].ToString());
    }


}

Open in new window



MSSQL Code
--drop assembly CLR

create assembly CLR authorization dbo
from 'D:\networkshare\Projects\Dev\justin\NETProjects\CLR\CLR\bin\Release\CLR.dll'
with permission_set = unsafe
go

--drop function CLRTest

create function CLRTest()
returns table(id nvarchar(255))
as external name CLR.TableFunctions.CLRTest
go

select * from CLRTest()

Open in new window



I have already previously enabled CLR on my MSSQL Server.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I got rid of the try catch that I had foolishly copied in from elsewhere and now I am seeing an error. I was missing DataAccessKind.Read from the declaration of the function in C#.


Changed line

[SqlFunction(FillRowMethodName = "FillCLRTest")]

Open in new window


to

[SqlFunction(FillRowMethodName = "FillCLRTest", DataAccess = DataAccessKind.Read)]

Open in new window


and it is working now.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial