An expression of non-boolean type specified in a context where a condition is expected, paramerization using DbCommand in c#

Question

I have this function to execute a parameterized select query:

public string LookUp(string sColuna, string sTabela, string sWhere)
{
    string[] Parameters = { "@column", "@table", "@where" };
    var comando = @"SELECT @column FROM @table WHERE @where";
    var cmd = this.OraConnection.CreateCommand();
    cmd.CommandText = comando;
    cmd.Parameters.AddRange(Parameters.Select(item => { var param = cmd.CreateParameter(); param.ParameterName = item; param.Value = 1; return para; }).ToArray());
    cmd.Parameters[0].Value = sColuna;
    cmd.Parameters[1].Value = sTabela;
    cmd.Parameters[2].Value = sWhere;
    DbDataReader Reader = cmd.ExecuteReader();
    return Reader.GetString(0);
}

when it reaches the DbDataReader Reader = cmd.ExecuteReader(); it throws me an exception saying: Additional information: An expression of non-boolean type specified in a context where a condition is expected, near '@where'. Can't one have a parameter in the where clause?

Now I've changed it to:

var cmd = this.OraConnection.CreateCommand();
            cmd.CommandText = string.Format("SELECT {} FROM {} WHERE", sColuna, sTabela, sWhere);
            DbDataReader Reader = cmd.ExecuteReader();
            return Reader.GetString(0);

A Where clause must evaluate to a Boolean expression... e.g. Where xyz = @where — Kevin, Jul 07 '16 at 13:52
Can't I have something like WHERE @something = @something? I'm passing this to the sWhere var: UserName = 'Joao' — LikeIfYouCaredAboutMyName, Jul 07 '16 at 13:54
@JaydipJ nope, @where contains val=val, hence I left the "WHERE" clause here ` var comando = @"SELECT @column FROM @table WHERE @where";` — LikeIfYouCaredAboutMyName, Jul 07 '16 at 14:01

score 0 · Accepted Answer · edited May 23 '17 at 11:46

0

String sWhere is the parameter needed to evaluate a boolean expression

[The Begining of your Query] WHERE [Something]=[Your Parameter]
[The Begining of your Query] WHERE [Something]=@where

For @where value = sWhere

If you want a more complexe answer about building WHERE clause, have a look at this answer about the same question

edited May 23 '17 at 11:46

Community

1
1

answered Jul 07 '16 at 13:59

JeromeVOB

24
3

ok I understand what you mean, but can't the [Something] also be a var? can it be a parameter, or it needs to be fix? – LikeIfYouCaredAboutMyName Jul 07 '16 at 14:16
If you follow the link you 'll see that it cannot be directly a parameter as you try but you can elaborate alternative option using object as parameter for the left member of the boolean expression. Basicly you can construct a parametrized where clause, but not the way you try ;) – JeromeVOB Jul 07 '16 at 14:20
Ok I've read the answers and I acknowledge that it works but not for my case, because I can't possible now what the field name will be so I can't make it fix. all the parameters in the query can change since this is ment to work both for oracle and sql and also for a wide range of users and databases. is there any other alternatives to this? I could just pass the command as a whole but then I wouldn't be able to parameterize. Does the select statement represents a risk in terms of sql injection? – LikeIfYouCaredAboutMyName Jul 07 '16 at 14:49
I could just pass a string with the command like: "SELEC something FROM something WHERE something = anotherSomething" – LikeIfYouCaredAboutMyName Jul 07 '16 at 14:49
var cmd = this.OraConnection.CreateCommand(); cmd.CommandText = string.Format("SELECT {} FROM {} WHERE", sColuna, sTabela, sWhere); DbDataReader Reader = cmd.ExecuteReader(); return Reader.GetString(0); – LikeIfYouCaredAboutMyName Jul 07 '16 at 14:54
What the new diagnostic? Error? Does your string.format throws exception? – JeromeVOB Jul 07 '16 at 16:47
I've found out that I actually need to build a dynamic query – LikeIfYouCaredAboutMyName Jul 15 '16 at 18:07

An expression of non-boolean type specified in a context where a condition is expected, paramerization using DbCommand in c#

1 Answers1