Updating the view count of a webpage in a database

Question

I have a database table called "Posts" which stores all the information regarding an article submission on a website. There is a column named "Views" which is a value that gets incremented each time that particular post gets viewed.

The process is this:

1. Get the record from the database
2. Increment the current by one
3. Save the changes to the database.

Pretty straightforward. My concern is that if multiple people click the link at the same time, the updates wont be accurate. How should I approach this? Should this only be done ina stored procedure?

    /// <summary>
    /// Updates the view count of a post.
    /// </summary>
    /// <param name="postId">The Id of the post to update</param>
    public bool UpdateViewCount(int postId)
    {
        Repository repository = new Repository();
        Post p = repository.Posts.Where(p => p.Id == postId).SingleOrDefault();
        if (p != null)
        {
            p.Views++;
        }
        repository.SubmitChanges(System.Data.Linq.ConflictMode.ContinueOnConflict);
    }

Answer 1

Do it in one go:

UPDATE table SET views=views+1 WHERE myId=12;

Answer 2

If your db context is called _db you can use this.

_db.ExecuteCommand("UPDATE posts SET views=views+1 WHERE id={0}", postId);

for further reading check out the Gu's post here.

http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx