locked
repeater group by RRS feed

  • Question

  • User181930479 posted

    I have records in my sql server db :

    ID | StudyName|  StudyText    | StudyLink                         

    1      study1         about ENR    http://www.en1.com      

    2      study1         about ENRA    http://www.en1a.com   

    3      study2         about ENR    http://www.en1.com   

    I want to group the data so that the repeater will show me :

    study1

    about ENR    http://www.en1.com

    about ENRA    http://www.en1a.com  

    study2

    study2         about ENR    http://www.en1.com  

    any hint ?

    Monday, December 18, 2017 3:24 PM

All replies

  • User475983607 posted

    Assuming TSQL, use GROUP BY.

    SELECT 
    	StudyText AS  StudyText, 
    	StudyLink AS StudyLink,
    	StudyName AS StudyName
    FROM TheTableInQuestion
    GROUP BY StudyText, StudyLink, StudyName  

    Then use basic looping logic to write the HTML as you like.

    Monday, December 18, 2017 3:58 PM
  • User-707554951 posted

    Hi NAF, <o:p></o:p>

    <o:p> 1. Use following code to retrieved data from data base:</o:p>

     using System.Data;
    using System.Configuration;
    using System.Data.SqlClient;
    
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name, Country FROM Customers"))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }

    https://www.aspsnippets.com/Articles/Display-data-from-database-in-HTML-table-in-ASPNet.aspx

    The use nested repeater to achieve what you want by using the code below:

       <asp:Repeater ID="rptCustomers" runat="server"  OnItemDataBound="rptCustomers_ItemDataBound">
    
                    <ItemTemplate>
                        <asp:Label ID="StudyNamelb" runat="server" Text='<%# Eval("StudyName") %>'></asp:Label><br />
                        <asp:Repeater ID="rpt2" runat="server">
                            <ItemTemplate>                   
    <%# Eval("StudyText") %> <asp:LinkButton ID="LinkButton1" runat="server"> <%# Eval("StudyLink") %> </asp:LinkButton>
                                <br />
                            </ItemTemplate>
                        </asp:Repeater>
                    </ItemTemplate>
                </asp:Repeater>

    CodeBehind:

     protected void Page_Load(object sender, EventArgs e)
            {
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[4] { new DataColumn("ID", typeof(int)),
                                new DataColumn("StudyName", typeof(string)),
                                new DataColumn("StudyText",typeof(string)),
                                  new DataColumn("StudyLink",typeof(string))});
                dt.Rows.Add(1, "study1", "about ENR","http://www.en1.com");
                dt.Rows.Add(2, "study1", "about ENRA", "http://www.en1a.com");
                dt.Rows.Add(3, "study2", "about ENR", "http://www.en1.com");
                var grouped = from table in dt.AsEnumerable()
                              group table by new { placeCol = table["StudyName"] } into groupby
                              select new
                              {
                                  Value = groupby.Key,
                                  ColumnValues = groupby
                              };
              
                
                DataTable pdt = new DataTable();
                pdt.Columns.Add("StudyName");
                foreach (var key in grouped)
    
                {
                    pdt.Rows.Add(key.Value.placeCol);          
                }
                rptCustomers.DataSource = pdt;
                rptCustomers.DataBind();
             
    
            }
    
            protected void rptCustomers_ItemDataBound(object sender, RepeaterItemEventArgs e)
            {
                if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
                {
    
                    Repeater innerRepeater = (Repeater)e.Item.FindControl("rpt2");
                    Label lb = (Label)e.Item.FindControl("StudyNamelb");
                    string studyname = lb.Text;
                    DataTable cdt = new DataTable();
                    cdt.Columns.AddRange(new DataColumn[2]{
                                new DataColumn("StudyText",typeof(string)),
                                  new DataColumn("StudyLink",typeof(string))});
                    DataTable dt = new DataTable();
                    dt.Columns.AddRange(new DataColumn[4] { new DataColumn("ID", typeof(int)),
                                new DataColumn("StudyName", typeof(string)),
                                new DataColumn("StudyText",typeof(string)),
                                  new DataColumn("StudyLink",typeof(string))});
                    dt.Rows.Add(1, "study1", "about ENR", "http://www.en1.com");
                    dt.Rows.Add(2, "study1", "about ENRA", "http://www.en1a.com");
                    dt.Rows.Add(3, "study2", "about ENR", "http://www.en1.com");
                    var row = from c in dt.AsEnumerable() where c.Field<string>("StudyName") == studyname select c;
                    foreach (DataRow r in row)
                    {
                        cdt.Rows.Add(r.Field<string>("StudyText").ToString(), r.Field<string>("StudyLink").ToString());
                    }
                    innerRepeater.DataSource = cdt;
                    innerRepeater.DataBind();
                }
            }

    Output:

    Best regards <o:p></o:p>

    Cathy<o:p></o:p>

    Tuesday, December 19, 2017 9:12 AM