Best practices for retrieving lookup values for foreign keys

StrataFrame Forum

Best practices for retrieving lookup values for foreign keys

http://forum.strataframe.net/Topic14580.aspx

By Larry Tucker - 2/27/2008

Hi,

I'm trying to figure out the best way to handle descriptive lookup values associated with foreign keys on a business object. I've read some of the past discussions (e.g. http://forum.strataframe.net/Topic12507-7-1.aspx?Highlight=RowPopulating+browse) and it is looking like custom field properties may be the way to go in many cases.

For example, I have a staffBO for all teachers in a school district. Each record has an FK_School foreign key pointing to the teacher's school. On my StaffMaintenanceForm Browse Dialog I want the school title (not FK) to show in the results column. So I added a cSchoolTitle custom field to StaffBO as shown below (using a call to SchoolBO.FillByPrimaryKey()).

This works fine, but I'm concerned about the speed and network demands of repeatedly doing a FillByPrimaryKey() each time a staff record is retrieved. I'm also trying to better understand what is going on behind the scenes, so please bear with me...

Does the FillByPrimaryKey() actually query the backend School table for each staff record?... or is some fancy buffering going on? I know doing this for a few hundred staff records may be trivial, but other tables with similar lookup requirements may have 10,000 records.

Is there a better way to just pull the entire School table into a local BO one time (using FillAll()) and look up the school titles there? Would this alternate approach be preferable for small lookup tables?

I've considered using a multi-table BO (based on SQL views that have joins to needed lookup tables) but was stymied by how to make these updatable.

Thanks in advance,

Larry

Public ReadOnly Property [CSCHOOLTITLE]() As System.String
Get
Dim schoolbo As New SchoolBO
schoolbo.FillByPrimaryKey(Me.FK_SCHOOL)
If schoolbo.Count = 1 Then
Return schoolbo.CTITLE
Else
Return String.Empty
End If
schoolbo.Dispose()
End Get
End Property

By Peter Denton - 2/27/2008

Larry,

FillByPrimaryKey will just fill a BOs datatable with a single row, the hard work is done by the SQL server which with a primary key can go straight to the row very efficiently.

You could always make the schoolbo global, populate it once with all records, and then instead of FillByPrimaryKey use NavigateToPrimaryKey, but this is only going to be better for small tables where every row is likely to be accessed many times.

Peter

By Larry Tucker - 2/27/2008

Peter,

This is exactly the kind of alternate approach I was wondering about. How do you make a BO global?

Larry

By Peter Denton - 2/27/2008

Larry,

To make a BO Global, there are a couple of options, drop it on your form, or declare it in the global section of your form. You then have to populate it, either within the bo itself in the parent form loading event handler, or within the form in the form loading event handler.

Another thing to note is that if you are just going to use a bo for a lookup, you only need the Primary key, and whatever you want to display, anyhting else just uses extra memory for no good purpose. So you might want a lookup BO that is different to your maintenance/update/transactional BO.

Peter

By Larry Tucker - 2/28/2008

Thanks Peter,

I will try this. Thinking about it, my lookup based on the FillByPrimaryKey() is probably more than efficient enough. It also has the benefit of immediately incorporating any new or edited lookup records that some other user may have created while the original user was active. (The retrieved global BO would not "know" about these unless it was requeried).

Larry

By Trent L. Taylor - 2/28/2008

Filling the entire row is not a good idea in this scenario. You can take two approaches. First, I would put the load on SQL Server and include the field in the query versus making another trip to the server. The fewer trips the better, and this can always be included in your query if necessary:

SELECT Staff.*, SchoolTitles.TitleName FROM Staff INNER JOIN SchoolTitles ON Staff.ForeignKey = SchoolTitles.PrimaryKey

Using this, you can create a custom property in the BO that uses the TitleName (which will already be retrieved from the initial query:

Public Readonly Property SchoolTitleName As String
   Get
      Return CType(me.CurrentRow.Item("TitleName"), String)
   End Get
End Property

The other way, which you may need to do within the BrowseResults, is to use the PopulatedThroughEvent and RowPopulating event (lots of samples and docs on this). It is the same as a ListView. In this case, you would want to use a Scalar method to retrieve the value...MUCH faster than pulling a full query as there is no TDS (tabular data stream) when pulling back a scalar method:

Public Function GetSchoolTitleName(Byval SchoolPkValye as Integer)
    Dim cmd As New SqlCommand("SELECT TitleName FROM SchoolTitles WHERE TitlePrimaryKey = @titlePrimaryKey")
    cmd.Parameters.AddWithValue("@titlePrimaryKey",SchoolPkValue).SqlDbType = SqlDbType.Int
    Return Me.ExecuteScalar(cmd)
End Function

By Larry Tucker - 2/29/2008

Trent,

First, I would put the load on SQL Server and include the field in the query versus making another trip to the server. The fewer trips the better, and this can always be included in your query if necessary:
SELECT Staff.*, SchoolTitles.TitleName FROM Staff INNER JOIN SchoolTitles ON Staff.ForeignKey = SchoolTitles.PrimaryKey

This is a very nice way to solve the lookup problem efficiently, with one trip to the server no matter how many staff records are pulled. It is like using a multi-table view for my BO (joining staff and schools), but without the headache of dealing with an updatable SQL view.

Thanks very much,

Larry

By Trent L. Taylor - 2/29/2008

Thanks very much,

Glad to help Smile

By Larry Tucker - 7/1/2008

SELECT Staff.*, SchoolTitles.TitleName FROM Staff INNER JOIN SchoolTitles ON Staff.ForeignKey = SchoolTitles.PrimaryKey

Using this, you can create a custom property in the BO that uses the TitleName (which will already be retrieved from the initial query:

Public Readonly Property SchoolTitleName As String
   Get
      Return CType(me.CurrentRow.Item("TitleName"), String)
   End Get
End Property

Trent, I'm getting back to testing this approach to custom field properties and have a few questions/problems. I added a new field to a BO.Fill method and added a custom property as below in my ClaimBO:

Public Sub FillAll() 
Me.FillDataTable("Select Claim.*, ses_Date from Claim left outer join Session " & _
"on Claim.cla_FK_Session = Session.ses_PK")
End Sub
''' <summary>
''' Add looked up Session Date from Session table
''' </summary>
<Browsable(False), _
BusinessFieldDisplayInEditor(), _
Description("Session Date"), _
DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _
Public ReadOnly Property ses_Date() As System.DateTime
Get
Return CType(Me.CurrentRow.Item("ses_Date"), Date)
End Get
End Property
Protected Overrides Function GetCustomBindablePropertyDescriptors() As MicroFour.StrataFrame.Business.FieldPropertyDescriptor()
Return New FieldPropertyDescriptor() {New ReflectionPropertyDescriptor("ses_Date", Me.GetType())}
End Function

Unfortunately I get an error when adding a new record on my ClaimSFMaintentanceForm after loading the form with my ClaimBO.FillAll() method:

KeyNotFoundException
The specified key was not present in the dictionary.

Source : MicroFour StrataFrame Base

If I don't load the ClaimSFMaintanenceForm with any records and press New, I get a similar error:

BusinessLayerException
An error occurred while refreshing the data from field 'ClaimBO.ses_Date' to property 'Text' on control 'Textbox5.'
TargetInvocationException
Exception has been thrown by the target of an invocation.
ArgumentException
Column 'ses_Date' does not belong to table Claim.

Source : MicroFour StrataFrame Business

Stack Trace:
   at System.Data.DataRow.GetDataColumn(String columnName)
   at System.Data.DataRow.get_Item(String columnName)
   at TBNet.ClaimBO.get_ses_Date() in E:\TBNet\TBNet\BOs\ClaimBO.vb:line 85
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
....

The second error says "ses_Date does not belong to table Claim"... which is true unless my FillAll() method is used to fill the underlying table. I assume this has something to do the with "dictionary" mentioned in the first error... but I need some additional guidance. My impression is that I need to do more to fully inform the default SF methods / engine about this field that doesn't exist on the source Claim table in the database.

TIA,

Larry

By Edhy Rijo - 7/1/2008

Larry Tucker (07/01/2008)

My impression is that I need to do more to fully inform the default SF methods / engine about this field that doesn't exist on the source Claim table in the database.

Hi Larry,

I am not at a computer with SF installed, but in the BO instance in your form, there is a property where you can tell which fields should be ignored for update. Sorry I don't remember the name of the property either, but that may be what you need.

By Larry Tucker - 7/1/2008

Edhy,

in the BO instance in your form, there is a property where you can tell which fields should be ignored for update

Thanks for jumping in an making the suggestion. I found the properties (FieldsToExcludeFromInsert and FieldsToExcludeFromUpdate) and filled each with "ses_Date"... but no improvement. Still thanks for pointing these out.

I think I'm interpeting Trent's original suggestion in an overly optomistic way. By adding the extra field to my FillAll() method, this only temporarily adds the lookup field (ses_Date) to my ClaimBO.CurrentDataTable. Even though the field property is defined in the BO, most other times the underlying datatable is filled by built-in SF methods, it will not have this field. I think that is why the Add method is failing.

Back in VFP I used to used updatable views a lot for this purpose, and would mark only the fields associated with the core table as "Send Updates". So in this case, I could work with my Claims table (and see the associated ses_date as a readonly lookup field that is not updated).

I can go back to using scalar calls to fill my custom field properties, but it seems like a lot of round trips to the server. Filling all at once using more complex Fill method calls (with joined lookup tables) seeme like a neat solution.

Larry

By Trent L. Taylor - 7/1/2008

Actually the problem here may be that when you perform a query the ses_date field is there. But if you have not performed a query and just add a new record, the ses_date field is not within the column collection. In this case, you need to manually add the column to the CurrentDataTAble.COlumns collection so that your code can reference this field without error.

Since you are getting this error, I would imagine that you are pulling from the strong-typed field property either via binding or in code without having performed a query...and thus the column doesn't exist within the BO. I have a shared method in our medical software that I use called EnsureColumnExists that I will place in different places depending on what I am trying to accomplish that accepts a BO, field name, and field type and then makes sure that the column always exists so that this error doesn't creep up on you.

By Edhy Rijo - 7/1/2008

Larry,

Here you can find the shared method Trent is talking about: http://forum.strataframe.net/FindPost16437.aspx

By Trent L. Taylor - 7/1/2008

Thanks, Edhy Wink

By Larry Tucker - 7/1/2008

Trent,

Thanks for the explanation and Edhy, thanks for the reference. I'll give it a try.

Larry

By Larry Tucker - 7/1/2008

I think I'm close. Adding the EnsureColumnExists() call prevents the "Column 'ses_Date' does not belong to table Claim" error as it should.

However, when I add a new record to ClaimBO using an SFMaintenance form, even after making sure the column exists, I am still getting the following:

KeyNotFoundException
The specified key was not present in the dictionary.

Source : MicroFour StrataFrame Base

Stack Trace:
   at MicroFour.StrataFrame.Data.DataBasics.GetFieldFromDictionaryOrdinalKey[T](Dictionary`2 dict, String key)
   at MicroFour.StrataFrame.Data.DataBasics.InitializeNewRow(DataRow NewDataRow, StringCollection IgnoredFields, Dictionary`2 FieldNativeTypes)
   at MicroFour.StrataFrame.Business.BusinessLayer.NewRow()
   at MicroFour.StrataFrame.Business.BusinessLayer.Add(Boolean CheckSecurity)
   at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Add(Boolean CheckSecurity)
   at MicroFour.StrataFrame.UI.Windows.Forms.MaintenanceFormToolStrip.cmdNew_Click(Object sender, EventArgs e)
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
   at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Any suggestions, or comments about the "dictionary" process that seems to an issue here, would be appreciated.

Larry

By Trent L. Taylor - 7/2/2008

Do you have a property descriptor defined for the property ses_date (or whichever) that you are trying to ensure the column exists for? I am trying to understand your BO code and the little snippet of the error could come from more than one place, technically.

By Larry Tucker - 7/2/2008

Trent L. Taylor (07/02/2008)

Do you have a property descriptor defined for the property ses_date (or whichever) that you are trying to ensure the column exists for? I am trying to understand your BO code and the little snippet of the error could come from more than one place, technically.

Here is the ClaimBO code for the property descriptor"

''' <summary>
''' This provides a property descriptor that is used for reflection. If you have more than
''' one custom property, then create another entry in the array that is being returned.
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Protected Overrides Function GetCustomBindablePropertyDescriptors() As MicroFour.StrataFrame.Business.FieldPropertyDescriptor()
Return New FieldPropertyDescriptor() {New ReflectionPropertyDescriptor("ses_Date", Me.GetType())}
End Function

Thanks,

Larry

By Trent L. Taylor - 7/2/2008

The problem is that this was a date field and the framework was looking to see if the value was a SmalLDateTime as this is treated differently when initializting the row. We have made a change to the framework so that this will not produce an error in the future. I will try and post a new beta build later in the day.

By Larry Tucker - 7/2/2008

Trent,

Thanks for investigating and finding the glitch. There is no hurry from my perspective as I just picked this field at random to test the general procedure for filling custom properties in one server pass. I'll continue my testing with a text field and press on.

Larry