Solved

How do I sort or group a .NET datatable using C# 2005

Posted on 2008-10-19
12
1,618 Views
Last Modified: 2013-12-17
I have a .NET datatable that I'm using C# 2005 to access.
What is the best way to group the records by one field. They don't have to be in order, but grouped.

E.g.  ungrouped table:
John | Smith | 12345 | Engineer
Joe | McCain | 99999 | Plumber
Ken | Henderson | 5551212 | Consultant
Jane | Doe | 11111 | Plumber

If I wanted to group on the last field "job", I would get both plumbers next to each other.
E.g.
John | Smith | 12345 | Engineer
Joe | McCain | 99999 | Plumber
Jane | Doe | 11111 | Plumber
Ken | Henderson | 5551212 | Consultant

 Order doesn't matter as long as rows with identical jobs are adjacent.

Thanks for your help,
Ken
0
Comment
Question by:kenfhenderson
  • 6
  • 4
  • 2
12 Comments
 
LVL 4

Accepted Solution

by:
petr_hlucin earned 400 total points
ID: 22753476
I'd solve it using a dictionary<string, list<record>> //record contains all information about people (name, surname, number, Job) except for job which is index of the dictionary.

Pseudo code follows.

dictionary<string, list<record>> dict;

for all records as record

{

  if (dict.Contains(record["Job"]))

    dict[record["Job"]].Add(record);

  else

  {

    List<record> l = new List<record>();

    l.Add(record);

    dict.Add(record["Job"], l);

  }

}

Open in new window

0
 

Author Comment

by:kenfhenderson
ID: 22753593
So, I would use a .NET list type? and the Sort method thereof?
You wouldn't happen to have any C# code for that would you?  :)
Thanks, Ken
0
 
LVL 4

Assisted Solution

by:petr_hlucin
petr_hlucin earned 400 total points
ID: 22753648
There is no need to use Sort() method of List. Basically you construct a Dictionary indexed by jobs which contains list of records. Hopefully the following C# code will help.
IDbCommand cmd = new ...; // construct the class according to your database type

// connect to the database and set the right SQL command

IDataReader sdr = cmd.ExecuteReader();

Dictionary<string, list<Person>> dict;
 

while (sdr.Read()) {

  Person p = new Person();

  p.Name = sdr.GetString(0);

  // set all other properties of p to the right values
 

  // Let's suppose Job is the 4th field returned by the query

  if (dict.Contains(sdr.GetString(3)))

    // There is already at least 1 person with the same job

    // Let's add the person to the list which is an item of the dicitionary

    dict[sdr.GetString(3)].Add(p);

  else

  {

    // No person with this job is in the current dictionary

    // We'll need to create a new item in the dictionary - a List

    List<Person> l = new List<Person>();

    // Add the current person to the newly created List

    l.Add(p);

    // Add this list to the Dictionary

    dict.Add(sdr.GetString(3), l);

  }

}

Open in new window

0
 

Author Comment

by:kenfhenderson
ID: 22754501
Thanks for the info on Dictionaries in .NET.
However, it seems that they do not allow duplicate keys. I need to have more than record with Job=Plumber.  I just need all the plumbers together so that as I go through the datatable, when the job has changed to something other than Plumber, I know there are no other Plumbers in the datatable.
Thanks, much.
Ken
0
 
LVL 1

Assisted Solution

by:Abh4IT
Abh4IT earned 100 total points
ID: 22756661
There are two points to consider.
1. Hash table is the best case for searching
2. List ok with respect to sorting.. But if your are doing search on a list first it does a sort and then tries to serach which is inefficient.

Yes if you have duplicate entries then yes..hashtable/dict has issues
One way of looking at is have a class defined as Employee
Attributes to be Name, Job etc...
And now you can have implrementation of IComparable
http://en.csharp-online.net/IComparable
Then you can define IComparer interfaces and have different comparison strategies. You can even start grouping based on different fields by implementing one comparer per attribute.
Some more details at
http://www.codeproject.com/KB/cs/GenericComparer.aspx
0
 

Author Comment

by:kenfhenderson
ID: 22757639
Hi,
I reallly do appreciate your advice. However, please note that I do NOT need the records to be sorted or orderred, only grouped.  I will have to read up on the IComparer interfaces.
Thanks again - Ken
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 4

Assisted Solution

by:petr_hlucin
petr_hlucin earned 400 total points
ID: 22761118
Yes, I know that dictionary by itself doesn't support duplicate values. This is the reason why I have the following if statement:
if (dict.Contains(record["Job"]))
Multiple records will be stored in the list which is a dictionary item.
0
 
LVL 1

Assisted Solution

by:Abh4IT
Abh4IT earned 100 total points
ID: 22764327
When you say Group  based on Job, It is the same that you get when you sort based on the job...Then when you browse thru it you will get them grouped. But I am not sure if .NET as it is supports some Grouping ideaology..may be in SQL Reporting Services/Crystal Reports...which supports SQL based Grouping. But another way if looking at it is use the Group By keyros iN SQL to have it grouped in your SQL Query itself. This has an issue that later when you want to change the grouping to based on another parameter, then you need to do it locally.
0
 

Author Comment

by:kenfhenderson
ID: 22765715
This is more what I'm looking for. I don't mind it sorted but don't need it sorted, simply grouped.
I am wondering if I could take the DataTable, put it into a DataSet, do a SELECT on it with ORDER BY, and pull it back out to a sorted DataTable.
Does anyone have an example of that?  This would all be done in-memory.
Thanks much - Ken
0
 
LVL 4

Assisted Solution

by:petr_hlucin
petr_hlucin earned 400 total points
ID: 22767266
Yes, on the database level sorting is exactly what you need and probably it is the fastest way to achieve what you need.
0
 

Author Comment

by:kenfhenderson
ID: 22771757
Here is my solution to the problem.
Unfortunately, because I have to use .NET 2.0 instead of .NET 3.5, I had to implement a copy routine the hard way.
It seems to work for me: sorting (and optionally filtering) an in-memory DataTable.
Cheers,
Ken Henderon

        DataTable groupAssignments(DataTable ungroupedTable)

        {

            DataTable groupedTable = new DataTable();

            DataRow[] foundRows;

            string rowFilter = "";

            string sortColumn = "AssignmentOwnerGUID";
 

            // Transfer the rows of the DataTable to the DataRow array in sorted order (optionally filtered)

            foundRows = ungroupedTable.Select(rowFilter, sortColumn);
 

            //  THE .CopyToDataTable METHOD EXISTS ONLY IN .NET 3.5

//            groupedTable = foundRows.CopyToDataTable();
 

            // Transfer the rows of the DataRow array to a new DataTable (can't use above code in .NET 2.0)

            groupedTable = CopyRowArrayToDataTable(foundRows);
 

            // See what it looks like now.

            ulog.dumpTableAndWait(groupedTable);
 

            return groupedTable;

        }
 

        DataTable CopyRowArrayToDataTable(DataRow[] inputArray)

        {

            DataTable outputTable = new DataTable();

            DataColumnCollection cols = outputTable.Columns;
 

            // Construct enough columns to hold all related fields

            cols.Add("BaaN_ResourceUID", typeof(string));

            cols.Add("BaaN_PeriodEnding", typeof(string));

            cols.Add("BaaN_Adjustment", typeof(string));

            cols.Add("BaaN_LastName", typeof(string));

            cols.Add("BaaN_FirstName", typeof(string));

            cols.Add("BaaN_ProjectUID", typeof(string));

            cols.Add("BaaN_WBS", typeof(string));

            cols.Add("BaaN_TaskUID", typeof(string));

            cols.Add("BaaN_ActualWork", typeof(string));

            cols.Add("BaaN_ActualWorkDate", typeof(string));

            cols.Add("BaaN_ActualOvertimeWork", typeof(string));

            cols.Add("ProjectGUID", typeof(string));

            cols.Add("ProjectName", typeof(string));

            cols.Add("TaskGUID", typeof(string));

            cols.Add("TaskName", typeof(string));

            cols.Add("ResourceGUID", typeof(string));

            cols.Add("ResourceName", typeof(string));

            cols.Add("AssignmentOwnerGUID", typeof(string));

            cols.Add("AssignmentOwnerNTAccount", typeof(string));

            cols.Add("AssignmentGUID", typeof(string));

            cols.Add("ActualDate", typeof(string));

            cols.Add("ActualWorkValue", typeof(string));

            cols.Add("ActualOvertimeValue", typeof(string));
 

            string BaaN_ResourceUID;

            string BaaN_PeriodEnding;

            string BaaN_Adjustment;

            string BaaN_LastName;

            string BaaN_FirstName;

            string BaaN_ProjectUID;

            string BaaN_WBS;

            string BaaN_TaskUID;

            string BaaN_ActualWork;

            string BaaN_ActualWorkDate;

            string BaaN_ActualOvertimeWork;

            string ProjectGUID;

            string ProjectName;

            string TaskGUID;

            string TaskName;

            string ResourceGUID;

            string ResourceName;

            string AssignmentOwnerGUID;

            string AssignmentOwnerNTAccount;

            string AssignmentGUID;

            string ActualDate;

            string ActualWorkValue;

            string ActualOvertimeValue;
 

            for (int i = 0; i < inputArray.Length; i++)

            {

                // Copy from array of DataRows into local fields to prevent the

                // "This row already belongs to another table." Exception

                BaaN_ResourceUID = inputArray[i]["BaaN_ResourceUID"].ToString();

                BaaN_PeriodEnding = inputArray[i]["BaaN_PeriodEnding"].ToString();

                BaaN_Adjustment = inputArray[i]["BaaN_Adjustment"].ToString();

                BaaN_LastName = inputArray[i]["BaaN_LastName"].ToString();

                BaaN_FirstName = inputArray[i]["BaaN_FirstName"].ToString();

                BaaN_ProjectUID = inputArray[i]["BaaN_ProjectUID"].ToString();

                BaaN_WBS = inputArray[i]["BaaN_WBS"].ToString();

                BaaN_TaskUID = inputArray[i]["BaaN_TaskUID"].ToString();

                BaaN_ActualWork = inputArray[i]["BaaN_ActualWork"].ToString();

                BaaN_ActualWorkDate = inputArray[i]["BaaN_ActualWorkDate"].ToString();

                BaaN_ActualOvertimeWork = inputArray[i]["BaaN_ActualOvertimeWork"].ToString();

                ProjectGUID = inputArray[i]["ProjectGUID"].ToString();

                ProjectName = inputArray[i]["ProjectName"].ToString();

                TaskGUID = inputArray[i]["TaskGUID"].ToString();

                TaskName = inputArray[i]["TaskName"].ToString();

                ResourceGUID = inputArray[i]["ResourceGUID"].ToString();

                ResourceName = inputArray[i]["ResourceName"].ToString();

                AssignmentOwnerGUID = inputArray[i]["AssignmentOwnerGUID"].ToString();

                AssignmentOwnerNTAccount = inputArray[i]["AssignmentOwnerNTAccount"].ToString();

                AssignmentGUID = inputArray[i]["AssignmentGUID"].ToString();

                ActualDate = inputArray[i]["ActualDate"].ToString();

                ActualWorkValue = inputArray[i]["ActualWorkValue"].ToString();

                ActualOvertimeValue = inputArray[i]["ActualOvertimeValue"].ToString();
 

                // Now synthesize a brand new row and put it into the output table.

                try

                {

                    outputTable.Rows.Add(new object[] { BaaN_ResourceUID, BaaN_PeriodEnding, BaaN_Adjustment,

                BaaN_LastName, BaaN_FirstName, BaaN_ProjectUID, BaaN_WBS, BaaN_TaskUID, BaaN_ActualWork, 

                BaaN_ActualWorkDate, BaaN_ActualOvertimeWork, ProjectGUID, ProjectName, TaskGUID, TaskName, 

                ResourceGUID, ResourceName, AssignmentOwnerGUID, AssignmentOwnerNTAccount, AssignmentGUID, 

                ActualDate, ActualWorkValue, ActualOvertimeValue });

                }

                catch (Exception ex)

                {

                    ulog.writeNTConsoleAndEventLog(ex, "Actuals",

                        "--Attempt to add row to outputTable in CopyRowArrayToDataTable failed.");

                    throw (ex);

                }

            }
 

            return outputTable;

        }

Open in new window

0
 

Author Comment

by:kenfhenderson
ID: 22786432
Folks,
I discovered that I one could sort on multiple columns simply by listing the sort columns, separated by commas.  (see attached snippet)
Ken

        DataTable groupAssignments(DataTable ungroupedTable)

        {

            DataTable groupedTable = new DataTable();

            DataRow[] foundRows;

            string rowFilter = "";

            string sortColumns = "AssignmentOwnerGUID,ProjectGUID,AssignmentGUID";
 

            // Transfer the rows of the DataTable to the DataRow array in sorted order (optionally filtered)

            foundRows = ungroupedTable.Select(rowFilter, sortColumns);
 

//            THE .CopyToDataTable METHOD EXISTS ONLY IN .NET 3.5 - SO, USE THE FOLLOWING METHOD...

//            groupedTable = foundRows.CopyToDataTable(foundRows);
 

            // Transfer the rows of the DataRow array to a new DataTable (can't use above code in .NET 2.0)

            groupedTable = CopyRowArrayToDataTable(foundRows);
 

            // See what it looks like now.

            ulog.dumpTableAndWait(groupedTable);
 

            return groupedTable;

        }

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
combo box display member not working 11 21
Object Oriented Best Practice 5 35
Wpf develop 5 32
Achieve json result 2 34
Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now