locked
[EntLib 2.0][DAAB]results of calling functions RRS feed

  • Question

  • User779908941 posted

     Hi,

     I have a very simple function in Oracle :

     

    function portail_select
    return varchar
     is
     titre_out varchar(50);
    begin
    select titre into titre_out from portail_infos where id=1;
    return titre_out;
    end;

     
     and I try to call it and have the result.

    I have tried :

     

    Database db = DatabaseFactory.CreateDatabase("Oracle");
    DbCommand dbCommand=db.GetStoredProcCommand("Devstage10g.portail_select");
     string results = null;
                
    db.DiscoverParameters(dbCommand);
    db.AddOutParameter(dbCommand, "titre_out", DbType.String, 50);
    db.ExecuteNonQuery(dbCommand);
    results = string.Format("{0}",db.GetParameterValue(dbCommand, "titre_out"));

      

     

     

    Database db = DatabaseFactory.CreateDatabase("Oracle");
    DbCommand dbCommand=db.GetStoredProcCommand("Devstage10g.portail_select");
     string results = null;
    using (IDataReader reader = db.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        results= reader.GetString(0);
                    }
                }

      

    or

     results = (string)db.ExecuteScalar(dbCommand);  

     

    But each time I have an error.

     

    Could you help me to find the good way please.

     Thank you



     

    Tuesday, June 26, 2007 6:54 AM

All replies

  • User-294030300 posted

    Hey,

    GetStoredProcCommand is for stored procedures, and yours is a function.  You could try creating a stored procedure to call your function and that would work.  The other thing you may want to try first is using GetSqlStringCommand and see if it works that way.

    Tuesday, June 26, 2007 7:59 AM
  • User779908941 posted

     hey,

    thanks for yr response.

    I can't use the encapsulation of function in procedure because I have many functions.

    I have tried with "GetSqlStringCommand" but doesn't work.
     


     

    Wednesday, June 27, 2007 4:45 AM
  • User779908941 posted

     Oki I have found a solution :

     For Oracle :

     

    Database db = DatabaseFactory.CreateDatabase("Oracle");
    
    string query = "select function_name(:param1,:param2) from dual";
    
    DbCommand dbCommand = db.GetSqlStringCommand(query);
    db.AddInParameter(dbCommand, ":param1", DbType.Int32, Convert.ToInt32(param1));
    db.AddInParameter(dbCommand, ":param2", DbType.Int32, Convert.ToInt32(param2));
    using (IDataReader reader = db.ExecuteReader(dbCommand))
    {.
     .
     .
    }

     

    For a SQLServer Database :

     

    Database db = DatabaseFactory.CreateDatabase("SQLServer");
    string query = "select function_name(@param1)";
    DbCommand dbCommand = db.GetSqlStringCommand(query);
               
    db.AddInParameter(dbCommand, "@param1", DbType.Int32, Convert.ToInt32(param1));
    using (IDataReader reader = db.ExecuteReader(dbCommand))
    {.
     .
     .
    }
     
     
    Wednesday, June 27, 2007 12:11 PM