Execute stored procedure with parameters postgresql

Question

My Npgsql version 3.2.5 - Postgresql 9.6

I get this error with CommandType.StoredProcedure (but CommandType.Text works):

Npgsql.PostgresException: '42883: function customer_select(pEmail => text, Password => text) does not exist'

string sql3 = @"customer_select";

NpgsqlConnection pgcon = new NpgsqlConnection(pgconnectionstring);
pgcon.Open();
NpgsqlCommand pgcom = new NpgsqlCommand(sql3, pgcon);
pgcom.CommandType = CommandType.StoredProcedure;
pgcom.Parameters.AddWithValue(":pEmail", "[email protected]");
pgcom.Parameters.AddWithValue(":pPassword", "eikaylie78");
NpgsqlDataReader pgreader = pgcom.ExecuteReader();

while (pgreader.Read()) {
    string name = pgreader.GetString(1);
    string surname = pgreader.GetString(2);
}

This is the function in the database:

CREATE OR REPLACE FUNCTION public.customer_select(
    pemail character varying, ppassword character varying)
RETURNS SETOF "CustomerTest"
LANGUAGE 'plpgsql'
COST 100.0
AS $function$                                   
BEGIN 
    RETURN QUERY
        SELECT "CustomerTestId", "FirstName", "LastName", "Email", "Password"
        FROM public."CustomerTest"
        WHERE "Email" = pEmail AND "Password" = pPassword;
END; 
$function$;
ALTER FUNCTION public.customer_select(character varying, character varying)
OWNER TO postgres;

Solution 1

Npgsql does support named parameters, but your parameters' case doesn't match your functions', try pemail instead of pEmail and ppassword instead of pPassword.

Note that there's no particular advantage to using CommandType.StoredProcedure over CommandType.Text with Npgsql - both end up doing the same thing. CommandType.StoredProcedure is mainly to ease porting code from SqlServer etc.

Solution 2

According to the issue the NPS SQL doesn't support named parameters because PostgreSQL doesn't. But you can try following:

string sql3 = @"select * from customer_select(:pEmail, :pPassword)";    
NpgsqlConnection pgcon = new NpgsqlConnection(pgconnectionstring);
pgcon.Open();
NpgsqlCommand pgcom = new NpgsqlCommand(sql3, pgcon);
pgcom.CommandType = CommandType.Text;
pgcom.Parameters.AddWithValue("pEmail", "[email protected]");
pgcom.Parameters.AddWithValue("pPassword", "eikaylie78");
NpgsqlDataReader pgreader = pgcom.ExecuteReader();

while (pgreader.Read()) {
    string name = pgreader.GetString(1);
    string surname = pgreader.GetString(2);
}

According to this issue