AddWithValue-method (C#) does nothing

Question

I'm currently writing a class to handle all database-activity in my application, and so I've come to the method that performs UPDATE-queries.

As of now I'm returning and displaying the content of the commandtext to check it, and it seems fine:

UPDATE news SET title = @title, newsContent = @newsContent, excerpt = @excerpt, date = @date, urlTitle = @urlTitle, isPublished = @isPublished WHERE (id = @id);

So, fine in other words. The next step is to replace all @values with actual values - and this is where i hit problems. Nothing happens. The AddWithValue-method just doesn't seem to do anything, and i find no reason for this.

public string updateQuery(string table, string[] fields, object[] values, string conditionField, object conditionValue)
{
    try
    {
        StringBuilder cmd = new StringBuilder("UPDATE " + table + " SET ");

        int i = 1;
        foreach (string s in fields)
        {
            if (i == fields.Length)
            {
                cmd.Append(s + " = @" + s + " ");
            }
            else
            {
                cmd.Append(s + " = @" + s + ", ");
            }

            i++;
        }

        cmd.Append("WHERE (" + conditionField + " = @" + conditionField + ");");
        command.CommandText = cmd.ToString();

        int j = 0;
        foreach (object o in values)
        {
            command.Parameters.AddWithValue("@" + fields[j], o);
            j++;
        }
        command.Parameters.AddWithValue("@" + conditionField, conditionValue);

        command.ExecuteNonQuery();
        connection.Close();

        return command.CommandText;
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Anyone?

Answer 1

Is it possible thatthe arg-value is null? If so the param won't be added. For nulls, you need to use DBNull.Value; for example (not the only way to do it):

cmd.Parameters.AddWithValue(argName, (object)argValue ?? DBNull.Value);

Answer 2

What do you mean by "it doesn't do anything"? Do you mean that the parameters don't have values when the command is executed? Or that the command text stays the same?

If it's the latter then this is expected. The command doesn't change the query text, it sends the query text as is, along with the parameter values, to the server.

Answer 3

Potentially the value of ID you are supplying doesn't match an entry in the table you are updating.

BTW drop the try..catch it isn't serving any purpose.

Answer 4

If you are expecting the string to be updated, you will be disappointed. A big feature of using a parameterized query is that values are never substituted directly into the command string, and thus you are protected from that kind of security vulnerability. Instead, the data is transmitted to the server separately.

Let me suggest this new function signature:

public string updateQuery(string table, IEnumerable<KeyValuePair<string, object>> values, KeyValuePair<string, object> condition)

This will create a strong association between your parameter names and the values, and also give you the flexibility to use constructs other than an array (though an array would certainly also be accepted). Then the code would look like this:

public string updateQuery(string table, IEnumerable<KeyValuePair<string, object>> values, KeyValuePair<string, object> condition)
{
    using (StringBuilder cmd = new StringBuilder("UPDATE [" + table + "]\nSET "))
    {
        string delimiter = "";
        foreach (KeyValuePair<string, object> item in values)
        {
           cmd.AppendFormat("{0}[{1}]=@{1}", delimiter, item.Key);
           delimiter = ",\n";
        }
        cmd.AppendFormat("\nWHERE [{0}]= @{0};", condition.Key);

        command.CommandText = cmd.ToString();
    }

    foreach (KeyValuePair<string, object> item in values)
    {
        command.Parameters.AddWithValue("@" + item.Key, (object)item.Value ?? DBNull.Value);
    }
    command.Parameters.AddWithValue("@" + condition.Key, condition.Value);

    // you didn't show where the connection was created or opened
    command.ExecuteNonQuery();    
    connection.Close();

    return command.CommandText;
}

Even better if you can use something that would create strongly-typed database parameters (like IEnumerable<SqlParameter>, for example).

Answer 5

I'll just post the rest of the code involved as well, i guess. First the method that passes on the values:

public void updatePost(int postId)
{
    DbConnection d = new DbConnection();

    string[] fields = {
                          "title",
                          "newsContent",
                          "excerpt",
                          "date",
                          "urlTitle",
                          "isPublished" 
                      };

    object[] values = {
                          this.title,
                          this.content,
                          this.excerpt,
                          this.date,
                          this.urlTitle,
                          this.isPublished,
                      };

    d.updateQuery("news",fields,values,"id",postId);
}

Constructor method that connects to the database:

public DbConnection()
{        
    connection = new SqlConnection("Data Source=****; User Id=****; Password=*****;");
    connection.Open();
    command = connection.CreateCommand();
}

I've yet to find out the problem, but I do really appreciate all help i get anyways. :-)

Answer 6

Using my other code, your new updatePost method would look like this.

using DBParam = KeyValuePair<string, object>; // reduce need to keep re-typing this

public void updatePost(int postId)
{   
    List<DBParam> values = new List<DBParam>();

    values.Add(new DBParam("title", this.title));
    values.Add(new DBParam("newsContent", this.content));
    values.Add(new DBParam("excerpt", this.excerpt));
    values.Add(new DBParam("date", this.date));
    values.Add(new DBParam("urlTitle", this.urlTitle));
    values.Add(new DBParam("isPublished", this.isPublished));

    new DbConnection().updateQuery("news", values, new DBParam("id", postid));
}

Answer 7

Thanks for the suggestions Joel Coehoorn, i will look into them.

Still, i want to get my stuff to work :p

Just noticed that the date-field actually have been updating itself all the time. None of the other fields, though. This just makes less sense the more i look at it. Maybe its better to try in the morning than at midnight.

EDIT: Found out what the problem was, and it was far simpler than i expected. I'd forgotten to check if the page was a postback, so every time i updated the database, the fields were filled with data FROM the database before the submit-method was called. So, the update-method worked all along. blush