By Edhy Rijo - 3/11/2010
Hi all,
Is it possible to specified or exclude the fields to be included in a BrowseDialog?
Since a Browse Dialog is assigned to a BO I believe that it will bring all fields in the BO, how to exclude a couple of fields in the SELECT statement so only the ones needed are used in the SELECT Field1, Field2, Field3, etc.?
|
By Edhy Rijo - 3/11/2010
Forgot to mention that I know I can create a view for this but is there another way to get this done?, I prefer not to use a view and handle this by code if possible.
|
By Alex Luyando - 3/11/2010
Edhy -
Hello.
So, you can control the fields that the BD shows to the user as search fields, as well as the ones that are displayed in the resulting list once the user performs a search.
I guess I am a bit confused (which probably means I will learn something here) as to what the goal is. When the BO that is to be populated from the BD is populated, it will use only those columns that it needs, even if the BD contains additional ones (as when a view is used to override the source of the BD). Are you asking this from a performance point of view?
|
By Edhy Rijo - 3/11/2010
Alex Luyando (03/11/2010)
...When the BO that is to be populated from the BD is populated, it will use only those columns that it needs, even if the BD contains additional ones (as when a view is used to override the source of the BD). Are you asking this from a performance point of view?
Hi Alex,
Based on my testing, you are wrong, the Browse Dialog will use all the fields in the BO as part of the SELECT statement. 
When you create a BO and assign it to a BrowseDialog, all the fields in the BO will be use as part of the SELECT statement. In my case I have a Transactions table which have a couple of VARCHAR(MAX) and VARBINARY(MAX) fields, these are only used in a specific form, but the Transactions table is used in many forms, so when customer use a browse dialog, this process is slowed because those (MAX) fields are included in the main transactionsBO.
I am almost sure that the only way around this is using a View to define the specifics fields needed in most cases and then create a BO using this view. I just wanted to make sure there was no other way to avoid having to create the view and maintain it 
|
By Greg McGuffey - 3/11/2010
Aren't there some events you can use to manipulate the SQL being sent?
|
By Edhy Rijo - 3/11/2010
Greg McGuffey (03/11/2010)
Aren't there some events you can use to manipulate the SQL being sent?
I looked at the Searching event, but it does handle the "WHERE" not the fields included or excluded from the BO.
|
By Greg McGuffey - 3/11/2010
I'm not seeing anything either Edhy that would allow the fields included in the select to be manipulated.
|
By Edhy Rijo - 3/11/2010
Thanks Greg for the confirmation.
I am going with the View route and will do some testing soon, just that I need to do several changes since I want this to be used in all BDs.
|
By Edhy Rijo - 3/11/2010
Ok here my conclusion, a view should be used and in this case of refactoring the source BO for the Browse Dialog it was worthy because in the BrowseResultLayout I need to show some calculated fields which I created using Custom Field Properties and those where also slowing things down a bit, so I decided to create my BrowserDialog view with a JOIN condition to get the calculated fields in a single trip and now getting and showing the data in the Browse Dialog is much faster and responsive.
One caveat to take into consideration using this approach is that after the user selects the record to work with from the BD, I them have to search that PK in the primary BO used in the form which in this case is not the one used in the BD. I know this can be confusing sometimes but in my case it works just fine because searching a record using the BO.FillByPrimaryKey(PKValue) is much faster than looking for many records in the BD which will include those VarBinary or Varchar fields that are not used in the BD.
Hey Alex, hope all this makes sense and if not, please let us know. 
|
By Charles R Hankey - 3/11/2010
( I'm posting this without thinking it through completely so bear with me if it turns out to be complete nonsense )
For some reason I assumed that if the fill method of a BO did not return all the columns it would generate an error. This doesn't seem to be the case. I just dropped a bo on a form that has a number of Varchar fields etc and in the parentformloading just did a filldatatable to get four columns, excluding varchar, varbinary etc. and I got a cursor.
Couldn't you to that for the BD and then use a different instance of the BO with a full column fill method to get a single chosen record with fillbyprimarykey (or for that matter a set of records)
|
By Edhy Rijo - 3/11/2010
Hi Charles,
Yes you are right, but in the BD I don't see how this could be done since the SELECT statement is done somewhere internally in which I don't see how we could hook up.
I have used your trick several times specially when exporting to Excel and in fact the BO is very flexible allowing us to manipulate its datatable, but again I cannot find how this could be done from a BD.
|
By Greg McGuffey - 3/11/2010
Actually, one of the very cool things about BOs is that you can fill a BO will any data. It does not have to even be related to the table/view that was used to map the BO. Of course, things can get weird if you aren't careful when doing this. I use this in a few ways. I have fill methods that will fill the BO just for combo or list filling, filling only data actually needed in the list (name and ID sort of thing). I use it to fill BOs with denormalized data (i.e. load the name of FK record). Many times I'll create custom field properties to access these other columns. The trick is to understand that if you don't fill in a column that the BO is mapped to, if you access that column, you'll get an error.
The issue Edhy was having was that it appears that we don't have control over what is put into the select statement when using the BrowseDialog. Thus, if you have a table that includes some monster fields, the search can be slow. He's concluded that using a view is the way to go, so he can exclude those large fields and also include some denormalized fields. He then uses normal fill methods when he wants to populate the form with the result of the search (I think that's what's going on anyway).
|
By Edhy Rijo - 3/11/2010
Greg McGuffey (03/11/2010)
The issue Edhy was having was that it appears that we don't have control over what is put into the select statement when using the BrowseDialog. Thus, if you have a table that includes some monster fields, the search can be slow. He's concluded that using a view is the way to go, so he can exclude those large fields and also include some denormalized fields. He then uses normal fill methods when he wants to populate the form with the result of the search (I think that's what's going on anyway).
Wow!!! either I did explain myself correctly or you are mind reader Greg
Thanks for the clarification you are right on the spot.
Of course by using the view and my approached and then will need to modify all the forms logic in which this BD is being used, not a big deal, but I would have preffered not to do so, but at the end, those forms will also benefit of having the denormalized fields instead of the nice Custom Fields Properties which I felt in love with when learning SF.
|
By Charles R Hankey - 3/11/2010
okay, again I may not be thinking this throug, but my idea is to use two BOs, one for the BD and the one that will be filled from the result of the BD. On looking at the BD properties I guess the problem is that the BD will let you specify the BO but not the designate what fill method will be used ? ( seems a good ER that that be added to BD props to let you point to a BO Fill method or sproc )
And I take it that Override Schema won't have anything to do with this either ?
|
By Greg McGuffey - 3/11/2010
Where's Override Schema? Is this an event or property or method?
|
By Charles R Hankey - 3/11/2010
OverrideSearchTableName
OverrideSearchTableSchema
Strataframe:Browse Data properties of the Browse Dialog
|
By Charles R Hankey - 3/11/2010
Here's the source that uses it in BrowseDialogWindow
Public Function Search(ByVal WhereStatements As WhereStatementCollection, ByVal useOrNotAnd As Boolean, ByVal tokens As Dictionary(Of String, String)) As Integer
'-- Establish Locals
Dim lnReturn As Integer = 0
Dim loInfo As New QueryInformation()
'-- Build the query information
loInfo.QueryType = QueryTypeOptions.SELECT_TOP
loInfo.TopRowCount = Me._BrowseDialog.MaximumReturnedRecords
If String.IsNullOrEmpty(Me._BrowseDialog.OverrideSearchTableName) Then
loInfo.TableName = Me._BusinessObject.TableName
Else
loInfo.TableName = Me._BrowseDialog.OverrideSearchTableName
End If
If String.IsNullOrEmpty(Me._BrowseDialog.OverrideSearchTableSchema) Then
loInfo.TableSchema = Me._BusinessObject.TableSchema
Else
loInfo.TableSchema = Me._BrowseDialog.OverrideSearchTableSchema
End If
|
By Charles R Hankey - 3/11/2010
okay, on searching further here on the forum I get it - the "schema" refrerred to here is something like "dbo." not the structure of the data that will be returned.
Oh well.
So you can override the table, but that gets us back to using a view.
I'm thinking there is an ER in the works here. Shouldn't be that difficult to allow loinfo to pass in the fieldlist to the filldatatable()
|
By Edhy Rijo - 3/11/2010
Yes Charles, this refers to the database Schema not the fields, but I am sure there should be some trick to handle this other than using the view.
I am still waiting for Trent to comment on this one.
|
By Edhy Rijo - 3/11/2010
!!!Warning!!!!
For anybody using the view approach the following steps most be done so it will work properly:
1.- In the BOM you must check "Override Primary Key Specification" in the BO Properties and select your PK field or fields used or when you select a record in the listview it will not have the correct PK value in the ListViewItem.Tag property.
2.- The BD property "ReturnSelectedRecordOnly" should be set to True so the correct record is copied the one in the BO assigned to the BD.
I just spend an hour trying to find out why the selected record in the listview was not being copied to the assigned BO and I just forgot that the view had not PK field assigned in the BOM and this was causing the code in the ListView to failed without error and not finding the correct record.
|
By Greg McGuffey - 3/11/2010
Seems like an analogous method to the OverrideSearchTableName/Schema would be nice here, something like OverrideSearchSelectFields. 
|
By Charles R Hankey - 3/11/2010
The Public Function Search() in the browsedialogwindow uses the bo allfieldslist
'-- Changed TLT - 8/25/2009 to allow a view to be used and include all fields in the results list
If Not _BrowseDialog.IgnoreTableMappingAndIncludeAllFieldsInResults Then
loInfo.Fields.AddRange(Me._BusinessObject.AllFieldsList.ToArray())
Else
loInfo.Fields.Clear()
End If
'-- End of change
So it would seem if that list were exposed in the BO properties on the instance level that would pretty much take care of it and be useful in other areas as well. Present all the fields in a picker - either uncheck to exclude or check the fields to exclude, that sort of thing.
Alternately, a property that is a list of fields that if it is not blank would be used instead of allfieldslist.
|
By Trent L. Taylor - 3/12/2010
OK....let me jump in here. First, there is already a way to do both. The BrowseDialog uses a QueryInfo class that gets passed all the way down to the DAL which in a query class written specifically for this class that is independent of any database on the back end. This is handled in the DbDataSourceItem which interprets the contents of the QueryInfo class and then properly builds the query necessary for the results. I have just made a change that will appear in the next update that fully exposes this property immediately before executing the query. It will be called BeforeSearchExecuted and it will give you the full QueryInfo instance that is to be used immediately before it is actually used. This is easier than using the Tokens that allows you to inject logic into the QueryInfo class through the Searching event.
When you get this update and if you elect to manipulate the QueryInfo class, you will just clear out the Fields collection on the QueryInfo and add in the fields that you want to be returned.
Now that this particular argument has been resolved....here is my opinion. You are better off creating a view anyway. It will be easier and was the reason it was introduced in the first place. Let's quickly discuss why the view or alternate table option was added. We had introduced the tokens within the Searching event that manipulated the QueryInfo class. However, there were still some issues that could not be fully realized until the underlying query was accessible. This was a need that came from our medical software. We had this very need, and we were also in control of the framework and knew that we could expose this QueryInfo class and manipulate it as well. Obviously we determined that the cleanest, fastest, and most scalable option was to create a view that could be injected as the query source which ultimately opens up the world in regards to result sets coming back in a browse dialog.
At any rate, you can do it either way...but I still would go the view route in this case. 
|
By Edhy Rijo - 3/12/2010
Hi Trent,
Thanks for the explanation and for the new event to handle this kind of situation even though I agree with you that at the end, a view will serve much better and will be more flexible to work with.
|