How to increment a value within a database column by button click

Question

As part of the project that I'm working on, users can create a post and then other users are able to click on a "like" or "dislike" button.

The code below is the Post.cs class responsible for adding the tables to the database.

public class Post
{
    //The post ID
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int postId { get; set; }
    // Foreign key to customer

    public string Id { get; set; }
    public string Email { get; set; }
    public string postTitle { get; set; }
    public string postBody { get; set; }
    public string postDepartment { get; set; }
    public string postCategory { get; set; }
    public bool postAnonymous { get; set; }
    public int postLikes { get; set; }
    public int postDislikes { get; set; }
    public DateTime postDate { get; set; }
}

The following code is the back-end C# code that's linked to either buttons.

protected void btnLike_Click(object sender, EventArgs e)
{

}

protected void btnDislike_Click(object sender, EventArgs e)
{

}

I am trying to get the buttons to increase the integer value by +1 on the database per each click and the users should only be able to click on either one without being able to like // dislike more than once.

How would I go about trying to do this successfully using the asp.net webforms.

<------------------EDIT------------------------->

protected void btnLike_Click(object sender, EventArgs e)
{
    var postIDforLike = // add logic to get the id of the post to increment the likes
    using (var _dbContext = new ApplicationDbContext()) 
    {
        var addLikeSql = "update post set postLikes = postLikes + 1 where postID = @id";
        var paramID = new SqlParameter("@id", postIDforLike);
        _dbContext.Database.ExecuteSqlCommand(addLikeSql, paramID);
    }
}

<--------------------------EDIT2----------------------->

<asp:Button ID="btnLike" class="btn btn-primary" runat="server" Text="👍 Like" Width="99.99px" OnClick="btnLike_Click" />&nbsp <asp:Button ID="btnDislike" Width="99.99px" class="btn btn-primary" runat="server" Text="Dislike 👎" OnClick="btnDislike_Click"  />

        </div>
    <br />
         <%--------------------------------------
          Inserting Comment Information
          --------------------------------------%>
 <div class="col-md-12">
            <div class="panel panel-primary">
                  <div class="panel-heading">
    <h3 class="panel-title text-center">Add Comment</h3>
  </div>

  <div class="panel-body">


      <fieldset>
      <table class="nav-justified">
          <tr>
              <td class="modal-sm" style="width: 237px; height: 21px;">
      <label for="commentBody" class="col-lg-2 control-label">Comment:</label></td>
              <td style="width: 434px; height: 21px;">    
         <asp:TextBox Width="400px" style="resize:none;" class="form-control" ID="commentBody" runat="server" placeholder="Body" TextMode="MultiLine"></asp:TextBox>
              </td>
              <td style="height: 21px">    
                  <asp:RequiredFieldValidator controltovalidate="commentBody" ID="commentBodyValidator" runat="server" ErrorMessage="*Comment is required" ForeColor="Red"></asp:RequiredFieldValidator>    
              </td>
          </tr>
          </table>
      <br />
      <table class="nav-justified">
          <tr>
              <td style="height: 21px; width: 511px">    
      <label for="commentAnonymous" class="col-lg-2 control-label" style="left: 0px; top: 0px; width: 538px">Would you like this comment to be submitted anonymously?:</label></td>
              <td style="height: 21px; width: 104px">    
      <asp:RadioButtonList ID="commentAnonymous" runat="server" BorderStyle="None" CellPadding="0" CellSpacing="0">
        <asp:ListItem Value="1" Text="Yes">Yes</asp:ListItem>
        <asp:ListItem Value="0" Text="No">No</asp:ListItem>
    </asp:RadioButtonList>  
              </td>
              <td style="height: 21px"><asp:RequiredFieldValidator controltovalidate="commentAnonymous" ID="commentAnonymousValidator" runat="server" ErrorMessage="*Please select an option" ForeColor="Red"></asp:RequiredFieldValidator>    
              </td>
          </tr>
      </table>
      <br />
      <table class="nav-justified">
          <tr>
              <td class="modal-sm" style="width: 408px">&nbsp;</td>
              <td>
        <button type="reset" class="btn btn-default">Cancel</button>
          <asp:Button  class="btn btn-default" ID="commentSubmitBtn" runat="server" autopostback="false" onclick="AddComment" Text="Submit" />
              </td>
              <td>&nbsp;</td>
          </tr>
      </table>
          </fieldset>
                      <hr>

      <table class="display" id="commentsTable">
        <thead>
            <tr>
                <th>Comment</th>
                <th>User</th>
                <th>Date</th>

            </tr>
        </thead>
        <tbody>

        </tbody>
    </table>

Volkmar Rigo · Accepted Answer · 2019-03-31 16:50:19Z

0

I assume that you use Entity Framework (based on the attributes used in the post class).

The easiest way to do that is to load the entity, increment the value and then call SaveChanges. But this is very inefficient and also dangerous, if you don't use a locking mechanism.

What you are probably looking for is a

update post set postLikes = postLikes + 1 where postID = @id

command. This would already be more efficient, because you do not load the whole post every time before updating the Likes value. You can execute Sql Commands like this using Database.ExecuteSqlCommand of your context.

Another possible solution would be to change your DB Model by adding a Like and Dislike Table where you add a record for every like/dislike. To get the current count of likes, you have to count the number of records associated to the post. This adds overhead, but has the advantage that you don't create a bottleneck because the database has to lock your post record every time you want to update the like field.

edited Mar 31, 2019 at 16:50

answered Mar 31, 2019 at 16:43

Volkmar Rigo

1,16320 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

zadders Over a year ago

yes I am using Entity Framework, apologies for not stating this in my original question. I'd probably prefer to keep it within the posts table instead of making a new table since that'd make more sense to me. But I don't get whree I'd put the update post statement that you suggested? even though it does make sense to me

Volkmar Rigo Over a year ago

You can find an explanation how to execute an sql statement here: learnentityframeworkcore.com/raw-sql#database.executesqlcommand

zadders Over a year ago

I've looked through that example, there's just 1 line that I don't get and that's the " var name = new SqlParameter("@CategoryName", "Test"); " one. I have edited my question with the new code I added, could you check that it looks right?

Volkmar Rigo Over a year ago

I modified your edit to fix the line you had trouble with. The new SqlParameter is needed to set the value for the Parameter Value for the @id parameter in the Sql.

zadders Over a year ago

Perfect! That's fixed the validation problem and the rest of the code is all correct! :D The likes and dislikes are working properly :D Thank you so much for answering all my nooby questions and baring with me :)

|

Kaan Eroğlu · Accepted Answer · 2019-03-31 17:01:42Z

//You need to have a new table that handle user-post-like // UserLike Table which have UserId int PostId int Like or dislike so you dont need Likes and Dislikes attributes on entity! //Suppose that, this operation can be done by user logged in.

protected void btnLike_Click(object sender, EventArgs e)
        {
//you need to get postId while button clicked... 
            DBContext db = new DbContext();
            var user = Session["loggedUser"] as User ;
            var didUserLike = db.Post.Where(p=>p.PostId == postId).Select(x=>x.UserId == user.UserId).FirstOrDefault());
if(didUserLike.Count() > 0){

//Operation like enable button disable button!
}   //and if you want to do it for dislike..yes you can
        }

Sorry i don't have studio or code here i cant control this.But i think you understand structure. Have a good day.

Edited(Added):

and in view you dont need to show from attribute of your enetiyt because this can be calculated with query. This is not right for database design.

and you can do it like ;

  public int likeCountForPost(int id){
      var postLikes = db.PostLikes.Where(x=>x.PostId == id && x.PostLike==1).Count();
      return  postLikes;
}

You also can do it by changing just 1 to 2 for return count of dislikes.

Collectives™ on Stack Overflow

How to increment a value within a database column by button click

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related