SqlFunction fails to open context connection despi

Question

I've got a SqlServer project with a very simple test for a Table-Valued-Function:-

[SqlFunction(TableDefinition = "forename nvarchar(50)", FillRowMethodName = "TestFillRow", DataAccess = DataAccessKind.Read)]
public static IEnumerable TestConn(int ID)
{
    using (SqlConnection con = new SqlConnection("context connection=true"))
    {
        //con.Open();
        yield return "Anthony";
    }
}

public static void TestFillRow(object obj, out string forename)
{
    forename = (string)obj;
}

Note the Open on the connection is currently commented out. Once deployed I can execute like this in SQL:-

SELECT * FROM [dbo].[TestConn](1)

All works fine.

Now I uncomment the con.open() and it fails with:-

Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

I don't see what the problem is, the TestConn function has got DataAccessKind.Read.

Anyone know of any other reasons for getting this error?

Answer 1

The problem is the following:

1. SQLCLR does not allow any data access inside TestFillRow

2. Even though it "looks" like your TestFillRow doesnt access data, the way the compiler translates code with "yield" statements is by actually deferring it's execution until the first .MoveNext() call to the iterator. Therefore the following statement:

   using (SqlConnection con = new SqlConnection("context connection=true"))        
   

   gets executed inside TestFillRow... which is illegal.

Do not use yield return; instead load the whole result to a List<> and return the list at the end of the UD Function.

Answer 2

"SQLCLR does not allow any data access inside TestFillRow" is a mistake.

If you don't use context connection = true connetion string you can access data inside FillRow method.