.NET Framework inside SQL Server
I am having problems opening a table from within my CLR UDF. Can anyone provide the proper configuration so that I can query existing tables
I had originally posted to another area of the forums, but have not been getting any responses.
The original thread is here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=2363760&SiteID=1
It's a compatibility bug between CLR TVF and "yield return" iterators in C#. C# compiler moves the data access code into auto-generated enumerator class, and defers its execution until the first call of IEnumerator.MoveNext() thus violating the CLR TVF requirements. Please look at the following code that reproduces the bug:
public class TestClass
{
[SqlFunction(Name = "fn_Test", DataAccess = DataAccessKind.Read, TableDefinition = "ID int", FillRowMethodName = "FillTest")]
public static IEnumerable Test()
{
List<int> values = new List<int>();
using (SqlConnection conection = new SqlConnection("context connection = true;"))
using (SqlCommand command = new SqlCommand("select ID=1 union all select 2 union all select 3", conection))
{
conection.Open();
foreach (DbDataRecord dataRecord in command.ExecuteReader(CommandBehavior.CloseConnection))
{
values.Add(dataRecord.GetInt32(0));
}
}
//solution that doesn't work
for (int i = 0; i < values.Count; i++)
yield return values[i];
//working solution
//return values;
}
private static void FillTest(object value, out int id)
{
id = (int)value;
}
}
Sergei,
Yes - absolutely. However that wasn't the issue the OP had. His code looked more or less like your "working solution", his problems was with getting the error that data access wasn't allowed.
Niels
It is the TheCPUWizard-2007's problem. He uses the yield return statement, so compiler moves the code (including data-access code) to generated enumerator class. So actually data are read not in the TableOfSmartEntityCatalog method, but later, when SQL Server's infrastructure calls IEnumerator.MoveNext() for the first time. That's why "Data access is not allowed in this context", because actual data access appears after the method decorated with SqlFunction attribute.