How to convert datatable to dictionary in ASP.NET/C#

Question

The DataTable below:

ClassID  ClassName  StudentID  StudentName
    1        A          1000      student666
    2        B          1100      student111
    5        C          1500      student777
    1        A          1200      student222
    2        B          1080      student999

The dictionary key is composed of "ClassID ,ClassName " and value is composed of "StudentID,StudentName" .

Dictionary<string, string> d = new Dictionary<string, string>();

foreach (DataRow dr in table.Rows)
{
    string key=dr["ClassID"].ToString() + dr["ClassName"].ToString();
    if (!d.ContainsKey(key))
    {
        //Do something();......
    }
    else
    {
        //Do something();......
    }
}
foreach (var s in d.Keys)
{
    Response.Write(s+"|+"+d[s]+"<br>");
}

Is there a faster way?

assume that key is '1,A' ,Value should be ' 1000,student666' and '1200,student222'

Since this is quite simple, I'm gonna ask: what did you try ? — Simon Belanger, Jul 02 '13 at 01:34
`dictionary key is composed of "ClassID ,ClassName " and value is composed of "StudentID,StudentName"` ... What? — Nikhil Agrawal, Jul 02 '13 at 01:37
Could you please post your code, that you are trying to improve (make faster, etc). — Andrew Savinykh, Jul 02 '13 at 01:59
As @SimonBelanger pointed out in my answer, the key you specify is not unique. For example, what should be returned as the value corresponding to key 1,A? There are two rows. — pilotcam, Jul 02 '13 at 02:10
assume that key is '1,A' ,Value should be ' 1000,student666' and '1200,student222' — user1662936, Jul 02 '13 at 02:14
That's two values... do you want each element to be a list instead? — pilotcam, Jul 02 '13 at 02:22

score 2 · Answer 1 · edited May 23 '17 at 12:22

2

Try this:

Dictionary<string, string> d = new Dictionary<string, string>();

            foreach (DataRow dr in table.Rows)
            {
                string key=dr["ClassID"].ToString() + "-" + dr["ClassName"].ToString();
                string value=dr["StudentID"].ToString() + "-" + dr["StudentName"].ToString();
                if (!d.ContainsKey(key))
                {
                    d.Add(key, value);
                }

            }

Reference Dictionary.Add Method

OR ELSE Try Onkelborg's Answer

How to use compound key for dictionary?

edited May 23 '17 at 12:22

Community

1
1

answered Jul 02 '13 at 02:21

Olrac

1,527
2
10
22

Same problem as pilotcam's original answer. This silently kill any (StudentId, StudentName) past the first one. – Simon Belanger Jul 02 '13 at 02:28

score 2 · Accepted Answer · edited Jul 02 '13 at 03:24

2

Here goes then. Using Linq, you can group them then perform string concatenation if you want.

// Start by grouping
var groups = table.AsEnumerable()
        .Select(r => new {
                      ClassID = r.Field<int>("ClassID"),
                      ClassName = r.Field<string>("ClassName"),
                      StudentID = r.Field<int>("StudentID"),
                      StudentName = r.Field<string>("StudentName")
                  }).GroupBy(e => new { e.ClassID, e.ClassName });

// Then create the strings. The groups will be an IGrouping<TGroup, T> of anonymous objects but
// intellisense will help you with that.
foreach(var line in groups.Select(g => String.Format("{0},{1}|+{2}<br/>", 
                                       g.Key.ClassID, 
                                       g.Key.ClassName,
                                       String.Join(" and ", g.Select(e => String.Format("{0},{1}", e.StudentID, e.StudentName))))))
{
    Response.Write(line);
}

edited Jul 02 '13 at 03:24

Andrew Savinykh

25,351
17
103
158

answered Jul 02 '13 at 02:48

Simon Belanger

14,752
3
41
35

Good example of why I like Linq (+1) – pilotcam Jul 02 '13 at 02:54
I typed it in the answer box (no access to my development computer at the moment). There might be some typos but that's the gist of it. – Simon Belanger Jul 02 '13 at 03:16
'list = (from st in datatable.AsEnumerable() select new CcompositeClass { ClassID = st.Field("ClassID"), ClassName = st.Field("ClassName"),' – user1662936 Jul 02 '13 at 03:19
@SimonBelanger: Note that `DataTable.Rows` does not implement IEnumerable, so above as written won't work. I've edited your code to fix this. `System.Data.DataSetExtensions` assembly has to be referenced for this to work. – Andrew Savinykh Jul 02 '13 at 03:26
Also note that this is less useful in a common scenario where you need to pass data outside of the method and format and output them elsewhere, may be in more than one place. This is because the `groups` variable ends up being of anonymous type. You still can pass it outside if you declare the type to be *dynamic* but then you loose strong typing and get little benefit. It probably worth creating a new type here that can be passed around. – Andrew Savinykh Jul 02 '13 at 03:29

score 0 · Answer 3 · edited May 23 '17 at 11:52

0

The tricky thing here is the composite key (ClassID, ClassName). Once you identify that, it's easy to search this site for the solution.

I'd recommend using tuples as pointed out here: Composite Key Dictionary

edited May 23 '17 at 11:52

Community

1
1

answered Jul 02 '13 at 01:39

pilotcam

1,749
14
22

1

Wouldn't work, the composite key in his example is not unique. – Simon Belanger Jul 02 '13 at 01:42

score 0 · Answer 4 · answered Jul 02 '13 at 01:43

0

The easiest way is to use a string value of ClassID|ClassName as key. For example, use string value "1|A" for key for the first row, and string value "2|B" for key for the second row, etc.

answered Jul 02 '13 at 01:43

user2540812

212
2
2

Oh god now.. why would you create strings for this? It adds a layer of nothingness. Say you create that string. Now every time you want the ID you have to decompose again. – Simon Belanger Jul 02 '13 at 01:44
user2540812 Yes.At first I achieve this like your answer! But faster... Thanks for your answer anyway. – user1662936 Jul 02 '13 at 01:50

Andrew Savinykh · Answer 5 · 2013-07-02T02:53:04.993

Here is something that can give you an idea:

using System;
using System.Data;
using System.Collections.Generic;

namespace SO17416111
{
    class Class
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    // Note that definition of Class and Student only differ by name
    // I'm assuming that Student can/will be expanded latter. 
    // Otherwise it's possible to use a single class definition
    class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    class Program
    {
        static void Main()
        {
            DataTable table = GetData();
            Dictionary<Class, List<Student>> d = new Dictionary<Class, List<Student>>();

            foreach (DataRow dr in table.Rows)
            {                
                // If it's possible to get null data from the DB the appropriate null checks
                // should also be performed here
                // Also depending on actual data types in your DB the code should be adjusted as appropriate
                Class key = new Class {Id = (int) dr["ClassID"], Name = (string) dr["ClassName"]};
                Student value = new Student { Id = (int)dr["StudentID"], Name = (string)dr["StudentName"] };

                if (!d.ContainsKey(key))
                {
                    d.Add(key, new List<Student>());
                }
                d[key].Add(value);
            }
            foreach (var s in d.Keys)
            {
                foreach (var l in d[s])
                {
                    Console.Write(s.Id + "-" + s.Name + "-" + l.Id + "-" + l.Name + "\n");
                }
            }
        }

        // You don't need this just use your datatable whereever you obtain it from
        private static DataTable GetData()
        {
            DataTable table = new DataTable();
            table.Columns.Add("ClassID", typeof (int));
            table.Columns.Add("ClassName", typeof (string));
            table.Columns.Add("StudentID", typeof (int));
            table.Columns.Add("StudentName", typeof (string));

            table.Rows.Add(1, "A", 1000, "student666");
            table.Rows.Add(2, "B", 1100, "student111");
            table.Rows.Add(5, "C", 1500, "student777");
            table.Rows.Add(1, "A", 1200, "student222");
            table.Rows.Add(2, "B", 1080, "student999");
            return table;
        }
    }
}

Note, that this can be compiled and tested as a console application - I substituted your Response.Write with Console.Write. I'm also generating a test DataTable, you should be able to use one that is already present in your application. As far as Class/Student classes go, you have several options here: you can have two separate classes as I show, you can use the same class or you can even use a Tuple class. I suggest you use two separate classes, as it improves readability and maintainability.

Note if you just need to output them, you don't need a dictionary or anything to that effect:

// Add null checks and type conversions as appropriate
foreach (DataRow dr in table.Rows)
{
    Response.Write(dr["ClassID"] + "-" + dr["ClassName"] + "-" + dr["StudentID"] + "-" + dr["StudentName"] + "<br>");
}

How to convert datatable to dictionary in ASP.NET/C#

5 Answers5

Linked