System.Data.Common.DbCommand: Parameterized Query as Procedure Parameter?

Question

I have procedure in PostgreSQL defined as:

CREATE OR REPLACE FUNCTION CreateCursorC(text, text)
 RETURNS text
 LANGUAGE c
AS '$libdir/mylibs', $function$createcursorc$function$

Execute example:

SELECT CreateCursorC('cursor_name', 'SELECT a FROM x WHERE a=''text''');

Of course I would like to use parameters (DbCommand.Parameters). Like this:

SELECT CreateCursorC($1, 'SELECT a FROM x WHERE a=$2');

Unfortunately it's not working because parameter $2 is in quotes. Is there a way to accomplished this task using parameters and not by writing custom SQL escaping function?

I tried to get an answer at Devart Forum, but no luck: Parameterized Query as Procedure Parameter? | Devart Forums

Answer 1

I think I understand your issue. I ran into a similar problem when trying to setup a parameterized query that included a LIKE expression in MySQL, via C#.

The trick that I found, in the case of the LIKE expression, was to make the % characters part of the parameter. In your case, this same type of logic may work for your quoted text.

Here's a snippet of code showing what I did in my case:

IDBCommandParameters cmdParams = dbContext.CreateDBCommandParameters();

cmdParams.AddParameter(QueryConstants.likeParam, string.Format("%{0}%", likeFilter));

List<TQueryResult> companies = LoadModelList<TQueryResult>(dbContext.Find(QueryConstants.findCompaniesLikeStatement, cmdParams, false), "");

return companies;