postgresql 使用参数postgresql执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46112835/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Execute stored procedure with parameters postgresql
提问by Korkut Düzay
My Npgsql version 3.2.5 - Postgresql 9.6
我的 Npgsql 版本 3.2.5 - Postgresql 9.6
I get this error with CommandType.StoredProcedure(but CommandType.Textworks):
我收到此错误CommandType.StoredProcedure（但CommandType.Text有效）：
Npgsql.PostgresException: '42883: function customer_select(pEmail => text, Password => text) does not exist'
Npgsql.PostgresException: '42883: 函数 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", "[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;
回答by Shay Rojansky
Npgsql does support named parameters, but your parameters' case doesn't match your functions', try pemailinstead of pEmailand ppasswordinstead of pPassword.
Npgsql 确实支持命名参数，但是您的参数大小写与您的函数不匹配，请尝试pemail代替pEmail和ppassword代替pPassword.
Note that there's no particular advantage to using CommandType.StoredProcedureover CommandType.Textwith Npgsql - both end up doing the same thing. CommandType.StoredProcedureis mainly to ease porting code from SqlServer etc.
请注意，与 Npgsql一起使用CommandType.StoredProcedureover没有特别的优势CommandType.Text——两者最终都会做同样的事情。CommandType.StoredProcedure主要是为了方便从 SqlServer 等移植代码。
回答by slesh
According to the issuethe NPS SQLdoesn't support named parameters because PostgreSQL doesn't. But you can try following:
根据问题，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", "[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
根据这个问题
