none
How to return a table of key/value pairs from a C# CLR function. RRS feed

  • Question

  • I am looking for help on how to return a table of key/value pairs from a CLR function in SQL Server. I have this code. It doesn't work ATM. Still reading posts and watching videos, but they are all geared around returning a single value. I found 1 Microsoft post that uses IEnumerable, but to be honest I am not understanding the use in the context of their post. i.e. It looks like a lot of code to accomplish what would seem to be a very easy task. 

    Microsoft Post

    The string I am trying to parse out into Key/value pairs is | delimited groups.

    '8796093218848=null| 8796094300192=null| 8796094267424=null| 8796093677600=null| 8796093513760=null| 8796093349920=null| 8796093612064=null| 8796093972512=null| 8796094234656=null| 8796093939744=null'

    1. Parsing the string in a Console app works to a dictionary object, but in SQLFunction is does not. I can't figure out how to do the same thing in a CLR function. 

    2. Instead of a dictionary object, how to I put that into a table and stream it back? 

    Still working on this, but this is where I am so far based on the Microsoft and few other documents I have found.

    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    public partial class UserDefinedFunctions
    {
      private class KeyValue
      {
        public SqlString Key;
        public SqlString Value;
    
        public KeyValue(SqlString key, SqlString value)
        {
          Key = key;
          Value = value;
        }
      }
    
      [SqlFunction(DataAccess = DataAccessKind.Read,
                   FillRowMethodName = "ProductParser_FillRow",
                   TableDefinition = "Key nvarhcar(1024), value nvarchar(1024)")]
      public static IEnumerable ProductParser(SqlString ProductList)
      {
        string productList = ProductList.ToString();
          
        if (productList.Length >= 999)
        {
          productList = productList.Substring(0, productList.LastIndexOf('|') - 1);
        }
    
        productList = productList.Replace("PropertyValue:", "");
        productList = productList.Replace("{", "");
        var Pairs = productList.Split('|')
                               .Select(x => x.Split('='))
                               .ToDictionary(x => x[0], x => x[1]);
    
        foreach (KeyValuePair<string, string> Pair in Pairs)
        {
          Console.WriteLine(Pair.Key + " - " + Pair.Value);
        }
        return new SqlString (string.Empty);
      }
    }
    



    John M. Couch

    Friday, June 14, 2019 11:41 PM

Answers

  • Yes, if you want to write a table-valued function in the CLR, you need to use IEnumerable, no matter you like or not.

    It wasn't exactly yesterday I wrote a CLR function, but I wrote about it long ago, and you can find my introduction on
    http://www.sommarskog.se/arrays-in-sql-2005.html#CLR
    This is part of an article that discusses various ways of splitting list of values into table format.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by John Couch Tuesday, June 18, 2019 7:01 PM
    Saturday, June 15, 2019 8:09 AM

All replies

  • Yes, if you want to write a table-valued function in the CLR, you need to use IEnumerable, no matter you like or not.

    It wasn't exactly yesterday I wrote a CLR function, but I wrote about it long ago, and you can find my introduction on
    http://www.sommarskog.se/arrays-in-sql-2005.html#CLR
    This is part of an article that discusses various ways of splitting list of values into table format.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by John Couch Tuesday, June 18, 2019 7:01 PM
    Saturday, June 15, 2019 8:09 AM
  • TableDefinition = "Key nvarhcar(1024), value nvarchar(1024)")]


    Additional you should use correct spelling to get it working, it's nvarchar, not nvarhcar

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, June 17, 2019 7:38 AM
  • Thank you Erland. i don't know why your posts weren't coming up in my searches, but I really appreciate the direction. I mimicked the code you had in your post and got it to return as you described in the article. Using just the basic functionality. The problem I am running into now is the dual split. I have the table definition in there now, and trying to do the final breakout from a = separated value is what is causing me issues now. I found several articles using a dictionary object, but it doesn't work on SqlString objects, so i tried converting it to a string, and it still doesn't work with that. If I build the same logic outside of a CLR function it works fine. Not sure what I am missing, but the records return as follows:

    8796093218848=null
    8796094300192=null
    8796094267424=null
    8796093677600=null
    8796093513760=null
    8796093349920=null
    8796093612064=null
    8796093972512=null
    8796094234656=null
    8796093939744=null
    8796093644832=null

    And I was trying to get them to return as 

    

    I started modifying the code per another article using the table definition, but I still need to get the string split into an array I believe, and thats the part causing me problems. 

    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    public partial class HybrisCLR
    {
      //private class KeyValuePair
      //{
      //  public SqlString Key;
      //  public SqlString Value;
      //  public KeyValuePair(SqlString key, SqlString value)
      //  {
      //    Key = key;
      //    Value = value;
      //  }
      //}
    
      [Microsoft.SqlServer.Server.SqlFunction(
            FillRowMethodName = "ProuctParserFillRow")]
            //TableDefinition = "Key nvarchar(128), Value nvarchar(128)")]
      public static IEnumerable ProductParser(SqlString products,
                                              SqlString delimiter)
      {
        products = products.Value.Replace("~", "|");
        products = products.Value.Replace(",", "|");
    
        if (products.Value.Length >= 999)
        {
          products = products.Value.Substring(0, products.Value.LastIndexOf('|') - 1);
        }
    
        products = products.Value.Replace("PropertyValue:", "");
        products = products.Value.Replace("{", "");
        products = products.Value.Replace("}", "");
    
        return products.Value.Split(delimiter.Value.ToCharArray(0, 1));
      }
    
      public static void ProuctParserFillRow(object row, out string products)
      {
        products = (string)row;
        products = products.Trim();
      }
    }

    Thanks again Erland for your help.

     

    John M. Couch

    Monday, June 17, 2019 4:34 PM
  • I think it would be easier to use the roll-your-own version where you implement IEnumerable. I think this is a lot easier. (And keep in mind that I'm an SQL Server guy who only writes C# left-handedly!). Split puts you quite much into a straight-jacket.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, June 17, 2019 9:52 PM
  • Hi Erland,

    Just wanted to say thank you for all the guidance. You helped me figure it out. I got my code working, and I pasted below the final. really appreciate that you took the time to share.

    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    public partial class HybrisCLR
    {
      private class KeyValuePair
      {
        public SqlString Ki;
        public SqlString Val;
        public KeyValuePair(SqlString ki, SqlString val)
        {
          Ki = ki;
          Val = val;
        }
      }
    
      [Microsoft.SqlServer.Server.SqlFunction(
            FillRowMethodName = "ProductParserFillRow",
            TableDefinition = "Ki nvarchar(128), Val nvarchar(128)")]
      public static IEnumerable ProductParser(SqlString products, SqlString delimiter)
      {
        ArrayList KeyValuePairs = new ArrayList();
    
        products = products.Value.Replace("~ PropertyValue:", " | PropertyValue:");
    
        if (products.Value.Length >= 999)
        {
          products = products.Value.Substring(0, products.Value.LastIndexOf('|') - 1);
        }
    
        products = products.Value.Replace("PropertyValue:", "");
        products = products.Value.Replace("{", "");
        products = products.Value.Replace("}", "");
    
        string[] KVs = products.Value.Split('|');
    
        if (KVs.Length > 1)
        {
          for (int i = 0; i < KVs.Length; i++)
          {
            int Equal = KVs[i].IndexOf("=");
            if (Equal > 1)
            {
              KeyValuePairs.Add(new KeyValuePair((SqlString)KVs[i].Substring(0, Equal), (SqlString)KVs[i].Substring(Equal + 1)));
            }        
          }
        }
    
        return KeyValuePairs;
      }
    
      public static void ProductParserFillRow(object keyvaluepair, out SqlString Ki, out SqlString Val)
      {
        KeyValuePair KVP = (KeyValuePair)keyvaluepair;
        Ki = KVP.Ki;
        Val = KVP.Val;
      }
    }
    


    John M. Couch

    Tuesday, June 18, 2019 7:01 PM
  • Hi Olaf,

    Thanks for pointing that out. I have dyslexic typing sometimes.  


    John M. Couch

    Tuesday, June 18, 2019 7:02 PM