CODEWITHSUNDEEP
c#asp.net
Sybren
Sybren

Reputation: 1079

Update or Insert a new value

I have the following SQL command in asp.net:

cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO userscore (username, score)VALUES(@username,   @score)";
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@score", userscore);
cmd.ExecuteNonQuery();

This command works but it stores two values in the my sql database every button click. It gets the score value from the textbox, but when an username + a score is in the database already, I want to update the value. Can someone help me with a query to get this done?

To clarify my question: I want to store the new score even when it's lower then the current score, and the username is unique in the table.

Upvotes: 2

Views: 199

Answers (4)

user2985029
user2985029

Reputation:

you could use a stored procedure that inserts the score if there is no score or updates the user's score if it's lower than the new score

code

cmd = connection.CreateCommand();
cmd.CommandText = "update_highscore";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", username);
cmd.Parameters.AddWithValue("@score", userscore);
cmd.ExecuteNonQuery();

execute this to create a stored procedure

CREATE PROCEDURE update_highscore
@username VARCHAR(30),
@userscore INT
AS
UPDATE userscore 
SET score = @score
WHERE username = @username

IF @@ROWCOUNT = 0
    INSERT userscore (username, score) VALUES (@username, @score)

Upvotes: 2

ChrisLively
ChrisLively

Reputation: 88044

If SQL 2008 or above:

MERGE INTO userscore as target
  USING(VALUES(@username, @score)) AS source(UserName, Score)
  ON (target.UserName = source.UserName)
  WHEN MATCHED THEN
    UPDATE SET Score = source.Score)
  WHEN NOT MATCHED THEN
    INSERT(UserName, Score) VALUES(source.UserName, source.Score)
;

The MERGE statement performs an update if a matching record exists or an insert if it doesn't. It's all one statement as well so you don't have to check for existence of the record first.

A copy/paste version is:

cmd.CommandText = "MERGE INTO userscore as target USING(VALUES(@username, @score)) AS Source(UserName, Score) ON (target.UserName = source.UserName) WHEN MATCHED THEN UPDATE SET Score = source.Score) WHEN NOT MATCHED THEN INSERT(UserName, Score) VALUES(source.UserName, source.Score);";

Upvotes: 2

Stephen Fischer
Stephen Fischer

Reputation: 2546

If the username is supposed to be unique for the table, then you can use an update query plus SqlCommand.ExecuteNonQuery (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx).

Something like:

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();
}

Upvotes: 1

Dai
Dai

Reputation: 155065

I assume you only want to store one highscore per username, rather than only one global highscore.

DECLARE @highscore int NULL
SELECT @highscore = MAX(score) FROM userscore WHERE username = @username

IF @highscore IS NULL BEGIN

    INSERT INTO userscore ( username, score ) VALUES ( @username, @score )

END ELSE IF @score > @highscore BEGIN

    UPDATE userscore SET score = @score WHERE username = @username

END

This T-SQL script requires two parameters, @username and @score. The @highscore variale is declared within the script.

Upvotes: 3

Related Questions