none
Accessing a "real" tableinside a UDF RRS feed

  • Question

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

     

    Sunday, November 4, 2007 10:09 PM

All replies

  • 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;

    }

     

     

     

    Monday, November 5, 2007 12:05 AM
  • 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.......

     

    Wednesday, November 7, 2007 2:01 PM
  • 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
    Wednesday, November 7, 2007 4:53 PM
  • 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.

    Wednesday, November 7, 2007 6:26 PM
  • 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.
    Thursday, November 8, 2007 11:31 AM
  • 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;
        }
    }
    Thursday, November 8, 2007 12:33 PM