Bind data repeater inside data repeater in ASP.NET

Question

I'm learning .NET at the moment, so forgive me if this is a silly question.

I have two MSSQL tables, one called 'Comment' and the other called 'CommentAdditionalAuthor'. The Comment table holds data from a comment form, such as comment title, date, main author name etc. The CommentAdditionalAuthor table contains information about other authors who contributed to the comment. So there is a one-to-many relationship there - each comment can have one or more additional authors.

I have a repeater control which binds data from the Comment table, which works nicely. Now what I want to do though is output the author details alongside the comment. So effectively a repeater within a repeater.

I've tried to clean up this code as much as possible but sorry that it's still pretty lengthy as I'd like to keep it relevant to what I'm doing so I can hopefully understand the solution. Having found other examples of this, I'm struggling to implement them as they bind to the data in different ways, which I just can't make work in my application.

The code in the aspx file is like this:

<div id="comments">
    <asp:Repeater ID="rptComments" runat="server">
    <ItemTemplate>
        <div class="comment-data">
            <h3 class="item">Submitted to <%# GetPageDetails(Eval("nodeid")) %> article on <%# Eval("created") %></strong></h3>
            <p class="item"><strong>Name</strong> <%# Eval("firstname") %> <%# Eval("surname") %></p>
            <p class="item"><strong>Occupation</strong> <%# Eval("occupation") %></p>
            <p class="item"><strong>Affiliation</strong> <%# Eval("affiliation") %></p>
            <p class="item"><strong>Email</strong> <a href='mailto:<%# Eval("email") %>'><%# Eval("email") %></a> <em>Publish email: <%# Eval("publishemail") %></em></p>
            <p class="item"><strong>Competing interests?</strong> <%# Eval("competingintereststext") %>&nbsp;</p>
            <p class="item"><strong>eLetter title</strong> <%# Eval("title") %></p>
            <p><%# Eval("comment").ToString().Replace("\n", "<br/>")%></p>


            <!-- This is what I want to do, but can't get it to bind: -->
            <div class="additional-authors">
                <h3>Additional authors</h3>
                <asp:Repeater id="rptAdditionalAuthors" runat="server">
                    <ItemTemplate>
                        <%# Eval("firstnameother")%><br>
                        <%# Eval("surnameother")%><br>
                        <%# Eval("occupationother")%><br>
                        <%# Eval("affiliationother")%><br>
                        <%# Eval("emailother")%><br>
                    </ItemTemplate>
                </asp:Repeater>
            </div>

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

And here's what the code-behind is doing:

    private void BindData()
    {
        var rr = _sqlHelper.ExecuteReader(string.Format("select * from comment {0} order by created desc", Filter));

        var commentDt = new DataTable("Comments");
        commentDt.Columns.Add("id", Type.GetType("System.Int32"));
        commentDt.Columns.Add("nodeid", Type.GetType("System.Int32"));
        commentDt.Columns.Add("firstname", Type.GetType("System.String"));
        commentDt.Columns.Add("surname", Type.GetType("System.String"));
        commentDt.Columns.Add("occupation", Type.GetType("System.String"));
        commentDt.Columns.Add("affiliation", Type.GetType("System.String"));
        commentDt.Columns.Add("title", Type.GetType("System.String"));
        commentDt.Columns.Add("email", Type.GetType("System.String"));
        commentDt.Columns.Add("publishemail", Type.GetType("System.Boolean"));
        commentDt.Columns.Add("competinginterests", Type.GetType("System.Boolean"));
        commentDt.Columns.Add("competingintereststext", Type.GetType("System.String"));
        commentDt.Columns.Add("comment", Type.GetType("System.String"));
        commentDt.Columns.Add("statusid", Type.GetType("System.Int32"));
        commentDt.Columns.Add("spam", Type.GetType("System.Boolean"));
        commentDt.Columns.Add("ham", Type.GetType("System.Boolean"));
        commentDt.Columns.Add("created",Type.GetType("System.DateTime"));

        while (rr.Read())
        {
            var dr = commentDt.NewRow();
            dr["id"] = rr.GetInt("id");
            dr["nodeid"] = rr.GetInt("nodeid");
            dr["firstname"] = rr.GetString("firstname");
            dr["surname"] = rr.GetString("surname");
            dr["occupation"] = rr.GetString("occupation");
            dr["affiliation"] = rr.GetString("affiliation");
            dr["title"] = rr.GetString("title");
            dr["email"] = rr.GetString("email");
            dr["publishemail"] = rr.IsNull("publishemail") == true ? false : rr.GetBoolean("publishemail");
            dr["competinginterests"] = rr.IsNull("competinginterests") == true ? false : rr.GetBoolean("competinginterests");
            dr["competingintereststext"] = rr.GetString("competingintereststext");
            dr["comment"] = rr.GetString("comment");
            dr["statusid"] = rr.GetInt("statusid");
            dr["spam"] = rr.IsNull("spam") == true ? false : rr.GetBoolean("spam");
            dr["ham"] = rr.IsNull("ham") == true ? false : rr.GetBoolean("ham");
            dr["created"] = rr.GetDateTime("created");

            commentDt.Rows.Add(dr);


            /* My failing attempt at the second bind:
            var rrAuthor = _sqlHelper.ExecuteReader(string.Format("select * from CommentOtherAuthor WHERE commentid = 81"));

            var otherAuthorDt = new DataTable("OtherAuthors");
            otherAuthorDt.Columns.Add("firstname", Type.GetType("System.String"));
            otherAuthorDt.Columns.Add("surname", Type.GetType("System.String"));
            otherAuthorDt.Columns.Add("occupation", Type.GetType("System.String"));
            otherAuthorDt.Columns.Add("affiliation", Type.GetType("System.String"));
            otherAuthorDt.Columns.Add("email", Type.GetType("System.String"));

            while (rrAuthor.Read())
            {
                var drAuthor = otherAuthorDt.NewRow();
                drAuthor["firstnameother"] = rr.GetString("firstname");
                drAuthor["surnameother"] = rr.GetString("surname");
                drAuthor["occupationother"] = rr.GetString("occupation");
                drAuthor["affiliationother"] = rr.GetString("affiliation");
                drAuthor["emailother"] = rr.GetString("email");
                otherAuthorDt.Rows.Add(drAuthor);
            }
            rptAdditionalAuthors.DataBind();
            */
        }

        var pgitems = new PagedDataSource
            {
                DataSource = commentDt.DefaultView,
                AllowPaging = true,
                PageSize = 25,
                CurrentPageIndex = CurrentPage
            };

        rptComments.DataSource = pgitems;
        rptComments.DataBind();

        if (pgitems.PageCount > 1)
        {

            var pages = new ArrayList();
            for (var i = 0; i < pgitems.PageCount; i++)
                pages.Add((i + 1).ToString());

            rptPages.DataSource = pages;
            rptPages.DataBind();
            rptPages.Visible = true;
        }
        else
        {
            rptPages.Visible = false;

        }
    }

The code-behind stuff doesn't work because of the second repeater I've set up, but the first repeater does work if I remove all that stuff. I thought it best to keep it in there so you can see my 'logic' (probably not very logical ;) ). Also worth noting that in the second data set I've hard-coded it to get the additional author details with id 81 - that was just to get the repeater working - obviously I need to substitute this with the id value from the Comment table, to get the authors for the current comment in the loop.

If anyone can help me get this second repeater working I'd be most grateful. It's over the edge of my learning curve at the moment!

Thanks folks!

score 3 · Accepted Answer · answered Jun 05 '12 at 14:20

3

There are several articles available online on Nested repeaters, including this one.

In essence, you need to first create your DataSet and define the relationships between the DataTables in code-behind, and then bind the child repeater to the child rows. The article linked to above has a full working example that's prety easy to modify.

if that article doesn't suit your needs, try one of these.

answered Jun 05 '12 at 14:20

David

68,722
16
125
165

Thanks. That first article is the one I've been trying to follow but I just can't get it working - it seems to use a bunch of new stuff like SQLDataAdapter which isn't in my application, and I don't understand it well enough to know what each part is doing and what the equivalent is in my example. I'll have a look at your second link though. – Briquette Jun 05 '12 at 14:25
1

The DataAdatper is used to fill a DataSet, which creates an in-memory version of your data. You're using an SqlDatareader, which allows you to loop through the records one at a time. They are two different approaches to fetching data. To do nested repeaters, the simplest way is to use DataAdapters. Hang on and I'll find you a quick article outlining the difference. it shouldn't be too difficult to switch. – David Jun 05 '12 at 14:31
Populating a DataSet from a DataAdapter: http://msdn.microsoft.com/en-us/library/bh8kx08z.aspx – David Jun 05 '12 at 14:34
Nice detailed article on dataAdapters: http://www.developerfusion.com/article/84366/adonet-dataadapter/ At any rate, using a dataAdapter is a fundamental .NEt skill, so it's important to learn about them. Good luck! – David Jun 05 '12 at 14:36
My database connection is made via a SQL helper method because this application is a plug-in for a content management system which allows abstraction of the data source so people can use MySQL or SQLCE etc. I don't know why, but defining a SQLDataAdapter feels wrong in the context of this application. The SQL Helper code is like this: `readonly ISqlHelper _sqlHelper = DataLayerHelper.CreateSqlHelper(GlobalSettings.DbDSN);` Is there any way to use this instead of a SQLDataAdapter? I'd like to keep as much of the original logic as possible rather than build new. – Briquette Jun 05 '12 at 14:55
1

I've never ran across code allowing you do do it. The only things I've seen that come close to what you're asking is to not use repeaters, and instead, build the HTML by hand using StringBuilders. As far as I know, for the true DataBinding to work in a nested scenario, you absolutely need to define the relations between the tables, which requires a DataSet. You CAN fill a DataSet with a DataReader by manually building the tables, looping through the result set of the reader, and adding rows to the tables, but that approach is cumbersome and not recommended. – David Jun 05 '12 at 14:57
Looking at your code, you are building the tables and populating the tables using the DataReader, so cumbersome and not recommended is a moot point. The code is doing it already. You should be able to simply define the DataRelations to get it to work. Focus on that portion of the code in teh first article. – David Jun 05 '12 at 15:06
Before I get too far into this, am I going to be able to keep the pagination and default view stuff currently set by `pgitems` in the code in my original post, or is that going to need to be rewritten? – Briquette Jun 05 '12 at 15:16
Adding a child repeater should not affect paging. – David Jun 05 '12 at 15:53
Accepted this as solution even though I can't get the SqlDataAdapter approach working - at least I know it's the way to do it now. Thanks for your help... – Briquette Jun 05 '12 at 16:05

Bind data repeater inside data repeater in ASP.NET

1 Answers1