Calling a stored procedure with Dapper and Postgres

c# dapper postgresql stored-procedures

Question

I have been trying to call a Postgresql stored procedure using Dapper and every example I have seen has the same thing but for some reason it is not working for me. It seems like it is calling the stored procedure but it never returns any results. This is my code:

using (PgSqlConnection c = new PgSqlConnection(Configuration.ConnectionString("database_string")))
        {
            var values = c.Query<TagValue>("get_values", new { _tag = tag, _start = start, _end = end }, commandType: CommandType.StoredProcedure);
        }

I cannot get this to return any results but it also does not throw any errors. Please help at least point me in the direction to look. If I run the query using a string such as:

values = c.Query<TagValue>(string.Format(@"select * from get_values('{0}', '{1}', '{2}');", tag, start, end));

it works correctly and returns the right results.

Popular Answer

This is how I call stored procs using Dapper

public class Parameters : DynamicParameters
{
    public new void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null)
    {
        if (dbType == null && value is string)
        {
            if (size == null)
            {
                dbType = DbType.AnsiString;
            }
            else
            {
                dbType = DbType.AnsiStringFixedLength;
            }
        }
        base.Add(name, value, dbType, direction, size);
    }
}

const string query = @"yourquery"
Parameters p = new Parameters();
        p.Add("@Tag", tag);
        p.Add("@Start", start);
        p.Add("@End", end);
var values = c.Query<TagValue>("get_values", p,CommandType.StoredProcedure);

DynamicParameters is a part of Dapper assembly.

Hope this helps.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why