SqlFunction无法打开，尽管DataAccessKind.Read present上下文连接 [英] SqlFunction fails to open context connection despite DataAccessKind.Read present
问题描述
我有一个SQLServer项目，一个非常简单的测试表值-功能： -
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;
}
请注意打开的连接上正在注释掉。一旦部署完毕，我可以在这样的SQL执行： -
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.
现在我去掉了 con.open（）和它失败： -
Now I uncomment the con.open() and it fails with:-
数据访问未在此不允许 上下文。任一情况下是一个 函数或方法不标明 DataAccessKind.Read或 SystemDataAccessKind.Read，是一 回调获得FillRow数据 表值函数的方法，或 是一个UDT验证方法。
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.
我不明白是什么问题，该TestConn功能已经得到了 DataAccessKind.Read 。
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?
推荐答案
问题如下：
-
SQLCLR不允许内部TestFillRow任何数据访问
SQLCLR does not allow any data access inside TestFillRow
尽管它看起来像你TestFillRow犯规存取数据，顺便编译器将code以收益的语句是通过实际延迟它的执行，直到第一个.MoveNext（）调用迭代器。因此下面的语句：
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"))
在 TestFillRow ...这是非法的得到执行。
gets executed inside TestFillRow... which is illegal.
不要使用收益回报;而不是加载整个结果到名单，其中;＆GT; 键，返回列表中的UD功能的结尾
Do not use yield return; instead load the whole result to a List<> and return the list at the end of the UD Function.
这篇关于SqlFunction无法打开，尽管DataAccessKind.Read present上下文连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
