CODEWITHSUNDEEP
c#loopsdatatable
sam smith
sam smith

Reputation: 107

Distinct column value in C# DataTable

I have a DataTable in C# that looks like the following. 

ID | Name | foodEaten | amountEaten
1  | Sam  | Burger    |   3
2  | Jeff | Burger    |   2
3  |Steve | Burger    |   1
4  |Clive | Mcdonalds |   4   
5  |Mike  | KFC       |   12 
6  |Cliff | KFC       |   5

Im trying to basically output it to HTML via, using webforms. The issue is, the output could always vary, but it will always need to group on the foodEaten.

What im after is basically a way to group the results on the foodEaten column, but then write out those who ate the food underneath. I just need pointing in the right direction of how I can dynamically assign this to a asp:repeater. Or something similar.

Ideally it would look like this on the page - 

 <h3> Burger(3)<h3>
 <p> Sam - 3 <p>
 <p> Jeff - 2 <p>
 <p> Steve - 1 <p>

Thanks

Sam

Edit -

I now have a distinct values datatable which contains the distinct foodEaten values! Now to work out how to compare.

Edit2 -

I now have a DataSet that contains the distinct 'foodEaten' Values as table names, and the respective rows in each table, based on the foodEaten column. Now I just need to bind that, dynmaically to a repeater!

Upvotes: 2

Views: 446

Answers (2)

dvo
dvo

Reputation: 2153

I would use LINQ because it is clean and easier than writing logic in a loop.

var groupedDT = dataTable.AsEnumerable()
                .GroupBy(x => x.Field<string>("foodEaten"))
                .Select(o => new
                {
                    food = o.Key,
                    personsCount = dataTable.AsEnumerable().Where(row => row.Field<string>("foodEaten") == o.Key).ToList().Count
                })
                .ToList();

This will return you a list of the distinct foods eaten with a nested list of the persons that ate that food with the amount they ate. You can access data (like Name) in a loop by doing something like groupedDT[ foodIndex ].dataRows[ personIndex ]["Name"]. Hope this helps.

====================================================================

** EDIT: Updated to show how this can feed to a nested repeater.

You need to add a ondatabound event to your main repeater. Then, when you bind your repeater, each item will fire the nested repeater binding. See below. Note: I also adjusted the LINQ to group the datatable so you can get just the count of persons (above).

aspx: 

<asp:Repeater runat="server" ID="repFood" OnItemDataBound="repFood_ItemDataBound">
    <ItemTemplate>

        <h3><%# Eval("food") + " (" + Eval("personsCount") + ")" %></h3>
        <asp:HiddenField runat="server" ID="hfFoodEaten" Value='<%# Eval("food") %>' />

        <asp:Repeater runat="server" ID="repPersons">
            <ItemTemplate>
                <p><%# Eval("[\"Name\"]") +  " - " + Eval("[\"amountEaten\"]") %></p>
            </ItemTemplate>
        </asp:Repeater>

    </ItemTemplate>
</asp:Repeater>

code-behind:

binding main repeater: 

repFood.DataSource = groupedDT;
repFood.DataBind();

ondatabound event: 

protected void repFood_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    var hfFoodEaten = e.Item.FindControl("hfFoodEaten") as HiddenField;
    var repPersons = e.Item.FindControl("repPersons") as Repeater;
    var dataSource = ViewState["DataTable"] as DataTable;

    repPersons.DataSource = dataSource.AsEnumerable().Where(row => row.Field<string>("foodEaten") == hfFoodEaten.Value).ToList();
    repPersons.DataBind();
}

Adding a hidden field to the main repeater items is an easy way to pass the foodEaten variable to the nested repeater. Use this to get the data rows where the food matches. Then from the aspx, use Eval to grab the ID, Name, amountEaten, or whatever you need. Hope this helps.

Upvotes: 1

Avo Nappo
Avo Nappo

Reputation: 630

If performance is not an issue, you could cast that table to Enumerable and then use Linq on it. Something like this:

var result = table.AsEnumerable().GroupBy(row => row["Food"]).Select(grp => new
{
    Food = grp.Key,
    Eaten = grp.Sum(eaten => (int)eaten["amountEaten"])
});


foreach (var grp in result)
{
    log($"{grp.Food}  ->  {grp.Eaten}");                
}

Upvotes: 0

Related Questions