Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
536 views
in Technique[技术] by (71.8m points)

c# - SqlFunction fails to open context connection despite DataAccessKind.Read present

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...