Oracle Packages returning an Entity collection

Requirements
RadControls version	Q3 2009
.NET version	3.5 sp1
Visual Studio version	2008
programming language	C#
browser support	all browsers supported by RadControls

DESCRIPTION

Following these stems we will be able to execute Oracle Packages and return the Collection of entities representing the resulset.

I'm not going to post my whole project since I'm using and older version of the controls and the visual elements have changed since it was released to a better and more friendly interface, but the procedure to accomplish this task should be very similar.

1. The first thing to do is to check if in your project you already have the StoredProcedure class, OpenAccess use this class to declare all your stored procedures.
   *if thats not the case, you can take any other stored procedure, then use the OpenAccess wizard to generate its class, or
   *just copy the code below and add it to your Model project:
   
   public static class StoredProcedure

   {

   public static IQueryResult insertUpdateWorkcenterLog(IObjectScope scope, string v_NEW_WORKCENTER, string v_UPDATEBY, string v_OLD_WORKCENTER, string v_MODEL)

   {

       IQuery query = scope.GetSqlQuery("\"INSERT_UPDATE_WORKCENTER_LOG\" ?,?,?", null, "VARCHAR V_NEW_WORKCENTER,VARCHAR V_UPDATEBY,VARCHAR V_OLD_WORKCENTER, VARCHAR V_MODEL");

       IQueryResult res = query.Execute(new object[] { v_NEW_WORKCENTER, v_UPDATEBY, v_OLD_WORKCENTER, v_MODEL });

       int a = res.Count;

       return res;

   }

   }

   
   *Another option is to create a copy of the stored procedure outside the package , generate the class for it then drop it.

   
   

2. Ok now we have the base class and method to call a regular stored procedure. but we need to generate the entity that our SP will return, the easiest way is to take the select statement from your stored procedure and create a view for it in the database, then use the OpenAccess wizard to generate its class, after that you can remove that view from the database. in my case I generated the Entity called NavYieldsView then I dropped the view used to generate it.
   
   
3. Change the return value of the stored procedure method to be IEnumerable<your_entity>, in my case IEnumerable<NavYieldsView> 
   
   
4. Add the name of the package and stored procedure you want to call to the GetSqlQuery function it will look something like this:
   scope.GetSqlQuery<your_entity>("your_package.your_storedprocedure ?,?,?","your parameters");
   in my case it is :
   
   Query<NavYieldsView> query = scope.GetSqlQuery<NavYieldsView>("YIELDS_REPORT_PKG.YIELD ?,?,?,?", "DATE V_DATE_TO,DATE V_DATE_FROM,OUT.CURSOR V_CURSOR,LONGVARCHAR V_STRMODEL");
   
   

   
   

5. Make sure you change the return type to OUT.CURSOR and if you are using parameters longer than 256 declare them as LONGVARCHAR. Also make sure you are casting the return value correctly with your entity.
   
   
6. Then you need to pass these parameters to the ExecuteEnumerable method, with the exception of V_CURSOR that is not required, since the return resultset will be stored in a local Entity collection. that value can be null.
   
   var res = query.ExecuteEnumerable(new object[] { v_DATE_TO, v_DATE_FROM, null, v_STRMODEL });
7. return the res variable.
8. Add the call to this method in the ODataContextClass as follow:
   
   public IEnumerable<NavYieldsView> NavYieldsView(DateTime? v_DATE_FROM, DateTime? v_DATE_TO, string v_STRMODEL)

   {

       return StoredProcedure.getYieldByDateModel(Scope, v_DATE_FROM, v_DATE_TO, v_STRMODEL);

   }
9. now you are ready to call it from your client:
   
   var NavYieldsViewQry = _entities.NavYieldsView(v_DATE_FROM, v_DATE_TO, v_STRMODEL);

   List<NavYieldsView> yieldData = new List<NavYieldsView>();

   NavYieldsViewQry.ToList().ForEach(item =>

   {

        NavYieldsView sditem = new NavYieldsView();

        sditem.Firstfail1 = item.Firstfail1;

        sditem.Firstfail2 = item.Firstfail2;

   .

   .

   .
   

        yieldData.Add(sditem);

        });

   return yieldData;

   }