CODEWITHSUNDEEP
c#datatable
akd
akd

Reputation: 6758

DataTable group by and iterate through C#

I have a datatable which was returned from database.

the content is similar below:

UserId | Address | Phone
 1        blah     0123
 1        blah2    3445
 2        sdsdf    2343
 2        ssf      2223
 3        sadss    2321

This is the content of DataTable which is returned from database.

Now I just would like to group the results by User Id and loop through to out put result

foreach (DataRow userRow in DataTableGroupedByUser){

foreach (DataRow restOfColumns in AddressAndPhoneRows){

Output user details } }

The output I would get should be:

User Id: 1

Address   Phone
1- blah   0123
2- blah2  3445

User Id 2:
  Address   Phone
1- sdsdf    2343
2- ssf      2223

user Id :3 ...

I hope you get the idea.

Basically I do not want to query the database many times for each user but just use one query to pull all data and handle the grouping in the memory.

There are also other reason for this.

Is there any way to achieve this by using DataTable and filtering?

Upvotes: 3

Views: 12590

Answers (2)

bowlturner
bowlturner

Reputation: 2016

You could get a unique list of users from your datatable (you can add an order by clause as well)

var users = MyDatatable.Select(x => x.User_ID).Distinct().ToList();

Then you can do your for loops

foreach (int u in users)
{
    var userInfo = MyDatatable.Where(x => x.User_Id == u).ToList();

    foreach(var info in userInfo)
    {
    ...
    }
}

Once it's in the data table you won't be going back to the database each time.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

You can use LINQ:

var groupedByUserId = table.AsEnumerable().GroupBy(row => row.Field<int>("UserId"));

foreach(var group in groupedByUserId)
{
    Console.WriteLine("User Id: {0}", group.Key);
    Console.WriteLine("Address   Phone");
    int rowNum = 0;
    foreach(DataRow row in group)
    {
        Console.WriteLine("{0}- {1}   {2}", ++rowNum, row.Field<string>("Address"), row.Field<string>("Address"));
    }
}

Upvotes: 10

Related Questions