如何在C＃中调用postgresql函数 [英] How to call postgresql functions in C#

问题描述

HI，

如何在c＃中调用这些类型的查询

How to call these type of query in c#

CREATE OR REPLACE FUNCTION kiadb_fngetdetailsbyvariousparam(ind_zone text,dst text,nmind text,ind text,implst text,rsvr text,n34b text,prjapr text)
RETURNS   SETOF RECORD AS 
$BODY$
DECLARE 
sql text;
BEGIN
sql:='select indzone,dstr,nmindar,plno,pltar,nmalt from plotboundary where indzone='|| quote_literal(ind_zone)||' AND indx='|| quote_literal(ind);
IF dst IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' dstr = ' || quote_literal(dst);
END IF;
IF nmind IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' nmindar = ' || quote_literal(nmind);
END IF;
IF implst IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' implst = ' || quote_literal(implst);
END IF;
IF rsvr IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' rsvr = ' || quote_literal(rsvr);
END IF;
IF n34b IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' n34b = ' || quote_literal(n34b);
END IF;
IF prjapr IS NOT NULL THEN
 sql := sql || ' AND ';
 sql := sql ||' prjapr = ' || quote_literal(prjapr);
END IF;
RETURN QUERY EXECUTE sql;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION kiadb_fngetdetailsbyvariousparam( text,
 text, text, text, text, text, text, text)
  OWNER TO postgres;

在postgresql中调用

calling in postgresql

select * from kiadb_fngetdetailsbyvariousparam('Tumakuru Zone',null,null,'Allotted','Yet to Start','General','No','DLSWCC')
AS t(indzone character varying,dstr character varying,nmindar character varying,plno character varying,pltar double precision,nmalt character varying);

在数据库中工作正常，但我想在c＃中调用它。怎么做



我尝试过：



我我正在尝试在c中调用函数＃

working perfect in database but i want call it in c#.How to do it

What I have tried:

I am trying calling function in c#

DataTable dt = pgHelper.ExecuteParamerizedSelectCommand("select * from kiadb_fngetdetailsbyvariousparam AS t(indzone character varying,dstr character varying,nmindar character varying,plno character varying,pltar double precision,nmalt character varying) ", CommandType.Text, parms);

i我的错误就像函数不存在一样

i am getting error like function doesn't exist

推荐答案

BODY

DECLARE
sql text ;
BEGIN
sql：= ' 从plotboundary中选择indzone，dstr，nmindar，plno，pltar，nmalt，其中indzone =' || quote_literal（ind_zone）|| ' AND indx =' ||的quote_nullable（IND）;
IF dst IS NOT NULL 那么
sql：= sql || ' AND';
sql：= sql || ' dstr =' ||的quote_nullable（DST）;
END IF ;
IF nmind IS NOT NULL 那么
sql：= sql || ' AND';
sql：= sql || ' nmindar =' ||的quote_nullable（nmind）;
END IF ;
IF implst IS NOT NULL 那么
sql：= sql || ' AND';
sql：= sql || ' implst =' ||的quote_nullable（implst）;
END IF ;
IF rsvr IS NOT NULL 那么
sql：= sql || ' AND';
sql：= sql || ' rsvr =' ||的quote_nullable（rsvr）;
END IF ;
IF n34b IS 不 NULL 那么
sql：= sql || ' AND';
sql：= sql || ' n34b =' ||的quote_nullable（n34b）;
END IF ;
IF prjapr IS NOT NULL 那么
sql：= sql || ' AND';
sql：= sql || ' prjapr =' ||的quote_nullable（prjapr）;
END IF ;
RETURN QUERY EXECUTE sql;
END ;

DECLARE sql text; BEGIN sql:='select indzone,dstr,nmindar,plno,pltar,nmalt from plotboundary where indzone='|| quote_literal(ind_zone)||' AND indx='|| quote_literal(ind); IF dst IS NOT NULL THEN sql := sql || ' AND '; sql := sql ||' dstr = ' || quote_literal(dst); END IF; IF nmind IS NOT NULL THEN sql := sql || ' AND '; sql := sql ||' nmindar = ' || quote_literal(nmind); END IF; IF implst IS NOT NULL THEN sql := sql || ' AND '; sql := sql ||' implst = ' || quote_literal(implst); END IF; IF rsvr IS NOT NULL THEN sql := sql || ' AND '; sql := sql ||' rsvr = ' || quote_literal(rsvr); END IF; IF n34b IS NOT NULL THEN sql := sql || ' AND '; sql := sql ||' n34b = ' || quote_literal(n34b); END IF; IF prjapr IS NOT NULL THEN sql := sql || ' AND '; sql := sql ||' prjapr = ' || quote_literal(prjapr); END IF; RETURN QUERY EXECUTE sql; END;

BODY

这篇关于如何在C＃中调用postgresql函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！