2

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';

1 Answer 1

2

Your column appears to be either named result, or be anonymous - but your local type has a property called JSONResult. Dapper is fussy about names. If you make the names match, it should work fine. However, frankly:

  • if you're only returning one column, there's no need for an entity - just use <string> (this also bypasses the naming issue - dapper doesn't care about names for single column fetches)
  • if you're only returning one grid, there's no need for QueryMultiple - just use Query<T>

As a very minor point: .AsList() will be more efficient than .ToList() - avoids any additional copying.

Personally, I'd say "return typed columns, not JSON", but: that's up to you.

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

4 Comments

The result set is always (single column x single/multiple rows) in my case. So in that case naming should not be a factor ?
Naming is a factor. Dapper is getting "result" and tries to map it to "JSONResult" in the Entity Class. That fails.
Thanks for the explanation both Marc & Palle :)
@izengod naming is only ignored if the T is a simple well-known type, not a class etc; or when we deploy the value-tuples support, naming is ignored for value tuples

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.