如何从c#调用plpgsql函数

Question

我在plpgsql中创建了一个函数，我试图从. net核心api调用存储过程，但我在c#中得到了以下异常

42883：功能程序插入测试（brndcode=

没有函数匹配给定的名称和参数类型。可能需要添加显式类型转换。

程序：

CREATE OR REPLACE FUNCTION public.proc_insert_test(p_brndcode integer, 
                                                p_brndname varchar(100), 
                                                p_brndsname varchar(100), 
                                                p_prdtype char(1),
                                                p_discontinue char(1),
                                                p_crddate date,
                                                p_status integer,
                                                p_recstat integer,
                                                p_brndgrpseqno integer,
                                                p_wefrom date)
RETURNS char
LANGUAGE plpgsql
AS $body$
BEGIN
    Insert into arc_mmstbrndgroup(brndcode, brndname, brndsname, prdtype, discontinue, crddate, status, recstat, brndgrpseqno, wefrom) 
    values(p_brndcode, p_brndname, p_brndsname, p_prdtype, p_discontinue, p_crddate, p_status, p_recstat, p_brndgrpseqno, p_wefrom);
    return 'saved';
END;
$body$
;

从c#调用过程：

NpgsqlCommand cmd = new NpgsqlCommand("proc_insert_test", _connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@brndcode", NpgsqlTypes.NpgsqlDbType.Integer, 123);
cmd.Parameters.AddWithValue("@brndname", NpgsqlTypes.NpgsqlDbType.Varchar, 100, "Test3");
cmd.Parameters.AddWithValue("@brndsname", NpgsqlTypes.NpgsqlDbType.Varchar, 100, "Test3");
cmd.Parameters.AddWithValue("@prdtype", NpgsqlTypes.NpgsqlDbType.Char, 1, "T");
cmd.Parameters.AddWithValue("@discontinue", NpgsqlTypes.NpgsqlDbType.Char, 1, "T");
cmd.Parameters.AddWithValue("@crddate", NpgsqlTypes.NpgsqlDbType.Date, DateTime.Now);
cmd.Parameters.AddWithValue("@status", NpgsqlTypes.NpgsqlDbType.Integer, 1);
cmd.Parameters.AddWithValue("@recstat", NpgsqlTypes.NpgsqlDbType.Integer, 9);
cmd.Parameters.AddWithValue("@brndgrpseqno", NpgsqlTypes.NpgsqlDbType.Integer, 1234);
cmd.Parameters.AddWithValue("@wefrom", NpgsqlTypes.NpgsqlDbType.Date, DateTime.Now);
_connection.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
_connection.Close();

会有什么问题？

Answer 1

对我来说，它正在发挥作用

PGDbContext _context = new PGDbContext();
            string d1 = "mm5";
            string d2 = "mmTest6";
            int d3 = 11;
            string d4 = "1";
            var DistributionChannelGUID = _context.Database.SqlQuery<List<string>>("call dbo.insupdelglclassmaster({0},{1},{2},{3})", d1, d2, d3, d4).ToList();

Answer 2

我也面临这个问题。经过分析发现，数据类型转换不起作用。所以我们像这样改变了我们的呼叫方法，

无返回值：

string strquery = "SELECT PROC_INSERT_TEST(123,'Test3','Test3','T','T',";
strquery = strquery + "'" + DateTime.Now.ToString("MMM-dd-yyyy HH:mm:ss") + "',1,9,1234,";
strquery = strquery + "'" + DateTime.Now.ToString("MMM-dd-yyyy HH:mm:ss") + "')";

NpgsqlCommand cmd = new NpgsqlCommand(strquery, _connection);
_connection.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
_connection.Close();

返回值

string strquery = "BEGIN; SELECT PROC_INSERT_TEST_WITH_RETURN(123,'Test3','Test3','T','T',";
strquery = strquery + "'" + DateTime.Now.ToString("MMM-dd-yyyy HH:mm:ss") + "',1,9,1234,";
strquery = strquery + "'" + DateTime.Now.ToString("MMM-dd-yyyy HH:mm:ss") + "')";

NpgsqlCommand cmd = new NpgsqlCommand(strquery, _connection);
_connection.Open();
object cursorVal = cmd.ExecuteScalar();
DataSet ds = FetchAll(_connection, cursorVal);
cmd.Dispose();
_connection.Close();


private DataSet FetchAll(NpgsqlConnection _connection, object cursorVal)
{
    try
    {
        DataSet actualData = new DataSet();

        string strSql = "fetch all from \"" + cursorVal + "\";";
        NpgsqlCommand cmd = new NpgsqlCommand(strSql, _connection);
        NpgsqlDataAdapter ada = new NpgsqlDataAdapter(cmd);
        ada.Fill(actualData);

        return actualData;

    }
    catch (Exception Exp)
    {
        throw new Exception(Exp.Message);
    }
}