Solved

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

Posted on 2008-10-19
12
1,626 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 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

21 Experts available now in Live!

Get 1:1 Help Now