用参数postgresql执行存储过程

Question

我的NPGSQL版本3.2.5 -PostgreSQL 9.6

我使用CommandType.StoredProcedure遇到此错误（但CommandType.Text有效）：

npgsql.postgresexception：'42883：function customer_select（pemail => text，password => text）不存在'

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", "[e-mail]");
pgcom.Parameters.AddWithValue(":pPassword", "eikaylie78");
NpgsqlDataReader pgreader = pgcom.ExecuteReader();
while (pgreader.Read()) {
    string name = pgreader.GetString(1);
    string surname = pgreader.GetString(2);
}

这是数据库中的功能：

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;

Answer 1

npgsql确实支持命名参数，但是您的参数的情况不匹配您的功能"，请尝试pemail，而不是pEmail和ppassword，而不是pPassword。

。

请注意，使用CommandType.StoredProcedure与npgsql一起使用CC_7没有特别的优势 - 两者最终都做同样的事情。CommandType.StoredProcedure主要是为了从SQLServer等放大移植代码。

Answer 2

根据问题， nps sql 不支持名为参数，因为PostgreSQL没有。但是您可以尝试以下内容：

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", "[e-mail]");
pgcom.Parameters.AddWithValue("pPassword", "eikaylie78");
NpgsqlDataReader pgreader = pgcom.ExecuteReader();
while (pgreader.Read()) {
    string name = pgreader.GetString(1);
    string surname = pgreader.GetString(2);
}

根据此问题