Inner join statement in bind variables

Question

I use bind variables to get data from the SQL tables.

The code:

string commandText = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3}", fields, from, whereStr, orderBy);
DbCommand command = GetSqlCommandWrapper(commandText);
int index = 0;
foreach (var item in whereParams)
{
    command.Parameters.Add(new OracleParameter(item, whereParamsBy[index++]));
}
DataTable db = new DataTable();

DataSet dataSet = null;
dataSet = CurrentDatabase.ExecuteDataSet(command);

The CommandText:

fields = "TableA.*";
from = "TableA INNER JOIN TableB ON TableA .id = TableB.id";
whereStr = "TableA .id > 10";
orderBy = "TableA .id, TableB .id";

Everything works fine until I have to get data from 2 tables like the query:

select * from a inner join b on a.id = b.id where....

Someone can tell me how to do it? Thanks in advance

Can you show your `commandText` attempt that includes the inner join commands? — gravity, Aug 22 '18 at 13:47
why and how does it fail? you'd include the joins in the `from` string or shift the `String.Format` arguments to the right, so {2} becomes your placeholder for the join clause(s). But IMO the whole approach is questionable. Could you use an existing framework for dynamic sql generation? — Cee McSharpface, Aug 22 '18 at 13:48
I inclieded it in the from string, but I get an error. can you give example how the commandText shoud show? — rikush, Aug 22 '18 at 13:57
Make your `from` contain `a inner join b on a.id = b.id`, done. You're generating the query text yourself, so it's your job to make sure the result ultimately makes sense. When constructing SQL, always evaluate the results afterwards (that is, print or inspect `commandText`); it's usually obvious when the syntax ends up wrong. Note that when you start doing joins, you want to add the table names to the `fields`, otherwise something like `id` is ambiguous. — Jeroen Mostert, Aug 22 '18 at 14:17
I got this error: ORA-01036:Variable name or variable number is invalid — rikush, Aug 22 '18 at 14:24
Undoubtedly -- and what's in `commandText` when it gives this error? What happens if you execute that query as a whole on the server? Does `whereParams` still match up with the statement's parameters? — Jeroen Mostert, Aug 22 '18 at 14:26

score 2 · Answer 1 · answered Aug 22 '18 at 14:17

The overall approach is not very nice, but in terms of getting what you want, this should do it.

fields = "*";
from = "TableA AS a INNER JOIN TableB AS B ON a.id = b.id";
whereStr = "a.id > 10";
orderBy = "a.id, b.id";

Set your input parameters to that, or just paste it in before the code you gave us and it will produce the command SELECT * FROM TableA AS a INNER JOIN TableB AS B ON a.id = b.id WHERE a.id > 10 ORDER BY a.id, b.id

Please note that orderBy = "a.id; DROP TABLE TableA;"; is a scary prospect

Inner join statement in bind variables

1 Answers1