CODEWITHSUNDEEP
c#postgresqlplpgsql
Spedo De La Rossa
Spedo De La Rossa

Reputation: 127

How to return the result of a postgresql function in c#? Console output empty

I have the problem that i have a function in postgresql that calculates two integer and should return the result to the c# (npgsql) conosle and i don't know where my mistake is, because the debugger doesn't say anything to me which is helpful.

so first of all the code of c# and of the function.

                ...
            cmd.Parameters["x"].Value = 20;
            cmd.Parameters["y"].Value = 22;
            connection.Open();

            if (connection.State == System.Data.ConnectionState.Open) {
                //Console.WriteLine(cmd.Parameters["x"].Value);
                command.ExecuteNonQuery();
                Console.WriteLine(cmd.Parameters["sum"].Value);

            }

and now the code of the DB:

CREATE OR REPLACE FUNCTION t2(
    IN x integer,
    IN y integer,
    OUT sum integer)
  RETURNS integer AS
$BODY$BEGIN
    sum := x + y;
    INSERT INTO  t2 (x, y, sum) values (x, y, sum);
END

So when i try to run it,

Console.WriteLine(cmd.Parameters["sum"].Value);

will be empty and the ["sum"].Value is NULL. What am I doing wrong? Am I right, that when I say that "sum" is an OUT variable, I do not need a return?

Please help.

SOLVED, thank you to all! @Patrick gave me the right answer: use ExecuteScalar() instead of ExecuteNonQuery()

Upvotes: 9

Views: 6702

Answers (2)

Patrick
Patrick

Reputation: 32199

Instead of

command.ExecuteNonQuery();

you should call

Object res = command.ExecuteScalar();
Console.WriteLine(res);

Upvotes: 8

Alex Zhukovskiy
Alex Zhukovskiy

Reputation: 10015

Did you try to run this code, for example, command.ExecuteNonQuery() ? Because you're reading a value without actually executing the query.

connection.Open();
command.ExecuteNonQuery();
int result = (int) cmd.Parameters["sum"].Value;
Console.WriteLine(result);

Upvotes: 0

Related Questions