So I'm trying to execute pl/pgSQL functions from a web app using Dapper micro-ORM. I'm always getting null values irrespective of the values I'm passing as parameters in QueryMultiple method; although in database the function gives proper result.
DAL Method:
public List<JSONData> ExecutePostgres(string spName, List<QueryParam> Params)
{
List<JSONData> students = new List<JSONData>();
using (var dbInstance = new NpgsqlConnection(postgreConnectionString))
{
using (var reader = dbInstance.QueryMultiple(spName, new { std_id = Params[0].value }, commandType: CommandType.StoredProcedure))
{
students = reader.Read<JSONData>().ToList();
}
}
return students;
}
Implementation Method:
public List<string> ExecuteProcedure(string spName, List<QueryParam> Params)
{
var list = objDLOperation.ExecutePostgres(spName, Params);
var strList = new List<string>();
list.ForEach(x => strList.Add(x.JSONResult));
return strList;
}
This strList is always a List of null values
Entity Class:
public class JSONData
{
public string JSONResult { get; set; }
}
pl/pgSQL Function:
CREATE OR REPLACE FUNCTION public.usp_getstudent(std_id integer)
RETURNS SETOF json
AS $function$
BEGIN
RETURN QUERY
select row_to_json(result) from (select * from student where id = std_id) as result;
END
$function$ LANGUAGE 'plpgsql';