Npgsql failed to call plpgsql function

1

I'm doing tests of Npgsql . One of the tests tests the return of a plpgsql function. But the test is failing by saying that the function does not exist. In the description of the error the name is given in the function passed and the parameters. But I'm noticing that the first parameter type is wrong, as well as the function name too. Below my codes.

Library Code:

using System.Collections.Generic;
using Npgsql;
using System.Data;

namespace PostgreSqlDataAccessLayer
{
    public class Dal
    {
        public string StringConnection { get; set; }
        public NpgsqlConnection Connection { get; }

        public Dal ( string stringConnection )
        {
            StringConnection = stringConnection;

            Connection = new NpgsqlConnection ( StringConnection );
        }

        public object ExecuteNoQueryOperation ( string spOrSqlInstructions, CommandType commandType = CommandType.StoredProcedure, Dictionary<string, object> parameters = null )
        {
            NpgsqlCommand npgsqlCommand = new NpgsqlCommand(spOrSqlInstructions, Connection);
            npgsqlCommand.CommandType = commandType;

            if ( parameters != null )
                foreach ( var item in parameters )
                    npgsqlCommand.Parameters.Add ( new NpgsqlParameter ( item.Key, item.Value ) );

            try
            {
                Connection.Open ( );

                return npgsqlCommand.ExecuteScalar ( );

            }
            finally
            {
                if ( Connection != null && Connection.State != ConnectionState.Closed )
                    Connection.Close ( );

                npgsqlCommand.Dispose ( );
            }
        }

        public DataSet ExecuteQueryOperation ( string spOrSqlInstructions, CommandType commandType = CommandType.StoredProcedure, Dictionary<string, object> parameters = null )
        {
            NpgsqlCommand npgsqlCommand = new NpgsqlCommand(spOrSqlInstructions, Connection);
            npgsqlCommand.CommandType = commandType;

            if ( parameters != null )
                foreach ( var item in parameters )
                    npgsqlCommand.Parameters.Add ( new NpgsqlParameter ( item.Key, item.Value ) );

            NpgsqlDataAdapter npgsqlDataAtapter = new NpgsqlDataAdapter(npgsqlCommand);
            npgsqlDataAtapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataSet dataSet = new DataSet ( );

            try
            {
                npgsqlDataAtapter.Fill ( dataSet );
                return dataSet;
            }
            finally
            {
                npgsqlDataAtapter.Dispose ( );
                npgsqlCommand.Dispose ( );
            }
        }

        ~Dal()
        {
            if ( Connection != null )
                Connection.Dispose ( );
        }
    }
}

Test Method:

[TestMethod]
public void TestReturnFunction ( )
{
    Dictionary<string, object> dic= new Dictionary<string, object> ();

    dic.Add ( "f_name", "Joice Silva" );
    dic.Add ( "f_age", 31 );

    pgDal = new PostgreSqlDataAccessLayer.Dal ( "Host=192.168.56.2;Username=postgres;Password=$Sat2598$;Database=Test" );

    var ret = (int)pgDal.ExecuteNoQueryOperation ( "insertPerson", parameters: dic);

    Assert.AreEqual ( 31, ret);
}

Function plpgsql:

CREATE OR REPLACE FUNCTION public."insertPerson"(
    f_name character varying,
    f_age integer)
  RETURNS integer AS
$BODY$ 
DECLARE age_ret INTEGER;
BEGIN 
    INSERT INTO "person" VALUES (f_name, f_age) RETURNING age INTO age_ret;
    RETURN age_ret; 
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public."insertPerson"(character varying, integer)
  OWNER TO postgres;

Error:

Nome de Teste:  TestReturnFunction
FullName de Teste:  DataAccessLayerTest.PostgreSqlDataAccessTest.TestReturnFunction
Fonte de Teste: C:\Users\Matheus Saraiva\OneDrive\Desenvolvimento\Sistemas\DataAccessLayer\DataAccessLayer\DataAccessLayerTest\PostgreSqlDataAccessTest.cs: linha 38
Resultado de Teste: com Falha
Duração do Teste:   0:00:00,1832631

StackTrace do Resultado:    
em Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage)
   em Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode)
   em Npgsql.NpgsqlConnector.ReadExpecting[T]()
   em Npgsql.NpgsqlDataReader.NextResultInternal()
   em Npgsql.NpgsqlDataReader.NextResult()
   em Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)
   em Npgsql.NpgsqlCommand.ExecuteScalarInternal()
   em Npgsql.NpgsqlCommand.ExecuteScalar()
   em PostgreSqlDataAccessLayer.Dal.ExecuteNoQueryOperation(String spOrSqlInstructions, CommandType commandType, Dictionary'2 parameters) na C:\Users\Matheus Saraiva\OneDrive\Desenvolvimento\Sistemas\DataAccessLayer\DataAccessLayer\PostgreSqlDataAccessLayer\Dal.cs:linha 32
   em DataAccessLayerTest.PostgreSqlDataAccessTest.TestReturnFunction() na C:\Users\Matheus Saraiva\OneDrive\Desenvolvimento\Sistemas\DataAccessLayer\DataAccessLayer\DataAccessLayerTest\PostgreSqlDataAccessTest.cs:linha 46
Mensagem de Resultado:  
Método de teste DataAccessLayerTest.PostgreSqlDataAccessTest.TestReturnFunction gerou exceção: 
Npgsql.PostgresException: 42883: function insertperson(f_name => text, f_age => integer) does not exist

By the error description, the function name is wrong (all lowercase) and the first parameter type is also ( text , correct would be varchar ).

    
asked by anonymous 19.06.2016 / 17:43

0 answers