I am creating a .NET target framework 7 Web API and I am trying to get data from a Postgresql function using Dapper, but I only get this error :

42601: syntax error at or near "getallvendormasternew"
POSITION: 1

getallvendormasternew is a Postgresql function and I have defined this function variable name in the StoredProcedure class like this:

public class StoredProcedure
{
    public static string GetVendorList = "getallvendormasternew";
}

I have called this function as shown here, using DynamicParameter:

var queryParameters = new DynamicParameters();
queryParameters.Add("_pagenumber", 1);
queryParameters.Add("_pagesize", 10);
queryParameters.Add("_order_by_spec", Convert.ToString("vendorid_DESC"));

return await QueryAsync<VendorModel>(StoredProcedure.GetVendorList, queryParameters);

QueryAsync is implemented in the Repository class like this:

public async Task<IReadOnlyList<T>> QueryAsync<T>(string sql, object param = null, CommandType commandType = CommandType.StoredProcedure)
{
    return (await _db.QueryAsync<T>(sql, param)).ToList();
}

Please guide me how to solve this error.

Thanks.

I am trying to get data from postgresql function using above also I have try with add CommandType as stored procedure in QueryAsync but when I try this, I get an error that this is not a stored procedure because I created it as a function in PostgreSql.

Also I have tried calling it like public.getallvendormasternew in the StoredProcedure class, but I still get the same error.

I want to know how I can sort out this error and what the issue really is.

Thanks.

CodePudding user response：

If I use CommandType commandType = CommandType.Text and pass dynamic parameter in query and create a model of only that fields which is a return from function then I get a data.

CodePudding user response：

Have you tried calling a function?

return await QueryAsync<VendorModel>(
    "SELECT getallvendormasternew(_pagenumber, _pagesize, _order_by_spec)"
    ,queryParameters
    ,CommandType.Text);