StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Foreign Key Field Description

Foreign Key Field Description

ChanKK — Thu, 17 Jun 2010 13:30:48 GMT

Hi,
I have table employee (EmployeeBO) with field
employeeNo, employeeName.

I have txn table sale (SalesBO) with field
SalesNo, Date, EmployeeNo, Qty, Price, Total

I would like like to list table employee in editable GridView with columns

SalesNo, Date, EmployeeNo, EmployeeName, Qty, Price, Total

In order to achieve this, I added custom bindable field - EmployeeName to SalesBO, and retrieve it from employeeTable whenever required. I found that it is really killing my application performance.

May I know how should I amend it to get back performance?

Thank you

RE: Foreign Key Field Description

Edhy Rijo — Thu, 17 Jun 2010 13:30:48 GMT

Hey Larry, thanks for the code, I like your approach to make sure that the custom field exist in the data table:

[quote]If Me.CurrentDataTable.Columns.Contains("cu_Name") Then....[/quote]

Updating my code now... :hehe:

RE: Foreign Key Field Description

Larry Caylor — Thu, 17 Jun 2010 13:05:47 GMT

Chan,

Edhy is right on, I use this approach for custom fields all the time. Shortly after the original version of SF was released it was enhanced to save only the fields contained in the BOs AllFieldNames list (The AllFieldNames contains the fields defined in BO mapper). This allows you to add any fields you want in a custom fill method, it just won't be persisted to the database. For example, assume that I have an order table that contains the foreign key for the customer table and I want to display the customers name as a custom field in my order BO. My OrderBO custom fill method would look someting like this

Public Sub FillOrder(ByVal OrderID As Integer)
' Establish locals.
Dim sqlCmd As New SqlCommand
' Build the command
With sqlCmd
    .CommandText = "SELECT " & Me.AllFieldNames & ",cu_Name " & _
    "FROM " & Me.TableNameAndSchema & " " & _
    "JOIN Customer ON cu_pk = or_cu_pk " & _
    "WHERE or_pk = @OrderID "
    .Parameters.AddWithValue("@OrderID", OrderID)
End With
' Fill object.
Me.FillDataTable(sqlCmd)
End Sub

The custom field on my OrderBO would look like

<Browsable(False), _
BusinessFieldDisplayInEditor(), _
Description("Customer Name"), _
DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _
Public ReadOnly Property cust_CustomerName() As String
Get
' Check to see if datatable contains the custom field in
' case object was filled by a method that doesn't contain the field.
    If Me.CurrentDataTable.Columns.Contains("cu_Name") Then
      If Not TypeOf (Me.CurrentRow.Item("cu_Name")) Is DBNull Then
        Return CStr(Me.CurrentRow.Item("cu_Name"))
      Else
        Return ""
      End If
    Else
      Return ""
    End If
End Get
End Property

When I go to save the OrderBO object, only the fields defined in the AllFieldNames list will be saved, any fields that I've added will be ignored.

-Larry

RE: Foreign Key Field Description

Edhy Rijo — Thu, 17 Jun 2010 11:22:17 GMT

Well then my approach should work for you since I do the same thing.

In SalesBO you have the FK for employee plus the cfp_EmployeeName, once you load the data it will bring the cfp_EmployeeName for that FK. If you are adding records, then you need to update the cfp_EmployeeName yourself at that time since the new record will not have that information.

Please try not to get confuse, this is pretty simple approach. One of the beauty feature of the BO is that it uses an internal data table and this does not need to match with the properties created by the BOM which means that if the SQL query you use to fill the bo have a field (in this case cfp_EmployeeName) which is not in the BO it will to trigger any error but the underline data table will have this column which you can access in the Custom Field Property and update the cfp_EmployeeName property from it.

RE: Foreign Key Field Description

ChanKK — Thu, 17 Jun 2010 10:53:52 GMT

Hi,
No, I don't want to update employeeName, but need to update

SaleDate, Qty, Price, EmployeeNo

As I know, SQL Server doesn't support update to any view that involve more than one table.

Beside, if I want to add new record to SalesBO, I do need to have additional handling to update EmployeeName in SalesBO as well so that the grid will show the latest data.

Is it the way?

RE: Foreign Key Field Description

Edhy Rijo — Thu, 17 Jun 2010 10:49:06 GMT

Oh, sorry, in my case I never update the FK description field, just RO, but all other fields are modifiable.

If you need to modify the Employee Name in that case probably you will need a view, I have not done that so I cannot advise you on that.

I am sure somebody will chime in here for the view or other method to get it done.

RE: Foreign Key Field Description

ChanKK — Thu, 17 Jun 2010 10:44:29 GMT

Hi,
JOIN = view?
If so, how could BO update a multi join view to underlying table?

Thank you

RE: Foreign Key Field Description

Edhy Rijo — Thu, 17 Jun 2010 10:40:14 GMT

[quote][b]ChanKK (06/17/2010)[/b][hr]...In order to achieve this, I added custom bindable field - EmployeeName to SalesBO, and retrieve it from employeeTable whenever required. I found that it is really killing my application performance.

May I know how should I amend it to get back performance?[/quote]

Yes a custom field property may affect performance if you need to deal with a lot of records. I get around that by using a custom fill method with a JOIN to bring the desired field with an alias "cfp_EmployeeName", then I will create the custom field property (you already have it), but with code to just return the column value like this:
[codesnippet]
BusinessFieldDisplayInEditor(), _
Description("Employee Name"), _
DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _
Public ReadOnly Property [cfp_EmployeeName]() As System.String
Get
Dim loValue As Object
loValue = Me.CurrentRow.Item("cfp_EmployeeName")
If loValue Is DBNull.Value Then
Return String.Empty
Else
Return CType(loValue, System.String).Trim()
End If
End Get
End Property
[/codesnippet]

This way you can continue to use the Custom Field Property in the designers and just need to make sure that the source SQL will have that column "cfp_EmployeeName".