CODEWITHSUNDEEP
c#postgresqlstored-proceduresdapper
GBreen12
GBreen12

Reputation: 1900

Calling a stored procedure with Dapper and Postgres

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.

Upvotes: 1

Views: 6680

Answers (2)

Augusto Humire Martinez
Augusto Humire Martinez

Reputation: 23

It's a example how you could call a function and return a resulset.

public IEnumerable<Account> Login(string code)
{
    using (IDbConnection database = new NpgsqlConnection("yourConnectionString"))
    {
        var parameters = new DynamicParameters();
        parameters.Add("_code", code);
        
        return database.Query<Account>("AccountAuthenticate", parameters, commandType: CommandType.StoredProcedure);
    }
}

and it's their postgresql function

CREATE OR REPLACE FUNCTION AccountAuthenticate(_code VARCHAR) 
    RETURNS TABLE (
         Code VARCHAR
        ,Password VARCHAR
        ,Salt VARCHAR
    )
    LANGUAGE plpgsql  AS $$ DECLARE 
     BEGIN
    RETURN QUERY
    
        SELECT 
             a.code
            ,a.password
            ,a.salt
        FROM account a
        WHERE a.code = _code
END; $$;

Upvotes: 2

Salman Syed
Salman Syed

Reputation: 568

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.

Upvotes: 1

Related Questions