How to call PostgreSQL 13 stored procedure (no function) with cursor INOUT parameter from Npgsql 4.1.5.0 in C#

Question

I have this Stored Procedure with IN char parameter and INOUT cursor parameter:

CREATE OR REPLACE PROCEDURE SP_ObtenerFacturaPorNombreCliente(IN p_nombreCliente CHAR, INOUT p_cursorFacturas REFCURSOR)
LANGUAGE PLPGSQL 
AS 
$$
BEGIN
    OPEN p_cursorFacturas FOR
    SELECT "CodigoFactura", "NombreCliente", "DireccionCliente", "TelefonoCliente", "Fecha", "SubTotal", "Iva", "ValorIva", "Total", "Geografico", "Geometrico" FROM "Factura"
    WHERE "NombreCliente" = p_nombreCliente
    ORDER BY "CodigoFactura";
END
$$

The stored procedure calling with Npgsql 4.1.5.0 in C#:

NpgsqlConnection npgsqlConnection = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=mybase;User Id=user;Password=password;");
npgsqlConnection.Open();
npgsqlConnection.TypeMapper.UseNetTopologySuite();
string sentencialSQL = "CALL SP_ObtenerFacturaPorNombreCliente(:p_nombreCliente, :p_cursorFacturas);";
NpgsqlCommand npgsqlCommand = new NpgsqlCommand(sentencialSQL, npgsqlConnection);
// ===============================
NpgsqlParameter npgsqlParameter1 = new NpgsqlParameter();
npgsqlParameter1.ParameterName = ":p_nombreCliente";
npgsqlParameter1.Value = "Perico de los palotes";
npgsqlParameter1.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Text;
npgsqlParameter1.Direction = ParameterDirection.Input;
npgsqlCommand.Parameters.Add(npgsqlParameter1);
// -------------------
NpgsqlParameter npgsqlParameter2 = new NpgsqlParameter();
npgsqlParameter2.ParameterName = ":p_cursorFacturas";
npgsqlParameter2.Value = string.Empty;
npgsqlParameter2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor;
npgsqlParameter2.Direction = ParameterDirection.InputOutput;
npgsqlCommand.Parameters.Add(npgsqlParameter2);
// ===============================
npgsqlCommand.CommandType = CommandType.Text; // CommandType.StoredProcedure is with Function
NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader();

With: npgsqlParameter2.Value = string.Empty; I have this error:

42601: syntax error at or near <<:>>

With: npgsqlParameter2.Value = null; I have this error:

Parameter :p_cursorFacturas must be set

UPDATE:

With @madreflection suggestion, I changed null by DBNull.Value but the calling changed npgsqlParameter2 with "<unnamed portal 1>"

NpgsqlConnection npgsqlConnection = new NpgsqlConnection("Server=127.0.0.1;Port=5432;Database=mybase;User Id=user;Password=password;");
npgsqlConnection.Open();
npgsqlConnection.TypeMapper.UseNetTopologySuite();
string sentencialSQL = "CALL SP_ObtenerFacturaPorNombreCliente(:p_nombreCliente, :p_cursorFacturas);";
NpgsqlCommand npgsqlCommand = new NpgsqlCommand(sentencialSQL, npgsqlConnection);
// ===============================
NpgsqlParameter npgsqlParameter1 = new NpgsqlParameter();
npgsqlParameter1.ParameterName = ":p_nombreCliente";
npgsqlParameter1.Value = "Perico de los palotes";
npgsqlParameter1.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Text;
npgsqlParameter1.Direction = ParameterDirection.Input;
npgsqlCommand.Parameters.Add(npgsqlParameter1);
// -------------------
NpgsqlParameter npgsqlParameter2 = new NpgsqlParameter();
npgsqlParameter2.ParameterName = ":p_cursorFacturas";
npgsqlParameter2.Value = DBNull.Value;
npgsqlParameter2.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor;
npgsqlParameter2.Direction = ParameterDirection.InputOutput;
npgsqlCommand.Parameters.Add(npgsqlParameter2);
// ===============================
npgsqlCommand.CommandType = CommandType.Text; // CommandType.StoredProcedure is with Function
NpgsqlDataReader npgsqlDataReader = npgsqlCommand.ExecuteReader();

Does this answer your question? Use Null as parameter in SQL Although that question/answer has to do with SQL Server, it applies almost universally across ADO.NET providers, including Npgsql. — madreflection
– madreflection, Commented Oct 18, 2020 at 5:29
Does this answer your question? How to execute Anonymous Block PL/pgSQL (PostgreSQL 13) from Npgsql 4.1.5.0 in C# — Caius Jard
– Caius Jard, Commented Oct 18, 2020 at 8:25
@madreflection Thanks, with npgsqlParameter2.Value = DBNull.Value; and npgsqlCommand.ExecuteNonQuery(); I can call the Stored Procedure, but npgsqlParameter2 update with: "<unnamed portal 1>", how I could get the cursor values? — Ejrr1085
– Ejrr1085, Commented Oct 19, 2020 at 20:26
@CaiusJard: I spoke too quickly. I just discovered that I had named the object with "proc_" but recreated it as a function "temporarily" to try something and forgot to change it back before setting the work aside. Npgsql cannot read from a cursor by way of output parameter. — madreflection
– madreflection, Commented Oct 23, 2020 at 6:11
@Ejrr1085: Cursors are read using FETCH ALL IN "<unnamed portal 1>"; upon return from a function. Try wrapping your stored procedures in functions and using a new command object to execute the FETCH ALL IN statement. — madreflection
– madreflection, Commented Oct 23, 2020 at 6:11

Luis Lavieri · Accepted Answer · 2021-07-23 20:48:28Z

0

Ok I figured it out. This post helped me a lot. Here's the answer for others.

//transaction is needed to keep unnamed portal 1
var transaction = npgsqlConnection.BeginTransaction();

//create the command and add all the parameters, command type, etc 

...

npgsqlCommand.CommandType = CommandType.Text; 

npgsqlCommand.CommandText = "call your_procedure(:parameters)";

//execute the original procedure with ExecuteNonQuery
npgsqlCommand.ExecuteNonQuery();

//change the command text and execute the reader
npgsqlCommand.CommandText = "fetch all in \"<unnamed portal 1>\"";

var npgsqlDataReader = npgsqlCommand.ExecuteReader();

while (reader.Read())
{  
    // do whatever is needed to extract the data
}

//clean up
transaction.Commit();
npgsqlConnection.Close();

answered Jul 23, 2021 at 20:48

Luis Lavieri

4,1396 gold badges41 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to call PostgreSQL 13 stored procedure (no function) with cursor INOUT parameter from Npgsql 4.1.5.0 in C#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related