DIPSAS
Dapper OracleDynamicParameters, multipleQquery and clause 'in'
Hi, I am struggling with something not sure why not able to execute.
I have a clause in that I would like to execute in a second query... for simplicity let's say something like :
`
begin
open :c_query1 for
select * from T1;
open :c_query2 for
select * from T2 t_t2 where t_t2.name in :pNames;
end;
`
where I would have my parameters as `
var dynParams = new OracleDynamicParameters();
dynParams.Add(":c_query1", direction: ParameterDirection.Output, dbType: OracleMappingType.RefCursor);
dynParams.Add(":c_query2", direction: ParameterDirection.Output, dbType: OracleMappingType.RefCursor);
dynParams.Add(":pNames", new[] {"AA", "BB"});
`
when I execute this my "in clause" comes empty.... any idea? thanks
Hello! Can you please show that how are you declaring the "pNames" parameter into the procedure?
Hi @ut786 actually it is a text query....
I am able to query "in clause" with DynamicParameters... however when I change it for OracleDynamicParameters then query is as if no collection passed on....
Actually I couldn't get what you are actually doing. But somehow I get what you want to do. I am showing you the sample on how you can achieve that.
First change pNames type to VARCHAR2 then write the query like this in the procedure:
open :c_query2 for 'select * from T2 t_t2 where t_t2.name in ('|| :pNames';
Then in the C# code:
var values = new string[]{"AA", "BB"};
dynParams.Add(":pNames", direction: ParameterDirection.Input, dbType: OracleMappingType.VARCHAR2, value: string.Join(',', values));
Hi @ut786 , that is what I already doing (string concatenation) and was trying to avoid... as for that is always risk of sql injection, I would say.... and actually using "Aggregate" - cleaner....
Hi @ut786 , that is what I already doing (string concatenation) and was trying to avoid... as for that is always risk of sql injection, I would say.... and actually using "Aggregate" - cleaner....
Try to use DataTable in instead of string array.