Hi,

this is possible a very common question, but searching the forums and reading through the documentation still left me kind of clueless of how to accomplish the following scenario:

i have two tables:

theese translate to "MainCategories" and "SubCatergories".

I further have the following sproc, that returns a joined resultset from theese tables:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spG_LeseWarengruppenMitObergruppen] AS
SELECT W.ID,
W.Bezeichnung AS 'Warengruppe',
O.Bezeichnung AS 'WarenObergruppe'
FROM tbWarengruppen W INNER JOIN tbWarenObergruppen O 
ON W.tbWarenObergruppen_ID = O.ID 

 

Now how would i got to get this resultset into a BO ? When i use the BO-Mapper, i can only select a table from the database directly.

I read the portion about custom field properties aswell and at first thought that was what i been looking for. For a quick test i wrote the following custom property field code into the bo code:

#region Custom Field Properties
[Browsable(true), BusinessFieldDisplayInEditor(), Description("WarenObergruppe"), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
public string WarenObergruppe
{
get { return this.WarenObergruppe; }
}
protected override MicroFour.StrataFrame.Business.FieldPropertyDescriptor[] GetCustomBindablePropertyDescriptors()
{
return new FieldPropertyDescriptor[] { new ReflectionPropertyDescriptor("WarenObergruppe", typeof(WarengruppenBO)) };
}
#endregion

My Impression was, that this custom field property would now appear inside the buisness object mapper along with the other fields, and that i could just rename the field "Bezeichnung" to Warengruppe aswell as "removing" the fields ID and tbWarenObergruppen_ID to match up with the resultset that my sproc returns.

however, both assumtions turned out to be wrong.

So the question remains:

how do i "map" my sproc's resultset to a BO ?

Thanks.

I think that you have a fair bit of confusion here.  First, a custom properyt is just that, a custom property and it will never appear within the BO Mapper.  You can do anything you want within that custom proeprty and even bind to it if you set it up properly and override the GetCustomBindablePropertyDescriptors(...).

Now you can load a BO many different ways for a stored procedure.  In fact, you can load a number of different BOs at once with multiple result sets from a single stored procedure using the FillMultipleDataTables method on the BUsinessLayer:

You can also load a BO via a stored procedure within a BO itself using the FullDataTable method or the FillByStoredProcedure method.

In regards to mapping the BO, you may or may not have to depending on how you are using the BO.  You can take a mapped BO and bring any data into that you want.  If the strong-typed properties do not have an underlying column within the result set, then an error would occur if you referenced that strong-typed property.  However, you can bring in any result set and schema structure that you want.  If you do not have a strong-typed property, you could just access it like this:

But if you are using this in a report or binding to it, you will need to create a strong-typed property.  There are a lot of samples that show how to do this.  I recommend downloading the StrataFlix sample as it will show you how to do every bit of this.

Philipp,

Probably I answer as an old VFP/SQL guy, and perheaps it's not the best way... but:

the result of that select would be in a VIEW on your SQL server, so you retrieve its result in a BO as if it was a table.
Consider also a TABLE FUNCTION in your database (not a SP), but AFAIK, the view is the most optimizable for SQL server.

So then could i: Drop a BO onto my form, give it a name, dont touch it in the bo mapper and use the quoted code above to fill it with the results of my stored procedure ?

And if so, would i then call the sp that saves data changes back into the db seperatly/outside of the bo, or can i tell the bo another sproc for saving data directly ?

If i change my sproc into a view, is the view then selectable within the bo mapper ?

Thanks.

Yes, your views are selectable within the BO mapper, if they are in a the schema you choose for that BO.

I don't show any table directly, they are all in dbo schema, and views are in another schema (appuser). This AppUser Schema has not GRANT VIEW DEFINITION.

And BO mapper works fine...

i see

and then for saving can u tell the bo to call a stored procedure for each changed row ? for example when using it with a maintance strip ?

Yes, if it's a compound view (with at least one join), I invoke a SP from the BO to save data (sometimes, I use a trigger INSTEAD OF), from the save method raised within a maintenance toolstrip, or from a specific code in some method...

And really, none of my tables is exposed, even for a single table, I create a view in AppUser Schema. In such a case, a simple save on the BO is all you need.

Hi Michel,

So just to make sure, the supposed way is to:

1) Map the BO to View within BO-Mapper

2) Populate/Fill the BO using a stored procedure (FillByStoredProcedure)

3) Adding new BeforeSaveEventHandler to the BO that calls a stored procedure for saving

?

 

hmm, i cannot select views in the buisness object mapper

Buisness Object Structure Source -> Database Deployment Toolkit -> Select Source

only shows the tables within the database, but not the views.

i am almost getting the impression that strataframe wants to force me into not

using views and sprocs and put all that logic back into my application.

Maybe someone from microfour could post a step-by-step description of how to to work with this. i have a view, that is basicly the sproc i had before:

Now Lets say i have a form with a maintanance strip on it. i want to get the results from my view into the bo and work together with the maintanance strip. then i want to be able to specify a stored procedure for saving changes made within the maintance form.

I would glady appreciate a step-through example of that.

Thanks !

Hi Philipp,

I don't use DDT, and I can see all the views in the schema in the BO mapper. probably a side effect af DDT, let's wait for SF gurus answer.

The logic I use (and run) is:

1. Map the BO to view in the BO mapper
   
2. Fill BO using a FillDataTable(loCmdSQL).
   In the BO base class, I've a FillDataAllFields, and a FillDataAllFieldsOrderBy, and in each BO all the specifics FillDataTable methods required for these data.
   
3. Saving by stored procedure, called from the BO by an ExecuteNonQuery, or an ExecuteScalar on a function when I need to retrieve a result from the server (# lines really saved, intermediate results returned by triggers, etc..).
   Most often, I override the BO save method, so the business logic is in the save method, and the SP on the server manages data saving and referential integrity.