How to create message board messages counter
In this article you will see, how to create counter used in transformation of e.g. repeater. It could be used in situations like this: There is a repeater on the page showing up documents which contain message board and you would like to show up amount of messages (comments) for each document.
Please see following instructions:
1) Create
custom function which will be called in the repeater's transformation. It can be defined like this:
...
using CMS.DataEngine;
using System.Data.SqlClient;
...
public static string GetBoardMessagesCount(object documentID)
{
//prepare connection
GeneralConnection gn = ConnectionHelper.GetConnection();
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString;
//declare variables
string dID = (string)documentID.ToString();
int bmCount = 0;
//set SQL command
string sql = "SELECT count(*) as commentCount FROM Board_Board JOIN Board_Message ON Board_Message.MessageBoardID = Board_Board.BoardID WHERE BoardDocumentID = " + dID + " AND MessageIsSpam = 0 AND MessageApproved = 1";
//execute query
DataSet ds = gn.ExecuteQuery(sql, null, CMS.IDataConnectionLibrary.QueryTypeEnum.SQLQuery, false);
//set counter
bmCount = (int)ds.Tables[0].Rows[0]["commentCount"];
//return value
return bmCount.ToString();
}
Please note the
MessageIsSpam and
MessageApproved restrictions in WHERE condition - they are here because of not all messages are meant to be shown (and therefore not counted) within the board's messages. It's better to count only those which are displayed.
2) As it was mentioned, function above will be called in the repeater's transformation this way:
<%# MyFunctions.GetBoardMessagesCount(Eval("DocumentID")) %>
It will put on appropriate document ID for each item in the repeater.
3) Finally, you should see number of messages (comments) which are posted to each document.
Note: Please keep in mind this works for documents with one message board only.
See also: Applies to: Kentico CMS 4.x