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

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
kenfhendersonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

petr_hlucinCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kenfhendersonAuthor Commented:
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
petr_hlucinCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

kenfhendersonAuthor Commented:
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
Abh4ITCommented:
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
kenfhendersonAuthor Commented:
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
petr_hlucinCommented:
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
Abh4ITCommented:
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
kenfhendersonAuthor Commented:
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
petr_hlucinCommented:
Yes, on the database level sorting is exactly what you need and probably it is the fastest way to achieve what you need.
0
kenfhendersonAuthor Commented:
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
kenfhendersonAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.