TheCPUWizard-2007

I am having problems opening a table from within my CLR UDF. Cananyone provide the proper configuration so that I can query existing tables




Re: .NET Framework Data Access and Storage Accessing a "real" tableinside a UDF

TheCPUWizard-2007

This still gives error:

Code Block

private const string ConnectionString = @"context connection=true;";

struct EntityTypeDefinition

{

public string EntityType;

public EntityTypeDefinition(string entityType)

{

this.EntityType = entityType;

}

}

[SqlFunction(FillRowMethodName = "Fill", TableDefinition = "EntityType nvarchar(2048)",

DataAccess = DataAccessKind.Read,

SystemDataAccess = SystemDataAccessKind.Read)]

public static IEnumerable TableOfSmartEntityCatalog(string partition)

{

List<EntityTypeDefinition> m_List = new List<EntityTypeDefinition>();

try

{

string sql = String.Format("select [Key] from CacheData where PartitionName='{0}'", partition);

using (SqlConnection conn = new SqlConnection(ConnectionString))

{

conn.Open();

using (SqlCommand cmd = new SqlCommand(sql, conn))

{

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

string[] split = reader.GetString(0).Split(new char[] { '|' });

m_List.Add(new EntityTypeDefinition(split[0]));

}

}

}

}

catch (System.Exception ex)

{

m_List.Add(new EntityTypeDefinition(ex.Message));

}

foreach (EntityTypeDefinition definition in m_List)

{

yield return definition;

}

}

private static void Fill(object source, out string EntityType)

{

EntityTypeDefinition definition = (EntityTypeDefinition)source;

EntityType = definition.EntityType;

}






Re: .NET Framework Data Access and Storage Accessing a "real" tableinside a UDF

TheCPUWizard-2007

I really can't believe that there is not a way to access existing tables from within a TV UDF

The only pointers I have seen are to referencing an external wervice which in turn access the internal data. This just seems so totally out of line.......






Re: .NET Framework Data Access and Storage Accessing a "real" tableinside a UDF

Sergei Almazov

You didn't describe what problem do you have. Please post error messages, etc.

Your code looks like OK, just several notes:
1. Why do you wrap strings into EntityTypeDefinition, you can just create a List<string>
2. You don't need foreach statement. Just return m_List, it implements IEnumerable interface"
3. Wrap "SqlDataReader reader = cmd.ExecuteReader()" into using statement




Re: .NET Framework Data Access and Storage Accessing a "real" tableinside a UDF

TheCPUWizard-2007

Sergei,

Thanks for the response. The posted code is a (gross) over-simlipfication of the "real" code,but it does re-product the problem.

SQL Server throws an exception when the code tries to open the connection.






Re: .NET Framework Data Access and Storage Accessing a "real" tableinside a UDF

Sergei Almazov

Please paste the exception message/stack trace, it is not enough to know that something threw some exception.
To understand the problem I need to know what exception has been thrown.




Re: .NET Framework Data Access and Storage Accessing a "real" tableinside a UDF

Sergei Almazov

Ok, I found the problem. The actual problem lies in the way C# compiler implements iterators ("yield return"), it actually delays the loading of data till calling IEnumerator.MoveNext for the first time, and that is not permitted by SQL Server.

Try to replace the

foreach (EntityTypeDefinition definition in m_List)
{
yield return definition;
}


with

return m_list;

and everything will start working as expected. There are several possible solutions:
a) you can return m_list object, it already implements IEnumerable interface. If you need to filter the list, try to do it during data loading
b) move iterator to separate method like this:

public static IEnumerable TableOfSmartEntityCatalog(string partition)
{
....
}

return GetList(m_list);
}

private static IEnumerable<
EntityTypeDefinition> GetList(IEnumerable<EntityTypeDefinition> list)
{
foreach (EntityTypeDefinition definition in list)
{
// Do additional checks here

yield return definition;
}
}