Fill nested repeaters in a table

Question

I have a repeater inside another repeater. How can I display all the fields(surname) from db in an inner repeater. In the function outerRepeater_ItemDataBound, DataSource isn't recognized. I don't know exactly where I need to select all the surnames for a specific name.

I have this in my repeater.ascx:

<form id="form1" runat="server">
    <div>
        <asp:Repeater runat="server" ID="outerRepeater" OnItemDataBound="outerRepeater_ItemDataBound">
            <HeaderTemplate>
                <table>
                 <td><th>Name:</th></td>
                 <td><th>Surname:</th></td>
                </table>
            </HeaderTemplate>
            <ItemTemplate>
                    <h3><%#DataBinder.Eval(Container.DataItem,"Name")%></h3> 
                <asp:Repeater runat="server" ID="innerRepeater" >
                    <ItemTemplate>

                        <%#Eval("Surname") %>

                    </ItemTemplate>
                </asp:Repeater>
            </ItemTemplate>
        </asp:Repeater>
    </div>
</form>

And this in repeater.ascx.cs

public partial class Repeater : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            outerRepeater.DataSource = GetDataSource();
            outerRepeater.DataBind();
        }
    private DataTable GetDataSource()
    {
        SqlConnection myConnection = new SqlConnection(@"my connection string");

        SqlDataAdapter myCommand = new SqlDataAdapter("SELECT Distinct Name FROM Person ORDER BY Name ", myConnection);

        DataTable dt = new DataTable();
        myCommand.Fill(dt);

        myConnection.Close();
        return dt;
    }
    private void Page_Init(object sender, EventArgs e)
    {
        InitializeComponent();
    }
    private void InitializeComponent()
    {
        this.Load += new System.EventHandler(this.Page_Load);
    }
    protected void outerRepeater_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
           if (e.Item.ItemType == ListItemType.AlternatingItem || e.Item.ItemType == ListItemType.Item)
           {
               Repeater innerRepeater = e.Item.FindControl("innerRepeater") as Repeater;
               DataTable dt = GetSurname();
               innerRepeater.DataSource = dt;//here isn't su
               innerRepeater.DataBind();
           }
    }

    private DataTable GetSurname()
    {
        SqlConnection myConnection = new SqlConnection(@"my connection string");
        SqlDataAdapter da = new SqlDataAdapter("Select Surname from Person ", myConnection);
        DataTable dt = new DataTable();
        da.Fill(dt);
        return dt;
    }

Answer 1

1. Manually adding `Page_Load` handler

I am not sure that you really need to set Page_Load event handler in your code-behind. With default page settings it will be wired-up automatically.

Setting it manually is required only if you set the PagesSection.AutoEventWireup to false.

2. Table with repeater

It is possible to create a table with repeater - How to create a three column table in ASP.Net Repeater, but it will require a bit different markup with properly set table tags:

<asp:Repeater runat="server" ID="outerRepeater" OnItemDataBound="outerRepeater_ItemDataBound">
    <HeaderTemplate>
        <table>
            <thead>
                 <th>Name:</th>
                 <th>Surname:</th>
            </thead>
            <tbody>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td>
                <h3><%#DataBinder.Eval(Container.DataItem,"Name")%></h3> 
            </td>
            <td>
                <asp:Repeater runat="server" ID="innerRepeater" >
                    <ItemTemplate>
                        <%#Eval("Surname") %>
                    </ItemTemplate>
                </asp:Repeater>
            </td>
        </tr>
    </ItemTemplate>
    <FooterTemplate>   
            </tbody>             
        </table>
    </FooterTemplate>
</asp:Repeater>

And, perhaps, GridView is a better alternative to manual table creation.

3. Get all surnames for the given name

You obviously get all surnames because Select Surname from People doesn't filter anything - it just selects all items. To achieve what you want you will have to add some WHERE clause, that filter by name associated with the current row. The Name can be obtained through the e.Item.DataItem in the outerRepeater_ItemDataBound handler.

protected void outerRepeater_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.AlternatingItem || e.Item.ItemType == ListItemType.Item)
    {
        Repeater innerRepeater = e.Item.FindControl("innerRepeater") as Repeater;

        String name = ((DataRowView)e.Item.DataItem)[0].ToString();

        DataTable dt = GetSurname(name);
        innerRepeater.DataSource = dt;//here isn't su
        innerRepeater.DataBind();
    }
}

private DataTable GetSurname(String name)
{
    using (SqlConnection myConnection = new SqlConnection(@"Data Source=(LocalDb)\v11.0; Database = TSQL2012"))
    {
        using (SqlDataAdapter da = new SqlDataAdapter("Select Surname from Person WHERE Name = @name", myConnection))
        {
            da.SelectCommand.Parameters.AddWithValue("@name", name);

            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
    }
}

4. Not always disposing the connection

While you call the Close method on your connections it is better to use the using clause as it used in the previous code samples, because using will guarantee that whatever happens the connection will be closed for sure. The same applies as well to the SqlDataAdapter, but whether it is actually required or just a good rule of thumb I do not know.

5. Multiple SQL requests

Multiple SQL requests, especially when all the required information is directly related can lead to a very poor performance. You can either fetch all the names and surnames to process them in the page, or you can apply Optimal way to concatenate/aggregate strings to fetch single datatable with all the needed data:

aspx:

<asp:Repeater runat="server" ID="outerRepeater">
...
<ItemTemplate>
    <tr >
        <td>
            <h3><%#DataBinder.Eval(Container.DataItem,"Name")%></h3> 
        </td>
        <td>
            <label><%#DataBinder.Eval(Container.DataItem,"ConcatenatedSurnames")%></label>
        </td>
    </tr>
</ItemTemplate>

Code-behind:

private const String SelectCommand = @"
WITH Partitioned AS
(
SELECT Name,
    Surname,
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS NameNumber,
    COUNT(*) OVER  (PARTITION BY Name ORDER BY Name) AS NameCount
FROM Person
),
Concatenated AS
(
SELECT Name,
    CAST(Surname AS nvarchar) AS ConcatenatedSurnames, 
    NameNumber, 
    NameCount 
FROM Partitioned 
WHERE NameNumber = 1

UNION ALL

SELECT 
    P.Name, 
    CAST(C.ConcatenatedSurnames + ', ' + P.Surname AS nvarchar), 
    P.NameNumber, 
    P.NameCount
FROM Partitioned AS P
    INNER JOIN Concatenated AS C 
    ON P.Name = C.Name AND 
        P.NameNumber = C.NameNumber + 1
)
SELECT 
Name,
ConcatenatedSurnames
FROM Concatenated
WHERE NameNumber = NameCount";

private DataTable GetDataSource()
{
    using (SqlConnection myConnection = new SqlConnection(@"Data Source=(LocalDb)\v11.0; Database = TSQL2012"))
    {
        using (SqlDataAdapter myCommand = new SqlDataAdapter(SelectCommand, myConnection))
        {
            DataTable dt = new DataTable();
            myCommand.Fill(dt);

            return dt;
        }
    }
}