New Posts New Posts RSS Feed: Aggregate Data via Stored Procedures
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Aggregate Data via Stored Procedures
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
Linguinut View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 394 		Post Options Post Options   Quote Linguinut Quote  Post ReplyReply Direct Link To This Post Topic: Aggregate Data via Stored Procedures
    Posted: 14-Jun-2007 at 11:20am
I have a stored procedure that lists the last ship date for a part if the ship date is older than a given date.  It is a so that returns a group of rows that do not reflect any given entity...it is a unique, yet consistent, ad hoc group of fields and rows.
 
Is it possible to use DevForce to create the sp entity, then run the query as needed?  If I use the object mapper, I am greeted by an error stating that there is no schema for the sp.  As a result, I am unable to use the sp.  How do I get DevForce to "see" the schema?  Do I have to build a schema into the sp somehow?  Should I not use DevForce in these situations?
 
Thanks for your help,
Bill
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715 		Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 14-Jun-2007 at 1:51pm
 believe that the root cause here is that you are using temporary tables which is confusing to the Object Mapper.
 
Fortunately, there is an easy workaround:
 
Just comment out the relevant sections of the stored pocedure that create and reference the temporary table(s), then use the OM to create the business object.  Finally, you can go back to the database, and take out your comments.  Everything will work as it should.  Of course, every time you go back to the OM to edit/create stored procedure-backed business objects, you must again comment out the sections that acccess the temporary tables.
 
An alternative workaround is to use any "dummy" stored procedure that has the correct input and output columns. After mapping has succeeded, replace the "dummy" stored procedure with the real one. 
 
Back to Top
Linguinut View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 394 		Post Options Post Options   Quote Linguinut Quote  Post ReplyReply Direct Link To This Post Posted: 14-Jun-2007 at 2:51pm
Great, David!
 
I created a template stored procedure that created the schema for me.  Worked beautifully.  I have the schema to work with, now, and compile into the model project.
 
Now, the query...here is what I did (InventoryAging is the object that points to the stored procedure):
 
StoredProcRdbQuery aQuery = new InventoryAging.StoredProcRdbQuery(dtmAgingDate.Value);
InventoryAging[] currAging = (InventoryAging[])mPersMgr.GetEntities(aQuery);
mInvAgingBS.DataSource = currAging;
 
I get a hiccup at the second line shown here.  It says the following:
Unable to load type Model.InventoryAgingDataRow+StoredProcRdbQuery required for deserialization.
 
I thought I would follow the code that was outlined in the video, but I must still be doing something wrong.  Any more tricks?
 
Thanks,
Bill
Back to Top
Linguinut View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 394 		Post Options Post Options   Quote Linguinut Quote  Post ReplyReply Direct Link To This Post Posted: 14-Jun-2007 at 2:59pm
Silly me.  Wacko
 
I forgot to update the BOS.
Back to Top
Linguinut View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 394 		Post Options Post Options   Quote Linguinut Quote  Post ReplyReply Direct Link To This Post Posted: 14-Jun-2007 at 5:05pm
Ok...I am a bit stumped on a (I think) related matter.  The datagridview (dgv) is populating beautifully with the data derived from the stored procedure.  Awesome!  Three lines of manual code pull it all off.  Now, the dgv will not sort on any column.  I've checked all of the properties of the form, dgv and the individual columns and I do not see any constraint on the sorting of the grid columns.  Is there some kind of limitation built-in to the DevForce sp query?  This is probably another one of those "duh!" moments.
Back to Top
Linguinut View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 394 		Post Options Post Options   Quote Linguinut Quote  Post ReplyReply Direct Link To This Post Posted: 14-Jun-2007 at 5:23pm
Here's the solution to that problem.  Initially, I followed the video on the StoredProcRdbQuery with the following code entry:

InventoryAging[] currAging = (InventoryAging[])mPersMgr.GetEntities(aQuery);

Now, this works, but it creates a nonsortable list.  So, I adjusted the code to this:
 
private EntityList<InventoryAging> mInvAging; //declared as a private variable earlier in the class code
mInvAging = mPersMgr.GetEntities<InventoryAging>(aQuery);
 
And the list can now be sorted.  Simple enough.
 
Bill
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down