Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-19
12
Medium Priority
?
1,649 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 1200 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 1200 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 300 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
 
LVL 4

Assisted Solution

by:petr_hlucin
petr_hlucin earned 1200 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 300 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 1200 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Okay. So what exactly is the problem here? How often have we come across situations where we need to know if two strings are 'similar' but not necessarily the same? I have, plenty of times. Until recently, I thought any functionality like that wo…
Prime numbers are natural numbers greater than 1 that have only two divisors (the number itself and 1). By “divisible” we mean dividend % divisor = 0 (% indicates MODULAR. It gives the reminder of a division operation). We’ll follow multiple approac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

876 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