Displaying partially hierarchical data with nested repeaters

I'm SO close, but so far. I've got two tables, one for publication details, the other for the categories adjacency list.

I'm trying to display the full dataset with nested repeaters (unless there's a better way) and I'm building my DataSet as shown:

string strSql = "SELECT category_id, parent_id, cat_name_en " +
                "FROM categories;" +
                "SELECT pub_id, title_en, category_id " +
                "FROM publications;";

SqlConnection conn = new SqlConnection(connString.ConnectionString);
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);

DataSet _ds = new DataSet();   
da.Fill(_ds);
_ds.DataSetName = "categories";
_ds.Tables[0].TableName = "category";
_ds.Tables[1].TableName = "publications";

_ds.Relations.Add("ParentChild", 
                  _ds.Tables["category"].Columns["category_id"],
                  _ds.Tables["category"].Columns["parent_id"], false);
_ds.Relations[0].Nested = true;

_ds.Relations.Add("CategoryMembers", 
                  _ds.Tables["category"].Columns["category_id"],
                  _ds.Tables["publications"].Columns["category_id"], false);
_ds.Relations[1].Nested = true;


categoryRepeater.DataSource = _ds.Tables["category"];
categoryRepeater.DataBind();

I'm displaying the data like this:

<asp:Repeater runat="server" ID="categoryRepeater" EnableViewState="false">
  <ItemTemplate>
    <%# DataBinder.Eval(Container.DataItem, "category_id") %>
    <%# DataBinder.Eval(Container.DataItem, "cat_name_en") %><br />

    <asp:Repeater runat="server" EnableViewState="false"
      DataSource='<%# GetChildRelation(Container.DataItem, "CategoryMembers") %>'>  
      <ItemTemplate>
        <p><%# DataBinder.Eval(Container.DataItem, "pub_id") %>&nbsp;
           <%# DataBinder.Eval(Container.DataItem, "title_en") %></p>
      </ItemTemplate>
    </asp:Repeater>

    <asp:Repeater runat="server" EnableViewState="false" 
      DataSource='<%# GetChildRelation(Container.DataItem, "ParentChild") %>'>
      <ItemTemplate>
        <%# DataBinder.Eval(Container.DataItem, "category_id") %>
        <%# DataBinder.Eval(Container.DataItem, "cat_name_en") %><br />  
        <asp:Repeater runat="server" EnableViewState="false"
          DataSource='<%# GetChildRela开发者_开发问答tion(Container.DataItem, "CategoryMembers") %>'>
          <ItemTemplate>
            <p><%# DataBinder.Eval(Container.DataItem, "pub_id") %>
               <%# DataBinder.Eval(Container.DataItem, "title_en") %></p> 
          </ItemTemplate>      
        </asp:Repeater>
      </ItemTemplate>    
    </asp:Repeater>
  </ItemTemplate>
</asp:Repeater>

I've nested the repeaters like that because some categories don't have children categories, but still have publications.

So I've got the Relations in place to get any Children categories, and then get any publications belonging to that category. I get the listing of categories with their children and any publications that belong to the category.

The problem is I can't figure out how to prevent some of the Children categories being treated as Parents. There are some categories that do not have parents, but still have publications. These categories will be displayed correctly under their parent and then AGAIN as their own Parent category

How do I configure the tables or relations so children categories aren't treated like parents?

---

First you'll need to create a DataView with the RowFilter of either "parent_id is null" or parent_id = '' depending on what the data is

Next bind that dataview instead of the table.

e.g.

DataView dv = new DataView(_ds.Tables["category"]);
dv.RowFilter = "parent_id is null";

categoryRepeater.DataSource = dv;

You shouldn't have to touch your GetChildRelation function as it will still get a DataRowView from Container.DataItem