Why do we need SqlCeCommand.Parameters.AddWithValue() to Insert a value?

Question

I have a C# WPF desktop application which uses SQL Compact 3.5 as its embedded database. In the insertion function it has

using (SqlCeCommand com = new SqlCeCommand(
    "INSERT INTO FooTable VALUES(@num)", con))
{
    com.Parameters.AddWithValue("@num", num);
    com.ExecuteNonQuery();
}

I don't get what the com.Parameters.AddWithValue() is about. I commented out this line of code and the insertion function run exactly the same. I thought ExecuteNonQuery carries out the insertion, so what is this Parameters.AddWithValue thing?

Answer 1

@num is a TSQL parameter. Without AddWithValue(@num, num) this is neither defined nor assigned a value. It simply will not work with the parameter omitted, and even if it did: where would it get your chosen value (num) from? The absolute best it could do would be to use null which was not your intent; more typically it would simply fail to execute (are you sure you aren't swallowing an exception somewhere?).

Note that concatenating the value into the string itself is not recommended; it would cause a SQL injection risk, and can reduce performance (plan re-use; not sure this applies to CE though - CE might very well not bother with cached plans).