What not to do

In Umbraco, you may be tempted to create a hit count, or visit count property in your page and add 1 to it every time someone visits the page. Don't do it. This is a bad idea. You would have to do a save and publish every time someone visits the page. It will be bad for performance, the umbraco.config file is re-written every time you do a save and publish.

A simple request of your page, should not cause your site to do a save and publish. You've heard of Distributed Denial of Service (DDoS) right?  Where someone decides to attack your site by sending thousands/millions of requests per second. Imagine how easy it would be to bring your site down if on each request it was doing a save and publish each time. Have I convinced you yet?

Ok, so it's easy to say what you shouldn't do, but how about I tell you what I would do. If you like it, you could implement it on your site. 

What I would do

Firstly, I would create a table in the database with a node_id and a views column

I would have the node_id as a unique integer, and the views column as a not null integer.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tbl_hit_counter](
[node_id] [int] NOT NULL,
[views] [int] NOT NULL,
CONSTRAINT [PK_tbl_hit_counter] PRIMARY KEY CLUSTERED
(
[node_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Next I would create a stored procedure to record the page view:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_record_view]
(
    @node_id INT
)
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS(SELECT 1 FROM tbl_hit_counter WHERE node_id = @node_id)     BEGIN
        UPDATE tbl_hit_counter SET views = views + 1
        WHERE node_id = @node_id
    END
    ELSE
    BEGIN
        INSERT INTO tbl_hit_counter(node_id, views) VALUES (@node_id, 1)
    END
END
GO

Then I would create a static method within your project somewhere that would call the stored procedure

using System.Data.SqlClient;
using System.Web.Configuration;
namespace CodeShare.Example
{
    public static class HitCounter
    {
        public static void RecordView(int nodeId)
        {
            using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["umbracoDbDSN"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("usp_record_view", conn))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("node_id", System.Data.SqlDbType.Int).Value = nodeId;
                   conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
 

Now in the top level view or only in the views for the page templates I care about, I would add the following code to check if a cookie called 'PageView_XXXX' exists or not. If the cookie doesn't exist, call the RecordView method, then create the cookie. This would stop multiple views being recorded for the same session. You may want to put this into a separate method to reduce duplication.

@{ 
    int nodeId = Umbraco.AssignedContentItem.Id;
    string cookieName = String.Format("PageView_{0}", nodeId);
    if (Session[cookieName] == null)
    {
        CodeShare.Example.RecordView(nodeId);
        Session[cookieName] = 1;
    }
}

To display the view count, lets create a stored procedure first to return the view count by node_id.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_get_view_count]
(
    @node_id INT
)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT views
    FROM tbl_hit_counter
    WHERE node_id = @node_id  
END
GO

Now let's create a method to retrieve the view count from the stored procedure when we pass it the node_id.

public static int GetViewCount(int nodeId)
{
    int viewCount = 0;
    using (SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["umbracoDbDSN"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("usp_get_view_count", conn))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add("node_id", System.Data.SqlDbType.Int).Value = nodeId;
            conn.Open();
            viewCount = (int)cmd.ExecuteScalar();
        }
    }
    return viewCount;
}

If you want to use this value in your view, just call it like this:

@CodeShare.Example.GetViewCount(Umbraco.AssignedContentItem.Id)

Future improvements

You could alter the stored procedure to return all results and store them in a Dictionary<int, int> object.

This object could be stored in the cache and you could reference it in the view to get the view count for the page.

Want to thank me?

If I've helped you out and you want to thank me, why not buy me a coffee?

Paul Seal

A .NET Web Developer from Derby (UK) who specialises in building Content Management System (CMS) websites using MVC with Umbraco as a framework. Paul is passionate about web development and programming as a whole. Apart from when he's with his wife and son, if he's not writing code, he's thinking about it or listening to a podcast about it.

Web: codeshare.co.uk

Comments

Share Top