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

Question

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

Are you actually executing the command? I can't see that in your code... — mlinth
– mlinth, Commented Jul 6, 2015 at 8:08
with command do you mean the function? yes i do, but i think that is not the problem? DbCommand command = connection.CreateCommand(); command.CommandText = "t2"; command.CommandType = CommandType.StoredProcedure; — Spedo De La Rossa
– Spedo De La Rossa, Commented Jul 6, 2015 at 8:15
I mean your "command" object. You need to run one of the methods starting with "execute" (sorry, can't remember which one off-hand) after opening the connection; that's the bit that actually runs the query on the database... — mlinth
– mlinth, Commented Jul 6, 2015 at 8:31
oh you mean command.ExecuteNonQuery();? Just added it, tried it alrdy , but it didn't work either. — Spedo De La Rossa
– Spedo De La Rossa, Commented Jul 6, 2015 at 8:35

Patrick · Accepted Answer · 2015-07-06 08:46:06Z

8

Instead of

command.ExecuteNonQuery();

you should call

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

edited Jul 6, 2015 at 8:46

answered Jul 6, 2015 at 8:43

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

5 Comments

Spedo De La Rossa Over a year ago

Hi Patrick, doesn't work for me. Console.WriteLine(command.Parameters["sum"].Value); stays empty.

Patrick Over a year ago

ExecuteScalar() returns the result of the function call. You should not look at the parameter here, but at the result of the call.

Patrick Over a year ago

I am not entirely sure about this, but Npgsql may not support the use of PG OUT parameters. In PG, a declared OUT parameter is just a notational variation of the RETURNS clause.

Spedo De La Rossa Over a year ago

ok, well thats true, i have to look at the result. thank you, thats working. so the failure was use ExecuteScalar() instead of ExecuteNonQuery(). THANKS!

Spedo De La Rossa Over a year ago

Since I am new, how do i do that? Just clicked the hook?

Alex Zhukovskiy · Accepted Answer · 2015-07-06 08:35:27Z

0

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

answered Jul 6, 2015 at 8:35

Alex Zhukovskiy

10.1k12 gold badges87 silver badges159 bronze badges

3 Comments

Spedo De La Rossa Over a year ago

Hi Alex, sorry I just took the wrong code, because I tried and tried and disabled the ExecuteNonQuery(); I had it. When i tried your code, i get an NullReferenceException with int result = (int)command.Parameters["sum"].Value;

Spedo De La Rossa Over a year ago

Thank you Alex. Patrick solved it, it was ExecuteScalar instead of ExecuteNonQuery. Anyway, thank you. ;)

Alex Zhukovskiy Over a year ago

I thinked about it, but removed ExecuteScallar code because it doesn't work always with output parameters :) It's good that your data provider support this feature. But do not very count on it

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related