Ado.net command.Paramaters.AddWithValue for update statement?

Question

model

public class Users
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public string DisplayName { get; set; }
    public string  Password { get; set; }
    public string Email { get; set; }
    public DateTime? LastLoginDate { get; set; }
    public string LastLoginIP { get; set; }
}

I want to update user display name only, in DB, like this

repository.Update(new User{ DisplayName = "display name" });

How can I create sql query? Should I create query dynamicaly? Or another way?

I use following code to update user.

public int Update(Users user)
{
    SqlCommand command = GetCommand("UPDATE Users SET UserName=@UserName, DisplayName=@DisplayName, Email=@Email, Password=@Password");

    command.Parameters.AddWithValue("@UserName", user.UserName, null);
    command.Parameters.AddWithValue("@DisplayName", user.DisplayName, null);
    command.Parameters.AddWithValue("@Email", user.Email, null);
    command.Parameters.AddWithValue("@LastLoginDate", user.LastLoginDate, null);
    command.Parameters.AddWithValue("@LastLoginIP", user.LastLoginIP, null);
    command.Parameters.AddWithValue("@Password", user.Password, null);

    return SafeExecuteNonQuery(command);
}

AddWithValueExtension

public static SqlParameter AddWithValue(this SqlParameterCollection target, string parameterName, object value, object nullValue)
{
    if (value == null)
    {
        return target.AddWithValue(parameterName, nullValue ?? DBNull.Value);
    }
    return target.AddWithValue(parameterName, value);
}

But above code update all fields by null values(user displayName except). I dont want to update fields if its values are null. I hope I can explain.

Thanks in advence...

Answer 1

Try this command instead

UPDATE Users SET UserName=ISNULL(@UserName, UserName), DisplayName=ISNULL(@DisplayName, DisplayName), Email=ISNULL(@Email, Email), Password=ISNULL(@Password, Password)

ISNULL(@UserName, UserName) returns the parameter's value, if it's not NULL, or the value from the database if the parameter is NULL.