Am new in postgresql, and I want to call stored procedure (postgresql) with some parameter in Asp.net Core web api via dapper.
Error: syntax error at or near "@"
Procedure:
CREATE OR REPLACE PROCEDURE public.usp_addbook(
book_id integer,
book_name text,
book_desc text)
LANGUAGE 'plpgsql'
AS $BODY$
begin
INSERT INTO book (book_id,book_name,book_desc) values (book_id,book_name,book_desc);
commit;
end;
$BODY$;
Asp.net Core :
await WithConnection(async conn =>
{
var p = new DynamicParameters();
p.Add("@book_id", entity.book_id);
p.Add("@book_name", entity.book_name);
p.Add("@book_desc", entity.book_desc);
string callSP = "CALL " + _commandText.AddBookByStoredProcedure + " (@book_id,@book_name,@book_desc)";
await conn.ExecuteAsync(callSP, p, commandType: CommandType.StoredProcedure);
});
protected async Task WithConnection(Func<IDbConnection, Task> getData)
{
try
{
await using var connection = new NpgsqlConnection(_ConnectionString);
await connection.OpenAsync();
await getData(connection);
}
catch (TimeoutException ex)
{
throw new Exception(String.Format("{0}.WithConnection() experienced a SQL timeout", GetType().FullName), ex);
}
catch (NpgsqlException ex)
{
throw new Exception(String.Format("{0}.WithConnection() experienced a SQL exception (not a timeout)", GetType().FullName), ex);
}
}
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…