Hi, I have the following to display nested comments on my blog which currently uses two tables blogComments and blogNestedComments.

Everything works fine but I'd like to know if somehow I could use just one table to accomplish the same. It would help me on the backend managing the data.

codebehind:

    protected void Page_Load(object sender, EventArgs e)
    {
        GetComments();
    }

    #region get comments

    protected void GetComments()
    {
        // get required elements
        int article = int.Parse(Request.QueryString["article"]);

        // get data
        SqlDataAdapter cmd = new SqlDataAdapter("SELECT blogComments.commenterName, blogComments.commenterEmail, blogComments.commenterWebSite, blogComments.commenterComment, blogComments.commentDate, blogComments.blogCommentId FROM blogComments INNER JOIN blog ON blogComments.blogId = blog.blogId WHERE(blogComments.blogId = " + article + ") AND (blogComments.commentApproved = 1) AND (blog.blogPublishNow = 1) AND (blog.blogPublishDate <= GETDATE());SELECT * FROM [blogNestedComments] WHERE blogId =" + article + "", new SqlConnection(ConfigurationManager.ConnectionStrings["dbMyCMSConnectionString"].ConnectionString));

        // add tables to dataset
        DataSet ds = new DataSet();
        cmd.Fill(ds);
        ds.Relations.Add(new DataRelation("nestThem", ds.Tables[0].Columns["blogCommentId"], ds.Tables[1].Columns["blogCommentId"]));

        // repeater1 datasource
        Repeater1.DataSource = ds;
        Repeater1.DataBind();
    }

    #endregion

    #region Databind repeaters

    protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        DataRowView dv = e.Item.DataItem as DataRowView;
        if (dv != null)
        {
            Repeater Repeater2 = e.Item.FindControl("Repeater2") as Repeater;
            if (Repeater2 != null)
            {
                Repeater2.DataSource = dv.CreateChildView("nestThem");
                Repeater2.DataBind();
            }
        }
    }

    #endregion

aspx:

<asp:Repeater ID="Repeater1" runat="server" OnItemDataBound="Repeater1_ItemDataBound">
    <ItemTemplate>
        <div class="commentwrap">
            <div class="commentsTitleArea">
                <span class="commentCounter"><%# Convert.ToInt32(Container.ItemIndex) + 1%>. </span>  <img src="../images/decoy-icon-16px.png" alt="Comment by..." title="Comment by..." class="blogCommentIcon" /><a href='<%# Eval("commenterWebsite")%>' target="_blank" rel="nofollow"><%# " " + Eval("commenterName")%></a>   <%# Eval("commentDate")%>
            </div>
            <div class="commentText">
                <%# Eval("commenterComment") %>
                <div><span class="btnCommentReply"><a href='<%# "article.aspx?article=" + Request.QueryString["article"] + "&cid=" + Eval("blogCommentId") + "#comment" %>'>REPLY</a></span></div>
            </div>
            <asp:Repeater ID="Repeater2" runat="server">
                <ItemTemplate>
                    <div class="commentwrap" style="margin:0px 20px 10px 50px;">
                        <div class="commentsTitleArea">
                            <span class="commentCounter"><%# Convert.ToInt32(Container.ItemIndex) + 1%>. </span>  <img src="../images/decoy-icon-16px.png" alt="Comment by..." title="Comment by..." class="blogCommentIcon" /><a href='<%# Eval("commenterWebsite")%>' target="_blank" rel="nofollow"><%# " " + Eval("commenterName")%></a>   <%# Eval("commentDate")%>
                        </div>
                        <div class="commentText">
                            <%# Eval("commenterComment") %>
                        </div>
                    </div>
                </ItemTemplate>
            </asp:Repeater>
        </div>
    </ItemTemplate>
</asp:Repeater>

Hey,

You shouldn't build sql strings on the fly like this:

// get data
        SqlDataAdapter cmd = new SqlDataAdapter("SELECT blogComments.commenterName, blogComments.commenterEmail, blogComments.commenterWebSite, blogComments.commenterComment, blogComments.commentDate, blogComments.blogCommentId FROM blogComments INNER JOIN blog ON blogComments.blogId = blog.blogId WHERE(blogComments.blogId = " + article + ") AND (blogComments.commentApproved = 1) AND (blog.blogPublishNow = 1) AND (blog.blogPublishDate <= GETDATE());SELECT * FROM [blogNestedComments] WHERE blogId =" + article + "", new SqlConnection(ConfigurationManager.ConnectionStrings["dbMyCMSConnectionString"].ConnectionString));

You should use sqlparameters instead:

• http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET
  
• http://msdn.microsoft.com/en-us/library/ms998271.aspx#paght000002_step3
  

I have never seen the DataRelation class before but I don't use ado.net for my data access, I have always preferred orm's such as linq2sql.

It seems like you know how to use them though so maybe I can just give you the theory and you can do the rest?

The way that multithreaded conversations are normally stored in databases is to add an extra column to the main comments table which is the parent comment id. The column should be nullable because if it is the first comment then it wont have a thread.

Using this you can select all null parent comments and they are your top level comments, select all the other comments and join it on parentid to commentid.

rtpHarry, Thank you! That code was just a quick draft. I always do that with everything untill know everything works. It is easier to mold things into place.

Currently it looks like this:

    protected void Page_Load(object sender, EventArgs e)
    {
        GetComments();
    }

    #region get comments

    protected void GetComments()
    {
        // get required elements
        int article = int.Parse(Request.QueryString["article"]);

        // connect to database
        SqlDataAdapter adapter = new SqlDataAdapter();
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbMyCMSConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand("sp_blog_Comments", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("@article", SqlDbType.Int).Value = article;
        adapter.SelectCommand = cmd;

        // add tables to dataset
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        ds.Relations.Add(new DataRelation("nestThem", ds.Tables[0].Columns["blogCommentId"], ds.Tables[1].Columns["blogCommentId"]));

        // repeater1 datasource
        Repeater1.DataSource = ds;
        Repeater1.DataBind();
    }

    #endregion

    #region Databind repeaters

    protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        DataRowView dv = e.Item.DataItem as DataRowView;
        if (dv != null)
        {
            Repeater Repeater2 = e.Item.FindControl("Repeater2") as Repeater;
            if (Repeater2 != null)
            {
                Repeater2.DataSource = dv.CreateChildView("nestThem");
                Repeater2.DataBind();
            }
        }
    }

    #endregion

rtpHarry, Thank you.

So the new column (parentId) stores the primarykey ID as well which in this case is the commentId (each comment's unique ID) and it is only inserted if a comment is a reply to a comment. Am I right? I think I understand. I will give this a shot.

Would be great if it worked...

Thank you!

rtpHarry, I managed to do what you have suggested. Added a new nullable column into the comments table that recives the comment ID and I didn't even have to change my existing code. It works fine. The only one minor issue is that it is not recursive - meaning that it only goes one level deep.

Thank you!