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.

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.

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.

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 ?

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.

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

?

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 !

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.

kind of a self bump here. would really appreciate a step-through example of my scenario above by someone from microfour.

additionally, since i need to select "SQL Server" and not "DDT" in the BOM to map views, it requires me to enter the connection details there. i was hoping it would rather get that from the datasource i set up in code, since off course the connection details on my workplace are not the same as at the client, and i am not sure where the deployed programm would try to pull it from.

i am reading theese from an xml file at runtime and adding the datasource in the code.

So the issue here isn't the data retrieval, but the saving.  At this point, you would just ensure that all expected columns in the BO exist prior to the save (i.e. if your data retrieval will exclude certain fields, then you will need to set the FieldsToExcludeInUpdate/Insert on the BO).  At this point, you can then update the CRUD settings of the BO to call the desired stored procedure.

If you are really trying to do something crazy on the save, then just override the Save method of that BO and take the contents and supply the information manually to the sproc in question and create an SqlCommand (or whatever database you are using) and then call an ExecuteNonQuery.  There are a lot of ways to go about this....but this should at least give you some ideas.

thanks, this gives me an idea of the saving.

could you provide a pointer towards "enabling views" in the ddt requiring to select SQL Server instead of DDT, asof:

Thanks !

umm, so what you are saying is that the BOM/BO's does/do not support views ?

if so, that comes kind of unexpected. from my perspective views & stored procedures are common practice in working with sql databases.

NO!!!!! That is not what I am saying at all!!!!   I am saying that when you are mapping a BO to a view that the DDT, though it will deploy views just fine, doesn't regex out and parse all of the individual field names to which the BO would be getting mapped.  That is why, when mapping a BO through the BO Mapper to a view you have to pull the schema from a view already associated with a server.  So if you are using the DDT, deploy the view first, then map the BO to the view by pulling the schema from teh deployed SQL Server database.

tehe, glad that was not what you were saying ^^

i understood, that in order to map to views, i need to select "SQL Server" as Datasource within the BOM, for the reasons you explained.

i think right now, my main question in regards to this is:

When i select "SQL Server" as source in the BOM, it will ask me for the connection details. I could off course just enter them, but then what will happen when i deploy the application at a customer, who has different connection details to his sql server.

i read the connection details from an xml file, and add it as datasource through the code.

ah now i get what the mapper actually does. ^^

Thanks