Datatable returning more rows than necessary

Question

I'm returning values from database in a DataTable. In the database I have 5 values and if I run the query in Management Studio I get the expected result. However my issue is when returning them via C# function I get 9 values

protected static DataTable GetData()
{
    using (SqlConnection con = new SqlConnection(Common.ConnectionString))
    {

        string myQuery = "SELECT MESSAGE FROM MYTABLE ORDER BY CREATEDATE DESC";
        using (SqlCommand cmd = new SqlCommand(sqlQuery, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                con.Open();
                sda.SelectCommand = cmd;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }
        }
    }
}

And to display this I have nested asp Repeater which are as following

 <asp:Repeater ID="Repeater1" runat="server">
    <HeaderTemplate>
        <table id="myt">
            <tr>
                <th>Message
                </th>
            </tr>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td class="sendbubble">
                <%# DataBinder.Eval(Container.DataItem, "MESSAGE") %>
            </td>
            <asp:Repeater ID="InnerRepeater" runat="server">
                <ItemTemplate>
                    <tr>
                        <td class="receivebubble" style="color: #ffffff;">
                            <%# DataBinder.Eval(Container.DataItem,"MESSAGE") %>
                        </td>
                    </tr>
                </ItemTemplate>
            </asp:Repeater>
        </tr>
    </ItemTemplate>
</asp:Repeater>

I don't understand why in Repeater1 I get duplicate values. In the Page_Load I have

Repeater1.DataSource = GetData();
Repeater1.DataBind();

3 Values are from Manager 2 From Employee

These values are separated by a Flag column which contains data either manager or employee

Thanks in advance for all your help and support

Answer 1

In the first repeater, you need to load only the 2 flags. For each flag, you have another repeater, where you create the rows with the corresponsing messages.

View:

<asp:Repeater ID="pageList" runat="server" OnItemDataBound="pageList_ItemDataBound">
                <HeaderTemplate>
                    <table id="myt">
                        <tr>
                            <th>Message
                            </th>
                        </tr>
                </HeaderTemplate>
                <ItemTemplate>
                    <tr>
                        <asp:Repeater ID="itemRepeater" runat="server">
                            <ItemTemplate>
                                <tr>
                                    <td class="receivebubble" style="color: #ffffff;">
                                        <%# DataBinder.Eval(Container.DataItem,"MESSAGE") %>
                                    </td>
                                </tr>
                            </ItemTemplate>
                        </asp:Repeater>
                    </tr>
                </ItemTemplate>
            </asp:Repeater>

Code behind:

protected void Page_Load(object sender, EventArgs e)
        {
            pageList.DataSource = GetFlags();
            pageList.DataBind();
        }

        protected void pageList_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            RepeaterItem item = e.Item;
            Repeater itemRepeater = (Repeater) e.Item.FindControl("itemRepeater");

            switch (item.ItemType)
            {
                case ListItemType.Header:
                    break;
                case ListItemType.Item:
                case ListItemType.AlternatingItem:
                    if (itemRepeater != null)
                    {
                        var flagType = (item.DataItem as DataRowView).Row["Flag"].ToString();
                        DataTable repeaterData = GetData(flagType);
                        itemRepeater.DataSource = repeaterData;
                        itemRepeater.DataBind();
                    }
                    break;
                case ListItemType.Footer:
                    break;
            }
        }

Business:

private static DataTable GetData(string flagType)
        {
            using (SqlConnection con = new SqlConnection("..."))
            {

                string myQuery = "SELECT MESSAGE FROM MYTABLE WHERE Flag = " + flagType + " ORDER BY CREATEDATE DESC";
                using (SqlCommand cmd = new SqlCommand(myQuery, con))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        con.Open();
                        sda.SelectCommand = cmd;
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }

        private static DataTable GetFlags()
        {
            using (SqlConnection con = new SqlConnection("..."))
            {

                string myQuery = "SELECT DISTINCT Flag FROM MYTABLE";
                using (SqlCommand cmd = new SqlCommand(myQuery, con))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        con.Open();
                        sda.SelectCommand = cmd;
                        DataTable dt = new DataTable();
                        sda.Fill(dt);
                        return dt;
                    }
                }
            }
        }