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

Some background would be helpful here. Why does the second parameter being quoted matter? What does this function do, and why do you have to pass it SQL text? What exactly "does not work" - what happens, and what should happen instead? What's the error message? PostgreSQL version? — Craig Ringer
– Craig Ringer, Commented Jun 4, 2014 at 1:13
Generally: no, it is not. Parameters are like variables in compiled languages - and you can not put source code in there and execute it. — TomTom
– TomTom, Commented Jun 11, 2014 at 19:14
In fact you can. Function is written in C. It creates cursor, count rows and returns position of last "selected" row. This function is much faster than standard operations using cursors. The only problem is how to pass parameters to query in secure way. — Tomasz Malik
– Tomasz Malik, Commented Jun 11, 2014 at 19:39
I'm confused by your question. Do you need help understanding how to setup parameterized queries in C# code? — dblood
– dblood, Commented Jun 11, 2014 at 19:42
@dblood No. I know how to do it. Problem is how to pass parameterized query as parameter? — Tomasz Malik
– Tomasz Malik, Commented Jun 11, 2014 at 19:46

dblood · Accepted Answer · 2014-06-11 20:21:34Z

2

+25

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;

answered Jun 11, 2014 at 20:21

dblood

1,77817 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related