Parameters in the Query

Question

When mounting a Query using Addwithvalue. Even if right, the return is not as expected.

In the example below the return is Zero

var query = "SELECT nome,usuario,email,administrador FROM GSCUsuarios WHERE @parametroWhere = @parametroCondicao");

dataAdapter.SelectCommand.Parameters.AddWithValue("parametroWhere", parametroWhere);
dataAdapter.SelectCommand.Parameters.AddWithValue("parametroCondicao", parametroCondicao);

I decided to ask in the American stackoverflow and it was suggested to change the query to the following format

var query = string.Format("SELECT nome,usuario,email,administrador FROM GSCUsuarios WHERE {0} = @parametroCondicao", parametroWhere);

dataAdapter.SelectCommand.Parameters.AddWithValue("parametroCondicao", parametroCondicao);

What I’d like to understand is why the first Query didn’t work. Addwithvalue didn’t just change the values?

Doing another Query, using LIKE.. also didn’t work! How this Query should be mounted?

var query = string.Format("SELECT nome,usuario,email,administrador FROM GSCUsuarios WHERE {0} LIKE '@parametroCondicao%'", parametroWhere);

dataAdapter.SelectCommand.Parameters.AddWithValue("parametroCondicao", parametroCondicao);

Thank you

Answer 1

Why the first query does not work: parameters for SQL queries are interpreted according to their value; a string parameter will be converted to a string in the query (*). Your first query is equivalent to:

SELECT nome,usuario,email,administrador
FROM GSCUsuarios
WHERE 'valorDoParametroWhere' = 'valorDoParametroCondicao'

And how the condition WHERE will never be true (unless the column value is equal to the column name), you will always have 0 results.

Regarding the use of LIKE, you can use the following:

var query = string.Format(@"SELECT nome,usuario,email,administrador
                            FROM GSCUsuarios
                            WHERE {0} LIKE @parametroCondicao",
    parametroWhere);

dataAdapter.SelectCommand.Parameters.AddWithValue(
    "parametroCondicao",
    parametroCondicao + "%");

Note that the use of string.Format to create SQL commands is dangerous - only do this if you are absolutely sure that the value of parametroWhere does not come directly from the user (or you are subject to SQL injection attacks)

(*) Actually the query is compiled before the application of the parameters, but the idea is the same.