By Ger Cannoll - 12/21/2011
Wondering if there is a simple way of joining a Business Object with a table, and create a new business object. Scenarion is as follows:
1. I Fill a BusinessObject
2. I process the BusinessObject and do various calculations on the Business Object
3. When Processing is finished I want to 'Join' the BusinessObject with another few Tables, to get some additional fields
(Dont particulalrly want to do the joins at 1 above as I dont want all the baggage when I am processing the BO
|
By Ivan George Borges - 12/21/2011
Hi Ger.
Processes 1 and 2 are straight forward, you have done it. Now to join fields from other tables, even though I have no clue about the situation, I guess I would create custom fields on my original BO and when ready I would enumerate the other table's records, find the appropriate place for them on the original BO and update the custom fields.
|
By Ger Cannoll - 12/21/2011
Hi Ivan. Many thanks for reply.
This has set me in the right direction, and I can create the Custome Fileds in the origina BO. Its the 'Joining' of thsi BO to the Tables I then am not sure about
.......I would enumerate the other table's records, find the appropriate place for them on the original BO and update the custom fields.
How would I go about 'Emumerating' the Tables records, and then 'Updating' the Custom fields. Is there any construct in SF where I can use one of the Fill Methods with a string like "Select * from MYBO join Table1 on MyBOKey = Table1Key".
Just thinling out loud, one way would be to select the Tables I want to Link to , into BO's , and then doing a Seek or a SeekToPrimaryKey from my original BO to the New BO's, and if the find is true, update the Fields in this way. Doing it this way seems a bit longwinded though, and perhaps slower than if a Join could be done directly to the Database ??
|
By Trent L. Taylor - 12/22/2011
First, you can enumerate BOs by calling the GetEnumerable() method:
foreach(MyBO bo in myBoInstance.GetEnumerable())
{
//-- At this point, you have the bo instance on the right record so you can access it like this
string x = bo.FieldNameHere;
//-- If you want to access a custom field that you HAVEN'T created a custom property for, then just access the current row
string y = (string)bo.CurrentRow["CustomFieldNameHere"];
}
|
By Ger Cannoll - 12/22/2011
Hi Trent. Many thanks for replying.
Ok, I see how to Enumerate. Any ideas on the most efficient strategy to get at the data from the other Tables after I have done all the Processing stuff on the Min BO.
|
By Edhy Rijo - 12/22/2011
Hi Ger,
SF Business Object are very powerful and flexible at the same time. By the default when using the Business Object Mapper (BOM) the BO will have all fields mapped to your table, but when you are filling the BO with your own SELECT statement, whatever data structure you get, it will be reflected in the datatable in the BO, so if you have a BO mapped to a Customer table with 2 fields ex: FirstName and LastName and you fill it with SELECT * FROM Customer, the BO.CurrentDataTable will have just those 2 fields, but if you fill it with a JOIN SELECT, and this has another field like CompanyName, the BO will have access to FirstName and LastName via their properlies, but the BO.CurrentDataTable will also have a column named CompanyName which you can access it by either creating a CustomFieldProperty in the BO or by using BO.CurrentRow.Item("CompanyName")
I don't quite understand what your goals is, but as you can see, there is a lot you can accomplish with the BOs.
|
By Ger Cannoll - 12/22/2011
Hi Edhy, Thanks for replying.
I'll give an example of what I am trying to do ,by way of illustration
I have a Table, with say a Million records, with StockCode,CustCode,SupplierCode,Fielda,Fieldb,Fieldc,Fileldd
I want to add Fielda+Fieldb+Fieldc+Fieldc to Get Fieldx.
Ok, I run down through the BO and do the various calculations. This data is to go out to a report, along with The Customer name, Stock Name and Supplier Name , these are all taken from additional Tables, linked to my Primarty BO by means of say a Primary Key. (In reality, there would not be 3, but perhaps 20 lookup fields that I would need to lookup from my Main Business Object from other tables using Primary Keys or similar).
I think there are proabably two ways to do this:
1. Join ALL the tables to start with, I then end up with the Million Rows, but with a lot of additional 'Bloat', that I am going to be processing as I do the calculations (i.e. traversing down the Business Object)
2. Just select out the 7 fields , as above , and add in the Lookup's to the other tables as a separate piece of code, after the procerssing has been done.
Of the 2 ways outlined above, I am concerned that perhaps Option 1 is not going to be very efficient, as I am 'Carrying' all the Bloat as I am 'processing' the BO. I would have thought that Option 2 (or something similar) would have been a more efficient way of processing as I only 'Append' in the additional data , when I need it, just at the end....and hence my initial question... whats the best and most efficient way of doing this in a StrataFrame environment
|
By Michel Levy - 12/22/2011
Hi Ger,
not sure it is the best solution, but what you ask may be done with QueryAdataset (http://www.queryadataset.com/). a very powerfull tool, which allows you to join the data of a BO with an external datatable, the result will be a datatable that you may import in a BO, or use for whatever you need.
Powerfull and cheap (200$, royalty free).
|
By Andrew Harper - 12/22/2011
Hi Edhy,
<< the BO will have access to FirstName and LastName via their properlies, but the BO.CurrentDataTable will also have a column named CompanyName which you can access it by either creating a CustomFieldProperty in the BO or by using BO.CurrentRow.Item("CompanyName") >>
I didn't realise this - thanks for the tip.
All the best for Christmas and the New Year,
Andy
|
By Edhy Rijo - 12/22/2011
Hi Andy,
You are welcome and happy holidays to you too!
|
By Edhy Rijo - 12/22/2011
Hi Ger,
Ger Cannoll (12/22/2011)
I think there are proabably two ways to do this:
1. Join ALL the tables to start with, I then end up with the Million Rows, but with a lot of additional 'Bloat', that I am going to be processing as I do the calculations (i.e. traversing down the Business Object)
2. Just select out the 7 fields , as above , and add in the Lookup's to the other tables as a separate piece of code, after the procerssing has been done.
Of the 2 ways outlined above, I am concerned that perhaps Option 1 is not going to be very efficient, as I am 'Carrying' all the Bloat as I am 'processing' the BO. I would have thought that Option 2 (or something similar) would have been a more efficient way of processing as I only 'Append' in the additional data , when I need it, just at the end....and hence my initial question... whats the best and most efficient way of doing this in a StrataFrame environment
I have some process where I handle millions of records, for those process I had to build a stored procedure, using a BO to load 500k+ records which you would need to enumerate using bo.Getenumerable would be pretty slow. On some cases I need to export some data to CSV and loading 1.5 million records into a datatable to be exported is just fine, but for you to process record by record could have a huge performance hit. MS SQL server has several nice features that allows you to process millions of records in batch to do all sort of things and then you can just load that result into a BO to show your report.
I am not a SQL expert, for that I do use the services of a MS SQL MVP developer for the last 2 years with pretty good results in all the projects I have used him, so if you need some reference I will be more than happy to introduce you to him.
To give you an example one of my process imports 800+ thousand records every week, do several calculations and matches against a lookup table which keeps growing with each imported batch, I have customers with more than 25 million records and the whole stored procedure process takes from 1 to 10 minutes to complete, when I was doing the same process via enumerating the records, the process could take from 30 minutes to 3 hours to complete, this MS SQL MVP make me a real believer of the power of MS SQL Server engine.
|
By Ger Cannoll - 12/22/2011
Hi Michel. Thanks for replying.
Had a quick look at this product and it seems to allow you to use Sql select type syntax for querying a Dataset. I could not determine if this product will allow you to Select from a Dataset table and JOIN a Database Table (that doies not exist in the dataset). I have left a question on their site. If it does this (i.e. can join say a Dataset Table to a Databse Table) it would be a grat help
|
By Michel Levy - 12/23/2011
Hi Ger,
I don't understand how you could join anything to a database table, if the code is not in a database object.
if you code where you have the dataset table, then you are out of the database, and the database tables are reachable only through a dataset. If you code where you have the table, then you are inside the database, and you need to have received the dataset as a table parameter, or a temp table to have be filled with the rows of the dataset.
|
By Ger Cannoll - 12/25/2011
Hi Edhy. Many thanks for very informative reply.
After reading it and considering it, I will difinitely have a look at using Stored Procedures now , particulalrly for any processing which involves large numbers of rows. I agree with you that this should definitely be more efficient, and it should also future proof development to some extent in that the 'processing' will be kept in the datatbase, and away from the client side, in case the futore dictates changes to the Client Side, in an ever changing and rapidly evoving IT environment
|
By Ger Cannoll - 12/25/2011
Hi Michel.
After considering the very informative replies in this thread, I am definitiely going to look into Stored Procedures and User Functions as part of a solution to Heavy processing requirements.
|
By Edhy Rijo - 12/25/2011
Ger Cannoll (12/25/2011)
Hi Edhy. Many thanks for very informative reply.
You are welcome Ger!!!
|
|