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:
public static string GetBoardMessagesCount(object documentID)
myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString;
GeneralConnection gn = ConnectionHelper.GetConnection();
SqlConnection myConnection = new SqlConnection();
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";
DataSet ds = gn.ExecuteQuery(sql, null, CMS.IDataConnectionLibrary.QueryTypeEnum.SQLQuery, false);
bmCount = (int)ds.Tables.Rows["commentCount"];
Please note the MessageIsSpam
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.
Please keep in mind this works for documents with one message board only.
See also: Applies to:
Kentico CMS 4.x