Grouping data with datatable with ASP.NET

Question

I am having trouble to grouping the data from the below table format and trying to group the data as mentioned in second table format

 legacy                   subid                               converted     licPart         count
Ent 100 EP Lic E-LTU    7ks7gp-qc42sn-g7zk0q-7pzmb7-xdp6yb  NL AC  CE E-LTU  LIC-CP-EN-100  100
Ent 100 EP Lic E-LTU    7ks7gp-qc42sn-g7zk0q-7pzmb7-xdp6yb  NL OB USR E-LTU  LIC-CP-EN-100  100
Ent 100 EP Lic E-LTU    7ks7gp-qc42sn-g7zk0q-7pzmb7-xdp6yb  NL OG EP E-LTU   LIC-CP-EN-100  100
Ent 100 EP Lic E-LTU    7ks7gp-qc42sn-g7zk0q-7pzmb7-xdp6yb  NL AC  CE E-LTU  LIC-CP-EN-100  100
Ent 100 EP Lic E-LTU    7ks7gp-qc42sn-g7zk0q-7pzmb7-xdp6yb  NL OB USR E-LTU  LIC-CP-EN-100  100
Ent 100 EP Lic E-LTU    7ks7gp-qc42sn-g7zk0q-7pzmb7-xdp6yb  NL OG EP E-LTU   LIC-CP-EN-100  100
Ent 1K EP Lic E-LTU     7ks7gp-qc42sn-g7zk0q-7pzmb7-xdp6yb  NL AC CE E-LTU   LIC-CP-EN-1K   1000
Ent 1K EP Lic E-LTU    7ks7gp-qc42sn-g7zk0q-7pzmb7-xdp6yb   NL OB USR E-LTU  LIC-CP-EN-1K   1000
Ent 1K EP Lic E-LTU    7ks7gp-qc42sn-g7zk0q-7pzmb7-xdp6yb   NL OG EP E-LTU    LIC-CP-EN-1K  1000

and I am trying to get the data as like this below for further assigning to repeater to display the info

 Ent 100 EP Lic E-LTU   7ks7gp-qc42sn-g7zk0q-7pzmb7-xdp6yb  NL AC CE E-LTU  LIC-CP-EN-100    100
                                                            NL OB USR E-LTU
                                                            NL OG EP E-LTU

 Ent 100 EP Lic E-LTU                                       NL AC CE E-LTU  LIC-CP-EN-100    100
                                                            NL OB USR E-LTU
                                                            NL OG EP E-LTU

Ent 1K EP Lic E-LTU                                         NL AC CE E-LTU   LIC-CP-EN-1K    1000  
                                                            NL OB USR E-LTU
                                                            NL OG EP E-LTU

I tried with the grid view during rowdatabound event and its working fine but i am not able to get the same with the repeater. I am sure we need to group the data before we are assigning it to the repeater

But I am not sure how to group the data as like mentioned above

This is my repeater code

<asp:Repeater ID="RepeaterEnterprise" runat="server" OnItemDataBound="ItemBound">
                            <HeaderTemplate>
                                <table>
                                    <tr>
                                        <th>Avaialable Endpoints
                                        </th>
                                        <th>Converted Endpoints
                                        </th>
                                    </tr>
                            </HeaderTemplate>
                            <ItemTemplate>
                                <tr>
                                <td>
                                <table>
                                <tr>
                                    <td>
                                        <asp:TextBox runat="server" ID="txtAvaialble" Text='<%#Eval("LicenseCount") %>' ReadOnly="true">
                                        </asp:TextBox>
                                    </td>
                                    <td>
                                        <asp:TextBox runat="server" ID="txtConverted" Text='' ReadOnly="true">
                                        </asp:TextBox>
                                    </td>
                                    <td>                                       
                                            <tr>
                                                <td>
                                                    <asp:Label ID="lblcount" runat="server" Text='<%# Eval("Converted") %>'></asp:Label>
                                                </td>
                                                <td>25*
                                                </td>
                                            </tr>
                                        </table>
                                        <table>
                                            <tr>
                                                <td>
                                                    <asp:TextBox runat="server" ID="txtEndpoints" Text=''>
                                                    </asp:TextBox>
                                                </td>
                                                <td>
                                                    <asp:TextBox runat="server" ID="txtTotalEndPoints" Text='' ReadOnly="true">
                                                    </asp:TextBox>
                                                </td>
                                            </tr>
                                        </table>
                                    </td>
                                </tr>

                            </ItemTemplate>
                            <SeparatorTemplate>
                                <tr>
                                    <td>
                                    </td>
                                </tr>
                            </SeparatorTemplate>
                            <AlternatingItemTemplate>
                                <table>
                                <tr>
                                    <td>
                                        <asp:TextBox runat="server" ID="txtAvaialbleEndPoints" Text='<%#Eval("Count") %>' ReadOnly="true">
                                        </asp:TextBox>
                                    </td>
                                    <td>
                                        <asp:TextBox runat="server" ID="txtConvertedEndPoints" Text='' ReadOnly="true">
                                        </asp:TextBox>
                                    </td>
                                    <td>                                       
                                            <tr>
                                                <td>
                                                    <asp:Label ID="lblcountryname" runat="server" Text='<%# Eval("Converted") %>'></asp:Label>
                                                </td>
                                                <td>25*
                                                </td>
                                            </tr>
                                        </table>
                                        <table>
                                            <tr>
                                                <td>
                                                    <asp:TextBox runat="server" ID="txtEndpoints" Text=''>
                                                    </asp:TextBox>
                                                </td>
                                                <td>
                                                    <asp:TextBox runat="server" ID="txtTotalEndPoints" Text='' ReadOnly="true">
                                                    </asp:TextBox>
                                                </td>
                                            </tr>
                                        </table>
                            </AlternatingItemTemplate>
                            <FooterTemplate>
                                </table>
                            </FooterTemplate>
                        </asp:Repeater>

Could any one have any idea on this solution how to make grouping?

Just wondering, the first 3 rows are the same as rows 4-6. Is that real data? It could affect the answer. — wazz, Dec 09 '17 at 07:41
@wazz its real data yeah we may get two more same type of data — Glory Raj, Dec 09 '17 at 08:28

Dan Dohotaru · Accepted Answer · 2017-12-10T16:23:49.080

the grouping rules are not obvious in your initial questions but based on your comments, i presume you need to

group the records by identifiers like subid
expose a subid result multiple times if converted field shows up many times

i imagine you have some sort of classes that resemble those bellow

public class InputModel
{
    public string legacy { get; set; }
    public string subid { get; set; }
    public string converted { get; set; }
    public string licPart { get; set; }
    public string count { get; set; }
}

public class OutputModel
{
    public string legacy { get; set; }
    public string subid { get; set; }
    public IList<string> list { get; set; }
    public string licPart { get; set; }
}

Among other solutions you can rely on a reducer/fold/aggregate approach and for that, C# comes in handy with enumerable .Aggregate (some nice examples to be found in here as well)

var seed = new List<OutputModel>();
var outcome = records
    .Aggregate(seed, (results, current) =>
    {

        var query = from result in results
                    where result.legacy == current.legacy
                        && (result.list == null
                            || !result.list.Contains(current.converted))
                    select result;
        var output = query.FirstOrDefault();
        if (output == null)
        {
            output = new OutputModel
            {
                legacy = current.legacy,
                subid = current.subid,
                licPart = current.licPart,
                list = new List<string>
                {
                    current.converted
                }
            };

            results.Add(output);
        }
        else
        {
            output.list.Add(current.converted);
        }

        return results;
    });

will give you this

Edit

on request see bellow one way of mapping a table to an input records collection

public static class Extensions
{
    public static IEnumerable<InputModel> Map(this DataTable instance)
    {
        foreach (DataRow row in instance.Rows)
        {
            yield return new InputModel
            {
                legacy = (string)row[0],
                subid  = (string)row[1],
                converted  = (string)row[2],
                licPart = (string)row[3],
                count = (string)row[4],
            };
        }
    }
}

thanks for you support but i need the grouping as like mentioned in second data format .. last list for the first Item i am having three items and for second item(LIC-CP-EN-1k) i am having 1 and then for third item (LIC_EN_CP_1k) two items .. i need to get three items for second and third items also — Glory Raj, Dec 10 '17 at 10:13
the business logic you want to use for the grouping was not that clear at first, but i've updated my answer to reflect your extra details. see the gist for the whole picture https://gist.github.com/dandohotaru/724d1206c79631219de338f44a8ee61b — Dan Dohotaru, Dec 10 '17 at 15:44
I am getting that data from datatable but i am not sure how to apply this logic with datatable .. — Glory Raj, Dec 10 '17 at 16:02
you just need to iterate over the rows of the table and extract table.row[index] to a value that you assign further — Dan Dohotaru, Dec 10 '17 at 16:22

Grouping data with datatable with ASP.NET

1 Answers1