JumpyForum: Inspired by Code Project Forum / Discussion / Message board

• Download source files - 610 Kb

Online demo - Jumpy Forum

Introduction

This article describes a discussion board inspired by and similar to Code Project Forums. I am sure, you all will agree that the discussion board at Code Project is one of the best out there. I tried searching for similar projects in CP and everywhere but all the examples given were in classic ASP / PHP and were highly complicated. My effort is to leverage ASP.NET, JavaScript and SQL Server to create a very simple and easy to understand Forum with the same functionality.

Before you get carried away, I just want to give a heads up on the strength of JumpyForum, this is a very simple (beginners level you can say) version and you might have to consider a lot more to actually have a forum with the CP like quality, scalability and durability. JumpyForum will only give you a start which is easy and workable and after that, the horizon is yours to explore.

The target was to create a SIMPLE AND EXTENDABLE Discussion board/Forum system like Code Project or better:

1. Usability: Users can add a comment of types/news/general/jokes and questions. Other users can reply in the form of general/news/questions/joke or reply
2. Rich Display: The forum display should maintain the hierarchy and the date/time after each comment
3. Reusable: The whole functionality of the forum should be easily plugged into any table for reusability
4. Consistent: If a message is deleted all the subsequent children should also be deleted hierarchically
5. Extensible: Forum should be easily extensible for different articles or different forums
6. Performance: The display of comments should not take a lot of time

_{* Disclaimer: It does look like Code Project Forum, but only claims that this is one of the ways of doing it.}

In Action

To hold your interest, here is how it will look, once completed. Check the demo here.

Assumptions

Those were the initial thoughts, but to make it a general, reusable and extensible module, I made some assumptions:

• We have a table in the SQL server CP_Forum_Comments (see screenshot below)
• The Table will have the following fields as shown including ArticleID (Foreign Key for the Article Table). The idea is for every article the forum will be different. Article ID can be changed to ForumId or anything you want. You can completely extend it as per your requirements
• Other Fields are pretty much self explanatory. ParentID is the id for the parent of the comment in the treeview (comment for which the reply is made)
• Indent Field is the key field which attributes to the indentation of the messages in the JumpyForum

Comments table structure CP_FORUMS_COMMENT
(Create Table SQL query attached in the source)

Actions

These are the actions which make the whole module:

• Data access
• The forum display and user interface
• The hierarchical stored procedure for the forum (crux)
• Add comment
• Add reply
• Delete comments and delete hierarchically (this feature is not present right now in CodeProject forum, that's why we see sometimes orphan messages if the parent is deleted)

Data access class

There is a standard data access class, clsDataAccess.cs, which handles all the data related actions.

Code

The main function here is getForumData which calls the stored procedure, I have kept here only the names of the other functions, just to give you a glimpse of the data access methods.

 using
       public class clsDataAccess { public
       clsDataAccess()

       { } SqlConnection mycon = new
       SqlConnection( ConfigurationSettings.AppSettings["ConnectionString"]);
        //Opens database connection in SQL SERVER
       public bool openConnection()
       public void closeConnection()

       public SqlDataReader getForumData(int ArticleId)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType= CommandType.StoredProcedure;
            sqlCommand.CommandText = "ShowHierarchyForum";
            SqlParameter newSqlParam = new SqlParameter();
            newSqlParam.ParameterName = "@ArticleId";
            newSqlParam.SqlDbType = SqlDbType.Int ;
            newSqlParam.Direction = ParameterDirection.Input;
            newSqlParam.Value = ArticleId;
            sqlCommand.Parameters.Add(newSqlParam);

            SqlParameter newSqlParam2 = new SqlParameter();
            newSqlParam2.ParameterName = "@Root";
            newSqlParam2.SqlDbType = SqlDbType.Int ;
            newSqlParam2.Direction = ParameterDirection.Input;
            newSqlParam2.Value = 0;
            sqlCommand.Parameters.Add(newSqlParam2);
            sqlCommand.Connection=mycon;

            SqlDataReader myr = sqlCommand.ExecuteReader
                    (CommandBehavior.CloseConnection);
            return myr;
        }
    }
}

For display purposes, we need to have the following images. I have taken them from CodeProject although these are easily available on the Internet.

The Forum Display and user interface

First let's assume we are getting all the records in the exact sequence we need (handled by the nested hierarchical stored procedure) and display it on the forum page. Consider dynamic display and Jumping thing of the forum.

What we are doing here is displaying all the records related to the article and then hiding all of them except their titles. When the user clicks on a message, that particular message will be displayed, others will be hidden. A simple JavaScript function can achieve this with on/off state. Here is the magical JavaScript for this:

function OnOffPost(e)
{
   if ( !e ) e = window.event;
   var target = e.target ? e.target : e.srcElement;

   while ( target && target.id != 'LinkTrigger' )
      target = target.parentNode;
   if ( !target || target.id != 'LinkTrigger' )
      return;

   if (Selected)
   {
      var body = document.getElementById(Selected + "ON");
      if (body)
         body.style.display = 'none';
      var head = document.getElementById(Selected + "OFF");
      if (head)
         head.bgColor = '#EDF8F4';
   }

   if (Selected == target.name) // just collapse
      Selected="";
   else
   {
      Selected = target.name;
      var body = document.getElementById(Selected + "ON");
      if (body)
      {
         if (body.style.display=='none')
            body.style.display='';
         else
            body.style.display = 'none';
      }
      var head = document.getElementById(Selected + "OFF");
      if (head)
         head.bgColor = '#B7DFD5';

      if ( body && head && body.style.display != 'none' )
      {
         document.body.scrollTop = FindPosition(head, "Top") - 
                `    document.body.clientHeight/10;
         OpenMessage(target.name, true);
      }
   }

   if ( e.preventDefault )
      e.preventDefault();
   else
      e.returnValue = false;
   return false;
} 

Some more JavaScript functions to open the message and find the position of the message:

function OpenMessage(msgID, bShowTop) {
   var msgHeader = document.getElementById(msgID + "OFF");
   var msgBody = document.getElementById(msgID + "ON");

   var MyBody = document.body;
   var top = FindPosition(msgHeader, 'Top');
   var bottom = FindPosition(msgBody, 'Top') + msgBody.offsetHeight;

   if ( MyBody.scrollTop > top && !bShowTop)
      MyBody.scrollTop = top - document.body.clientHeight/10;
   if ( MyBody.scrollTop+MyBody.clientHeight < bottom )
      MyBody.scrollTop = bottom-MyBody.clientHeight;
   if ( MyBody.scrollTop > top && bShowTop)
      MyBody.scrollTop = top - document.body.clientHeight/10;
}

function FindPosition(i,which)
{
   iPos = 0
   while (i!=null)
   {
      iPos += i["offset" + which];
      i = i.offsetParent;
   }
   return iPos
}

JavaScript On/Off functions are as old as JavaScript itself, not a lot of explanation is needed. You just need to pass the id of the element as the parameter on click event, which calls the method to change the display of that id. It is included in the source.

The Data to display in the user interface

To display all the records from the result of the stored procedure in the page, we need two things: a reader.read loop and a stringbuilder to create the whole page with all the messages. Here is how we deal with the time, message type and the indent, rest is just appended to the string builder with the data from the reader.

while (myReader.Read())
{
DateTime dt1 = DateTime.Now;
DateTime dt2 = Convert.ToDateTime(myReader["DateAdded"].ToString());
TimeSpan ts = dt1.Subtract(dt2);
string mytimeago = "";

if (Convert.ToInt32(ts.TotalDays) !=0) mytimeago = "" + 
        Math.Abs(Convert.ToInt32(ts.TotalDays))+ " Days  ago";
else {
     if            // dealing with the time 
    ((Convert.ToInt32(ts.TotalMinutes) < 5)&&
    (Convert.ToInt32(ts.TotalHours)==0))
    {  mytimeago="Just   Posted"; }
        else
        if ((Convert.ToInt32(ts.TotalMinutes) >  5)&&
        (Convert.ToInt32(ts.TotalHours)==0))
            {
            mytimeago  =  Convert.ToInt32(ts.TotalMinutes) % 60 + " Mins ago";
            }
            else if(Convert.ToInt32(ts.TotalHours)!=0)
            {
            mytimeago  = "" + Convert.ToInt32(ts.TotalHours) + "
            Hours " + Convert.ToInt32(ts.TotalMinutes) % 60 + " Mins ago";
            }
            else
            {
            mytimeago  =  Convert.ToInt32(ts.TotalMinutes) % 60 + " Mins ago";
            }


    }
    // Indentation of the image and the new image.gif
    string newimg ="";
    if (String.Compare(mytimeago,"Just Posted")==0)
    newimg = "<'JumpyForum/new.gif' border=0>";
    int myindent = 4;
    if (Convert.ToInt32(myReader["Indent"])<=4)
        myindent = 16 * Convert.ToInt32(myReader["Indent"]);
    else if (Convert.ToInt32(myReader["Indent"])<=8)
        myindent = 15 * Convert.ToInt32(myReader["Indent"]) ;
    else if (Convert.ToInt32(myReader["Indent"])<=16)
        myindent = 14 * Convert.ToInt32(myReader["Indent"]) ;
    else if (Convert.ToInt32(myReader["Indent"])<=20)
        myindent = Convert.ToInt32(13.5 * 
                Convert.ToDouble(myReader["Indent"]));
    else if (Convert.ToInt32(myReader["Indent"])<=24)
        myindent = 13 * Convert.ToInt32(myReader["Indent"]);
    else if (Convert.ToInt32(myReader["Indent"])<=28)
        myindent = Convert.ToInt32(12.7 * 
                Convert.ToDouble(myReader["Indent"]));
    else if (Convert.ToInt32(myReader["Indent"])<=32)
        myindent =  Convert.ToInt32(12.4 * 
                Convert.ToDouble(myReader["Indent"]));


     // Message Type           
    if (Convert.ToInt32(myReader["CommentType"].ToString())==1)
        sb.Append("<'JumpyForum/general.gif' align=absMiddle> </TD>");
    if (Convert.ToInt32(myReader["CommentType"].ToString())==2)
        sb.Append("<'JumpyForum/info.gif' align=absMiddle> </TD>");
    if (Convert.ToInt32(myReader["CommentType"].ToString())==3)
        sb.Append("<'JumpyForum/answer.gif' align=absMiddle> </TD>");
    if (Convert.ToInt32(myReader["CommentType"].ToString())==4)
        sb.Append("<'JumpyForum/question.gif' align=absMiddle> </TD>");
    if (Convert.ToInt32(myReader["CommentType"].ToString())==5)
        sb.Append("<'JumpyForum/game.gif' align=absMiddle> </TD>");
}

Now let's jump to the main part of the forum, i.e. displaying the hierarchical data.

The hierarchical stored procedure for the forum

Here is the nested hierarchical stored procedure which accomplishes this. I bet a nested hierarchical procedure can be quite haunting, but it pays to know the details. We pass two parameters

1. the Articleid to get all the messages for that article and
2. the Rootid the primarykey of the message to get all the children of that message.

A "Word of Caution" here, nested hierarchical stored procedure will not go beyond 32 sub levels. What we do here is create a temp table in a nested loop and then throw out the record of the temp table at the end. The stored procedure is attached with the source.

CREATE                          PROC dbo.ShowHierarchyForum
(
    @Root int,
    @ArticleId int
)
AS
BEGIN

if not exists (select name from [tempdb].[dbo].[sysobjects] 
                where name like '#YourLocalTempTable%')
create table #YourLocalTempTable (Id int, ParentId int,ArticleId int, 
                            Title nVarchar(250),
              username nvarchar(50),UserEmail nvarchar(50), 
                    Description nvarchar(2000),Indent int,
              DateAdded datetime,UserProfile nvarchar(100), 
                    CommentType tinyint)


    SET NOCOUNT ON
    DECLARE @CID int, @PID int, @Title varchar(250)

    insert into #YourLocalTempTable SELECT CP_FORUM_Comments.Id , 
                            ParentId ,ArticleId ,
                Title,username ,UserEmail ,Description ,Indent ,
                    DateAdded ,UserProfile, CommentType
                from CP_FORUM_Comments WHERE ID = @Root and ArticleId = 
                                @ArticleId

    SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments  WHERE ParentID = @Root)

    WHILE @CID IS NOT NULL
    BEGIN
        EXEC dbo.ShowHierarchyForum @CID, @ArticleId
        SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments 
                    WHERE ParentID = @Root

        AND ID < @CID and ArticleId = @ArticleId)


    END
END

if @@NESTLEVEL =1
select * from #YourLocalTempTable
GO

Add Comment

This is as simple as any Add record can be. Parentid = 0 when you add a new comment. To give it a touch of class, I have included the HTMLAREA control. More details about the brilliant control here by Fraser Cain.

SqlConnection myC =new SqlConnection();
myC.ConnectionString=ConfigurationSettings.AppSettings["ConnectionString"];
string sqlQuery="INSERT into " + 
ConfigurationSettings.AppSettings["CommentTable"] +
"(ParentId,ArticleId,Title,UserName,UserEmail,Description,Indent,UserProfile)
VALUES ('" +mParentId + "','" + mArticleId +  "','" + mTitle +  "','" + 
mUserName +  "','" +
mUserEmail +  "','" + mDescription + "','" + mIndent + "','" +
"http://www.codeproject.com/script/profile/whos_who.asp?id=81898" + "')";
myC.Open();
SqlCommand myCommand=new SqlCommand();
myCommand.CommandText=sqlQuery;
myCommand.Connection=myC;
int i=myCommand.ExecuteNonQuery();
myC.Close();
lblStatus.ForeColor = Color.Green ;
lblStatus.Text ="Status: Success";
Response.Redirect("Forum.aspx?id=" + articleid );  

Add Reply

Only one thing needs to be mentioned here, parentid and indent are taken from the parent message and updated accordingly.

Delete comments

The same hierarchical stored procedure is modified to hierarchically delete all the child messages in the Forum when the root message is deleted. DeleteHierarchyForum

CREATE  PROC dbo.DeleteHierarchyForum
(
    @Root int,
    @ArticleId int
)
AS
BEGIN

if not exists (select name from [tempdb].[dbo].[sysobjects]
            where name like '#YourLocalTempTable%')
create table #YourLocalTempTable (Id int, ParentId int,ArticleId int,
    Title nVarchar(250),
    username nvarchar(50),UserEmail nvarchar(50),Description nvarchar(2000),
    Indent int,DateAdded datetime,UserProfile nvarchar(100))

    SET NOCOUNT ON
    DECLARE @CID int, @PID int, @Title varchar(250)
    insert into #YourLocalTempTable SELECT CP_FORUM_Comments.Id ,
    ParentId ,ArticleId ,
    Title,username ,UserEmail ,Description ,Indent ,DateAdded ,UserProfile
    from CP_FORUM_Comments WHERE ID = @Root and ArticleId = @ArticleId

    SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments  WHERE ParentID = @Root)

    WHILE @CID IS NOT NULL
    BEGIN
        EXEC dbo.DeleteHierarchyForum @CID, @ArticleId
        SET @CID = (SELECT MAX(ID) FROM CP_FORUM_Comments
        WHERE ParentID = @Root AND ID < @CID and ArticleId = @ArticleId)
    END
END

if @@NESTLEVEL =1
Delete from CP_FORUM_Comments where CP_FORUM_Comments.Id in 
                (select ID from #YourLocalTempTable)

GO

Smile, we are done.

Jumpy Forum in Action: Multiple records

Article History

• Dec 15, 2006: First published
• Dec 18, 2006: Updates on the content
• Jan 12, 2007: Supports paging and Last Visit
• Jan 17, 2007: W3C Standard validation as per request from users
  Valid HTML 4.01 Transitional
• Jan 19, 2007: Fixed a JavaScript bug

And thanks

For coming so far! I hope you find this useful, and give me a vote/comment if you do and take care.