PgSqlParameter error using dotConnect Postgresql in Visual Studio

Question

I have a stored procedure . The input is 'id', output 'n'. But when I try to run it in Visual Studio , I have an error: The value for the output parameter 'n' is absent in the command execution result.

Here is my code:

int id = Convert.ToInt32(this.textBox1.Text);
PgSqlConnection con = new PgSqlConnection();
con.ConnectionString = Properties.Settings.Default.DBConnectionString;
PgSqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "getcountmaterials";
PgSqlParameter param = new PgSqlParameter("n", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
cmd.Parameters.Add(new PgSqlParameter("@id", id));
con.Open();
cmd.ExecuteNonQuery();
string kolvo = cmd.Parameters["n"].Value.ToString();
con.Close();
this.result.Text = kolvo;

Stored Procedure:

CREATE OR REPLACE FUNCTION public.getcountmaterials(id integer)
  RETURNS integer AS
$BODY$
declare n integer;
begin n := (select sum(count) from materials_in_warehouses
 where id_materials = id);
return n;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.getcountmaterials(integer)
  OWNER TO postgres;

Hambone · Accepted Answer · 2015-12-23T16:04:10.260

I've never used dotConnect for Pg (I am, however, a huge fan of it for Oracle), so I can't verify that I have the syntax right on all of this.

That said, I think I see your core issue. The lines between functions and "stored procedures" is somewhat blurred with Postgresql.

All you really want to do is run a select on the function above. As such, I believe the following will work. I know this would work with NpgSql, and I am hopeful it will translate properly to dotConnect:

PgSqlCommand cmd = new PgSqlCommand("select getcountmaterials(:ID)", con);
cmd.Parameters.AddWithValue("ID", id);
string kolvo = cmd.ExecuteScalar().ToString();

PgSqlParameter error using dotConnect Postgresql in Visual Studio

1 Answers1