Stored Proc PostgreSQL

[[email protected]]

Hello,
I do I run a stored procedure that returns a Boolean using PGSQLCommand?

Cheers,

Alberto

[MariiaI]

If we understand you correctly, you are using LinqConnect and you want to execute the PostgreSQL stored procedure, which returns Boolean.
To do this in LinqConnect, you need to add the required stored procedure to your model and perform it using the generated method of your DataContext object, for example:
1) Create the stored procedure:

Code: Select all

CREATE OR REPLACE FUNCTION ret_bool(condition boolean)
  RETURNS boolean AS
' BEGIN  Return condition; END;'
  LANGUAGE plpgsql VOLATILE
  COST 10000;
ALTER FUNCTION ret_bool(boolean)
  OWNER TO postgres;

2) Create a new LinqConnect model and add the ret_bool(condition boolean) to it. You will get the generated code for the corresponding method like this:

Code: Select all

[Function(Name=@"public.ret_bool", IsComposable=true)]
public System.Nullable<System.Boolean> RetBool([Parameter(Name="condition", DbType="BOOLEAN")] System.Nullable<bool> condition)
        {
            IExecuteResult _RetBoolResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), condition);
            return ((System.Nullable<System.Boolean>)(_RetBoolResult.ReturnValue));
        }

3) Create the DataContext object and perform the RetBool method:

Code: Select all

PostgreDataContext context = new PostgreDataContext();
 bool results = (bool) context.RetBool(false);

For more information about using stored routines for querying data in LinqConnect, please refer here:
http://www.devart.com/linqconnect/docs/ ... tines.html
http://www.devart.com/linqconnect/docs/ ... tines.html

As for the performing stored procedures via PgSqlCommand, it can be done in the following way:

Code: Select all

PgSqlConnection connection = new PgSqlConnection(connectionString);
connection.Open();
PgSqlCommand command = new PgSqlCommand("ret_bool", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("condition", PgSqlType.Boolean);
command.Parameters["condition"].Value = true;
command.Parameters.Add("return_value", PgSqlType.Boolean);
command.Parameters["return_value"].Direction = System.Data.ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
bool result = (bool) command.Parameters["return_value"].Value;

Please refer to
http://www.devart.com/dotconnect/postgr ... dType.html

If this information doesn't help, please specify the question in more details.

[[email protected]]

Excellent thank you. That is exactly what I needed.

Now is there a way to execute the PSSQLCommand asynchronously?

V/r

Alberto

[MariiaI]

Yes, it is possible to do.
Use this code

Code: Select all

IAsyncResult myResult = command.BeginExecuteNonQuery();   
// enter you code here
command.EndExecuteNonQuery(myResult);
bool result = (bool) command.Parameters["return_value"].Value;

instead of this

Code: Select all

command.ExecuteNonQuery();
bool result = (bool) command.Parameters["return_value"].Value;

For more information please refer tohttp://www.devart.com/dotconnect/postgr ... onous.html

[[email protected]]

You guys are phenomenal!!