Collaboration of Postgres for Windowns and NET

[felmarla]

Tenga you a coordial greeting of my part. The following mail with the purpose of asking for an aid to them, I need to know since I can make to execute functions from NET.Le agradeceria its collaboration. I have the following metodos in the class Thanks.as it is the route of the examples of functions of poststoneware but in windowns

CREATE OR REPLACE FUNCTION get_codigo_entidad("varchar")
RETURNS "numeric" AS
$BODY$
declare
sql_statement Varchar(200);
pg_codigo Numeric;
in_nombre alias for $1;
begin
select ent_codigo into pg_codigo from entidad where lower(ent_nombre) like lower(in_nombre);
return pg_codigo;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

public int getRegistroDecimal(string procedimiento, ref decimal salida, params Parametro[] parametros)
{
//PgSqlParameter pg_codigo;
pgcoman.CommandText = procedimiento;
pgcoman.CommandType = CommandType.StoredProcedure;

foreach (Parametro param in parametros)
pgcoman.Parameters.Add(param.Nombre, param.Valor);

PgSqlParameter pgp = new PgSqlParameter("pg_codigo", PgSqlType.Numeric);
//pg_codigo=pgcoman.Parameters.Add("pg_salida",PgSqlType.Numeric);
//pg_codigo.Direction=ParameterDirection.ReturnValue;
pgcoman.Parameters.Add(pgp).Direction = ParameterDirection.ReturnValue;
objeto = salida;
res = this.ejecutarScalar(ref objeto);
salida = (decimal) pgp.Value;
return res;
}

private int ejecutarScalar(ref object obj)
{
try
{
this.pgconec.Open();
obj = pgcoman.ExecuteScalar();
this.pgconec.Close();
return 0; //no hubo error
}
catch (SqlException serr)
{
this.pgconec.Close();
this.error = serr.Message.ToString();
return serr.Number; //retorna el número de error oracle sql
}
catch (Exception ex)
{
this.pgconec.Close();
this.error = ex.Message.ToString();
return -1; //retorna el número de error del sistema
}
}
decimal codigoEnt = 0;
conx.getRegistroDecimal("get_codigo_entidad", ref codigoEnt,new Parametro("valor_nombre", "Mantenimiento"));
msgMensaje.ShowMessage(codigoEnt.ToString());
error
ex {"Parameter name is missing.\r\nParameter name: pg_codigo" } System.Exception

[SecureGen]

Installation of PostgreSQLDirect .NET contains a set of sample projects that demonstrates how to use Postgre SQL server functionality in your application.
Please refer to our StoredProc demo project. It contains all necessary functionality. You can find it in the /Samples/StoredProc folder.

[SecureGen]

Try to use the following code:

try{
PgSqlConnection Conn = new PgSqlConnection(connectionString);
try{
Conn.Open();
PgSqlCommand cmd = Conn.CreateCommand();
cmd.CommandText = "test.get_codigo_entidad";
cmd.CommandType = CommandType.StoredProcedure;

PgSqlParameter p = cmd.CreateParameter();
p.ParameterName = "p1";
p.DbType = DbType.AnsiString;
p.Direction = ParameterDirection.Input;
p.Value = "";
cmd.Parameters.Add(p);

p = cmd.CreateParameter();
p.ParameterName = "return_value";
p.Direction = ParameterDirection.ReturnValue;
p.DbType = DbType.Decimal;
cmd.Parameters.Add(p);

cmd.ExecuteNonQuery();

p = cmd.Parameters["return_value"];
object res = p.Value;
if(res == null)
return;
}
finally{
Conn.Close();
}
}
catch(Exception ex){
MessageBox.Show(ex.Message);
}

If function returns value it can be accessed thru special "return_value" parameter.

[humblepatience]

I re-implemented my use of my stored procedure with the code you listed, and I got the same error message
Unfortunately I had to edit it because in your code it goes straight to the "finally" after executing my command.ExecuteNonQuery() - probably because of the error, but I wanted to see if It could still return a value and I had to do that within the same scope as the definintion of the command, if you know what I mean.
{"Parameter name is missing.\r\nParameter name: result1" }

Here's my code

Code: Select all

			try
			{
				CoreLab.PostgreSql.PgSqlConnection connection = new CoreLab.PostgreSql.PgSqlConnection(myConnString);
			
					connection.Open();
					CoreLab.PostgreSql.PgSqlCommand command = connection.CreateCommand();
					command.CommandText = "IsUAAvailable";
					command.CommandType = CommandType.StoredProcedure;
			
		
					CoreLab.PostgreSql.PgSqlParameter lon = command.CreateParameter();
					lon.ParameterName = "lon1";
					lon.DbType = DbType.Double;
					lon.Direction=ParameterDirection.Input;
					lon.Value = txtLon.Text;
					command.Parameters.Add(lon);
			
					CoreLab.PostgreSql.PgSqlParameter lat = command.CreateParameter();
					lat.ParameterName = "lat1";
					lat.DbType = DbType.Double;
					lat.Direction=ParameterDirection.Input;
					lat.Value = txtLat.Text;
					command.Parameters.Add(lat);
			
					CoreLab.PostgreSql.PgSqlParameter result = command.CreateParameter();
					result.ParameterName = "result1";
					result.DbType = DbType.Int32;
					result.Direction=ParameterDirection.ReturnValue;
					command.Parameters.Add(result);
			
					command.ExecuteNonQuery();
					
					//int hello = Convert.ToInt32(result.Value);
					//txtResult1.Text = Convert.ToString(command.Parameters["result1"].Value);
					
		
			}
			catch(Exception ex)
			{
				Console.WriteLine(ex.Message);

			}

Errror = Parameter name is missing.\r\nParameter name: result1

[ssvd]

all made