Collaboration of Postgres for Windowns and NET

Collaboration of Postgres for Windowns and NET

Postby felmarla » Thu 24 Nov 2005 14:41

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
Last edited by felmarla on Fri 25 Nov 2005 14:48, edited 1 time in total.
felmarla
 
Posts: 5
Joined: Thu 24 Nov 2005 14:16

Postby SecureGen » Thu 24 Nov 2005 15:13

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
Devart Team
 
Posts: 133
Joined: Thu 08 Sep 2005 06:27

Postby SecureGen » Fri 25 Nov 2005 16:31

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.
SecureGen
Devart Team
 
Posts: 133
Joined: Thu 08 Sep 2005 06:27

Postby humblepatience » Fri 06 Jan 2006 20:31

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
humblepatience
 
Posts: 1
Joined: Fri 06 Jan 2006 20:26

Re: Collaboration of Postgres for Windowns and NET

Postby ssvd » Fri 31 Jan 2014 04:23

all made
ssvd
 
Posts: 4
Joined: Thu 30 Jan 2014 03:00


Return to dotConnect for PostgreSQL