Reset Article View Count and Rating

retroviz · July 5th, 2008, 07:28 AM

Just thought I would share this.

When developing with the CMS I found I was viewing articles many times and therefore incrementing article view count. Also I may have rated articles during tested. I created the following method to simply reset these values.

Add a new stored procedure:

Code:

CREATE PROCEDURE dbo.rw_Articles_ResetViewCountAndRating
/**********************************************************************
 * SP Name:  Articles_ResetViewCountAndRatings
 * Author:   Retro
 * Date:     05/07/2008 
 * DB(s):    ASPNETDB
 *
 * Purpose:
 * Resets an Articles ViewCount and Rating/Votes
 *
 * Modification Log: Change
 ***********************************************************************
 * 05/07/2008, Ben Foster:
 * Created Stored Procedure
 *
 */
 (
     @ArticleID            int
 )
 
AS

    UPDATE rw_Articles
    SET ViewCount = 0, Votes = 0, TotalRating = 0
    WHERE (ArticleID = @ArticleID)

In SqlArticlesProvider.vb add the following method:

Code:

        ' Resets the ViewCount, Vote and TotalRating for the specified article
        Public Overrides Function ResetViewCountAndRating(ByVal articleID As Integer) As Boolean
            Using cn As New SqlConnection(Me.ConnectionString)
                Dim cmd As New SqlCommand("rw_Articles_ResetViewCountAndRating", cn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@ArticleID", SqlDbType.Int).Value = articleID
                cn.Open()
                Dim ret As Integer = ExecuteNonQuery(cmd)
                Return (ret = 1)
            End Using
        End Function

In ArticlesProvider.vb add the following function:

Code:

        Public MustOverride Function ResetViewCountAndRating( _
                ByVal articleID As Integer) As Boolean

In BLL Article.vb add following function:

Code:

        ' Resets an article's View Count and Rating
        Public Shared Function ResetViewCountAndRating(ByVal id As Integer) As Boolean
            Return SiteProvider.Articles.ResetViewCountAndRating(id)
        End Function

Finally on ShowArticle.aspx I add:

Code:

                <asp:LinkButton runat="server" ID="btnResetViewCountAndRating" CausesValidation="false"
                    Text="Reset View Count/Rating" Visible="false"/>

On Page_Load:

Code:

                btnResetViewCountAndRating.Visible = Me.UserCanEdit

and then click event for linkbutton:

Code:

        Protected Sub btnResetViewCountAndRating_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnResetViewCountAndRating.Click
            Article.ResetViewCountAndRating(_articleID)
        End Sub

Thought that may be of some use to some people as it will give better indication of how many views are from other people.

Shouldn't be hard to change to C#

Only thing this won't do is clear the user's rating cookie. As I only really needed this method when first going live it does not matter too much. However, if visitors rate your article and then you run this method, it will show the article as not rated but, when they view the article, their rating will be displayed.

To get by this you could extend the reset method to add a date when it was run. Then check date cookie was created. If before reset date then clear the cookie.

Thanks,

Lee Dumond · July 18th, 2008, 12:09 PM

The problem with this is that, if there are already non-zero values for Rating and ViewCount (say, if the article has been posted a while), you probably don't want to go resetting them.

I think a better solution might be to restrict Rating and ViewCount incrementing by role right up front.

retroviz · July 20th, 2008, 10:43 AM

Hi Lee,

Think you're probably right. The above only really works when first deploying the site (where view count's/ratings may have been increased/added during testing), which is what I needed it for. As a solution for the live site, retricting by role (or even preventing view counts/ratings by the person who created the article) seems like a good idea.

Thanks,