How to call plpgsql function from c#

Question

I've created a function in plpgsql and I'm trying to call the stored procedure from .net core api But I'm getting following exception in c#

42883: function proc_insert_test(brndcode => integer, brndname => character varying, brndsname => character varying, prdtype => character, discontinue => character, crddate => date, status => integer, recstat => integer, brndgrpseqno => integer, wefrom => date) does not exist

No function matches the given name and argument types. You might need to add explicit type casts.

Procedure:

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$
;

Procedure calling from 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();

What can be the issue?

try renaming parameters in .net to match with name in sql script, @brndcode => @p_brndcode, @brndname => @p_brndname and so on. — Arjun Vachhani
@ArjunVachhani OMG, Thank you so much, I completely forgot it XD Now it's working properly — Shreyas Pednekar

harisharju harisharju · Accepted Answer · 2019-11-14T12:01:22

I'm also faced this issue. After some analyze found that date datatype casting not working. So we changed our calling method like this,

Without return value:

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();

With return value

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);
    }
}

How to call plpgsql function from c#

2 Answers