CODEWITHSUNDEEP
c#.netlinqdictionarydatatable
Mrinal Kamboj
Mrinal Kamboj

Reputation: 11482

Conversion of DataTable to Dictionary<String,StringBuilder>

DataTable has following column names:

Name, Value, Type, Info

Need to convert a structure Dictionary

Where Name is the Key (string) and other values will be appended to the StringBuilder like "Value,Type,Info", however it is possible to have repetitive Name column value, then successive each appended value to the StringBuilder will use a separator like ?: to depict the next set of value.

For eg: if the DataTable data is like:

Name, Value, Type, Info

one     1     a   aa
one     11    b   bb
two     2     c   cc
two     22    dd  ddd
two     222   ee   eee

Now the result structure should be like:

Dictionary<String,StringBuilder> detail = new Dictionary<String,StringBuilder>
{
{[one],[1,a,aa?:11,b,bb},
{[two],[2,c,cc?:22,dd,ddd?:222,ee,eee}
}

It is easy to achieve the same using for loop, but I was trying to do it via Linq, so I tried something like:

datatable.AsEnumerable.Select(row =>
{
  KeyValuePair<String,StringBuilder> kv = new  KeyValuePair<String,StringBuilder>();

 kv.key = row[Name];
 kv.Value = row[Value]+","+row[Type]+","+row[Info]

 return kv;
}).ToDictionary(y=>y.Key,y=>y.Value)

This code doesn't take care of repetitive keys and thus appending, probably I need to use SelectMany to flatten the structure, but how would it work in giving me a dictionary with requirements specified above, so that delimiters can be added to be existing key's value. Any pointer that can direct me in the correct direction.

Upvotes: 1

Views: 5132

Answers (2)

Andrei Bucurei
Andrei Bucurei

Reputation: 2413

Edited:

datatable.AsEnumerable()
          .GroupBy(r => (string)r["Name"])
          .Select(g => new
            {
                Key = g.Key,
                // Preferred Solution
                Value = new StringBuilder(
                             g.Select(r => string.Format("{0}, {1}, {2}",   
                                      r["Value"], r["Type"], r["Info"]))
                                 .Aggregate((s1, s2) => s1 + "?:" + s2))                    
                /*
                //as proposed by juharr
                Value = new StringBuilder(string.Join("?:", g.Select( r => string.Format("{0}, {1}, {2}", r["Value"], r["Type"], r["Info"]))))
                */
            })
          .ToDictionary(p => p.Key, p => p.Value);

Upvotes: 3

Cameron
Cameron

Reputation: 2594

Something like this should work, and it avoid some complex Linq that could get irritating to debug:

public static Dictionary<string, StringBuilder> GetData(DataTable table)
{
    const string delimiter = "?:";
    var collection = new Dictionary<string, StringBuilder>();

    // dotNetFiddle wasn't liking the `.AsEnumerable()` extension
    // But you should still be able to use it here
    foreach (DataRow row in table.Rows)
    {
        var key = (string)row["Name"];

        var @value = string.Format("{0},{1},{2}", 
                                   row["Value"], 
                                   row["Type"], 
                                   row["Info"]);

        StringBuilder existingSb;

        if (collection.TryGetValue(key, out existingSb))
        {
            existingSb.Append(delimiter + @value);
        }
        else
        {
            existingSb = new StringBuilder();
            existingSb.Append(@value);
            collection.Add(key, existingSb);
        }
    }

    return collection;
}

Upvotes: 3

Related Questions