better way of using a single parameter multiple times in c#

Question

I'm new in using prepared statements for querying data from the database and I'm experiencing problems implementing parameters for c# specifically OracleParameters.

So let's say I have the following SQL:

string sql = "select * 
              from table1 t1, table2 t2 
              where t1.columnX = @parm and t2.columnY = @parm"

And the code is this:

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter("@parm", strParm));

The problem is when the cmd gets executed t1.columnX gets the value of strParm but when t2.columnY is just about to get the value of strParm, it throws an "ORA-01008: not all variables bound" exception.

It seems to me that the parameter gets to be substituted only once even though that parameter is seen somewhere else in the sql.

One solution I tried and works for me is this:

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter("@parm", strParm));
cmd.Parameters.Add(new OracleParameter("@parm", strParm));

Another solution is this:

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter("@parm1", strParm));
cmd.Parameters.Add(new OracleParameter("@parm2", strParm));

and the modified sql is this:

string sql = "select * 
             from table1 t1, table2 t2 
             where t1.columnX = @parm1 and t2.columnY = @parm2" 

The question is, is there a better way of doing this so that I don't have to add another parameter with the same value.

Note: I've just simplified the query above to show that @parm is used in several parts of the query. In real life this query has used the same parameter several times and its a pain to have to add more than one parameter of the same name and value.

Answer 1

I've had the same issue before and IIRC this solved it:

cmd.BindByName = true;

EDIT: I've just re-checked and this does allow you to set the parameter value once even though the parameter may be specified multiple times in the query. The only other thing that I'm doing differently to you is that I specify the parameter names with a leading :, for example :param.

Answer 2

In your case, there doesn't seem to be a need to actually use two parameters. How about changing your SQL to:

select * from table1 t1, table2 t2 where t1.columnX = @parm and t2.columnY = t1.columnX

or even

select * from table1 t1 join table2 t2 on t2.columnY = t1.columnX where t1.columnX = @parm