Conversion of DataTable to Dictionary

Question

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.

That's for the ease of appending strings instead of using something like String.Join. it would be efficient — Mrinal Kamboj, Mar 19 '15 at 12:29
@MrinalKamboj Do you plan to append anything after creating the dictionary? If not, then `StringBuilder` probably isn't very useful as the resulting type. Also don't assume that `string.Join` is less efficient than `StringBuilder` without bench marking. For all you know `string.Join` uses a `StringBuilder` or something even better. — juharr, Mar 19 '15 at 12:51
Here's a [question](http://stackoverflow.com/questions/585860/string-join-vs-stringbuilder-which-is-faster) specifically about `string.Join` and `StringBuilder`. — juharr, Mar 19 '15 at 12:58
@juharr Yes in this case once Dictionary is created, then for a given key, we append the values in the StringBuilder value. Idea of saying SB efficient was in perspective of its usage in this scenario. I am sure on bench marking for our scenario there will not be a huge difference — Mrinal Kamboj, Mar 19 '15 at 17:14

score 3 · Accepted Answer · edited Mar 19 '15 at 18:32

3

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

edited Mar 19 '15 at 18:32

Mrinal Kamboj

10,673
4
29
58

answered Mar 19 '15 at 12:22

B0Andrew

1,226
12
17

`Value` is a `StringBuilder`, not `String` in OP – VMAtm Mar 19 '15 at 12:23
Interesting let me try this, would it ensure that last string in StringBuilder doesn't get the delimiter "?:" after it – Mrinal Kamboj Mar 19 '15 at 12:35
@VMAtm The whole point of using a `StringBuilder` is for all the string concatenation, which is done in the `Aggregate` here, so it might as well just be a `string` at this point. Unless the OP plans to append even more stuff at a latter time. – juharr Mar 19 '15 at 12:41
@juharr I assume that OP will use the values in dictionary in other string operations. – B0Andrew Mar 19 '15 at 12:43
2

Instead of the `Aggregate` you could just use `string.Join` like `string.Join("?:", g.Select(r => string.Format(...)))`. That should be more efficient. – juharr Mar 19 '15 at 12:46
@B0Andrew True, if the OP plans to append more stuff, but almost nothing else would require a `StringBuilder`. I assume they wanted to initially use a `StringBuilder` specifically for concatenating the values, like in Cameron's answer. – juharr Mar 19 '15 at 12:47
@juharr correct the idea of using SB was to append the string, in my understanding the aggregate was a better choice in this case, since end solution needs to be value1?:valuie2?:value3, on using String.Format, we will get extra delimiter on one end, which is not required in current case. B0Andrew solution is what would work best for my requirement – Mrinal Kamboj Mar 19 '15 at 17:21

score 3 · Answer 2 · answered Mar 19 '15 at 12:38

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

I like your solution. The dictionary makes use of its own values (string builders) to build itself therefore it should be a little bit more efficient than my solution. — B0Andrew, Mar 19 '15 at 13:41
@Cameron as suggested in my question we already do it using the solution suggested by you, I wanted to figure out a solution using Linq. My current code almost looks same :) — Mrinal Kamboj, Mar 19 '15 at 17:16
@MrinalKamboj I saw that after I posted the answer, and didn't deem it unnecessary to remove, as it provides a solution that's not Linq. However, thanks for your comment as to why you did not accept my answer. :) — Cameron, Mar 19 '15 at 19:19

Conversion of DataTable to Dictionary

2 Answers2