当用户是数据库时，asp.net存储数据库中得分的总和

I got the following sql query in my aspx page to update my database in phpmyadmin. My software is visual web developer 2010.

我在我的aspx页面中获得了以下sql查询,以便在phpmyadmin中更新我的数据库。我的软件是可视化Web开发人员2010。

  cmd = connection.CreateCommand();
            cmd.CommandText = "UPDATE (userscore) SET score = @score WHERE username = @username";
            cmd.Parameters.AddWithValue("@username", username);
            cmd.Parameters.AddWithValue("@score", userscore);
            var numRowsAffected = cmd.ExecuteNonQuery();
            if (numRowsAffected == 0)
            {
                cmd.CommandText = "INSERT INTO userscore (username, score)VALUES(@username,   @score)";
                cmd.ExecuteNonQuery();
            }

This query adds an username plus is score in the database, when the specific username isn't in the database yet. That's what I want. But when for example the username admin is in the datbase with a score of -1 (in my application you can vote +1 or -1) and he gets another vote of +1, I want to store the sum the values (in this case -1 plus +1 = 0)in the database as the new score, and delete the old value, which in this example is -1.

当特定用户名尚未在数据库中时,此查询会在数据库中添加用户名加分数。这就是我想要的。但是当例如用户名admin在数据库中得分为-1时(在我的应用程序中你可以投票+1或-1)并且他获得+1的另一次投票,我想存储值的总和(在此在数据库中将case -1加+1 = 0)作为新分数,并删除旧值,在此示例中为-1。

1 个解决方案

#1

I'd just wrap your "upsert" code into the stored procedure, and pass the userid and the score increment/decremnt.

我只是将你的“upsert”代码包装到存储过程中,然后传递userid和score increment / decremnt。

Your current code does not update the score, but simply overwrites it with whatever you pass into it

您当前的代码不会更新分数,而只是用您传入的代码覆盖它

create proc SetUserScore
@UserName nvarchar(100),
@ScoreChange int
AS
BEGIN
    IF EXISTS(SELECT 1 FROM userscore WHERE username = @UserName)
    BEGIN
      UPDATE userscore 
      SET score = score + @ScoreChange
      WHERE username = @UserName
    END
    ELSE
    BEGIN
      INSERT INTO userscore (username, score)VALUES(@username,   @score)
    END

END

#1