Based on what Ben said, if it turns out that you need to get data from a query, then the code as I wrote it would need a BO instantiated. My understanding was that using ExecuteScalar() is MUCH faster than loading a whole datatable and the BO, even if it is only one row. So, the code to use this would something like...







I didn't know (and still don't exactly understand) that Nothing (null in C#) could be returned. So there would have to be code to handle that also.



Finally, you could potentially speed this up even more by directly querying for Location ID in your custom property. Then you would not need to instantiate the DivisionsBO at all. However, then you have SQL spread around, so I'd likely not use that strategy.



Finally, I suppose that you could share the method, to skip the need for an instantiation, but I don't know if this is a good idea.

If you've got enough information within the business object to calculate the value without needing to execute a database query, then you're always better off calculating it locally than querying the database.  However, if the custom property cannot be calculated or built from the fields that are already within the business object, then it's OK to go ahead and run a database query to retrieve the value.  You won't have to worry about taking the performance hit of instantiation, because the method is an instance method so you'll already have the bo instantiated.

On a side note, be careful of the ExecuteScalar method.  It does not always return a value.  For instance, if you try to execute this: "Select LocationID From DivisionTable Where 1=0" it will not return DBNull.Value; instead, it will return Nothing (or null in C#).  Now, if you cast Nothing as an integer, you'll get a 0 (since 0 is the default value for the integer value type), but if you're using ExecuteScalar() to retrieve a string, be careful of the difference between a DBNull.Value returned and a Nothing returned.

Hey Greg,

I have a question about your post:

After I create this method in my BO...I'll still have to instantiate the BO in order to call the function, right? Isn't instantiation where I'll take a performance hit so it wouldnt matter if I filled the BO or just called this scalar function?

Mike,

No, you cannot search off of a custom property.  The problem is that the browse dialog dynamically creates an SQL statement to retrieve records.  When a search field is added to the browse dialog, the browse dialog thinks that the field is a valid field in the DB and it can be included in the WHERE of the created SQL statement.  Since a custom property does not have a valid backing column in the database, it can't be included in the query.

Thanks Greg! I ALWAYS appreciate advice.

I have no idea about your question on if a Custom column can be used in a search, but one comment about how you're getting the LocationID. I'd use a scalar method to return the custom field. I.e. I wouldn't fill the BO, the return the property, I'd have a method just for this that used ExecuteScalar() to get the field. I.e. in DivisionsBO I'd have a method something like GetLocationByDivision(divisionID as Integer). Inside that method I'd do something like (code not tested, just typed here from memory...don't be surprised if it's not quite 100% ):







This has a few advantages:

- ExecuteScalar() is a lot faster.

- The method handles dealing with a NULL being returned from the DB. A zero is returned in this case. It makes error checking a bit easier.

- You can use this other places if needed.



Just something I picked up somewhere in the forum from Trent or Ben and thought I'd pass it one.

Here is the custom property that I was basing a search field on:

   ''' <summary>
   ''' LocationID
   ''' </summary>
   ''' <remarks></remarks>
   <Browsable(False), _
    BusinessFieldDisplayInEditor(), _
    Description("LocationID"), _
    DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _
   Public ReadOnly Property [LocationID]() As System.Int32
      Get
         Try
            Using loBo As New DivisionsBO
               loBo.FillByDivisionID(Me.DivisionID)
               Return loBo.LocationID
            End Using
         Catch ex As Exception
            MessageForm.ShowMessageByKey("Ems.DefaultErrorMessage", ex.Message)
            Ems.HandleException(Me, ex)
            Throw
         End Try
      End Get
   End Property

Yup... Thats the problem... it didnt notice it until now.  I created another BO based on the same table with just a fill method and thats when I discovered it.

Can I not base a search field on a custom property in a BO?

It could be the business object to which that browse dialog is bound.  Either you have a field with differing case between the different collections describing the field, or you have a custom property on the business object and you're trying to include it in the search fields. 

Looking at the code, the only dictionary referenced is the FieldDbTypes dictionary on the dictionary.  Check the BO at runtime and make sure that it contains a definition for every field that is contained within the search fields for the browse dialog.

I tried removing setconnections from AppMain and it still did it.  I looked back and realized that I didnt explain where I was getting this error i just said I was getting it... Allow me to explain where I get it...

When I run my app and bring up a browse dialog... i have a combo box in the search fields criteria that represents locations... when i choose a location from the combo box and then click search is when I get the error...

If i just click search and not choose a location it works fine. I'll include my stack dump just to help out.

The given key was not present in the dictionary.
 
  Exception (KeyNotFoundException): Source="mscorlib"; Target=null; Tag=null;
    Message = "The given key was not present in the dictionary."
 
  Environment:  ThreadIdentity="" DateTime=["2007-04-23 12:44:25.723"] ThreadName=null WindowsIdentity="STRUSS\MMurphy" ThreadId="11" DemoVersion="True" DomainName="STI.vshost.exe" OSVersion=["Microsoft Windows NT 5.1.2600 Service Pack 2"] MachineName="1LPMMURPHY2" UserName="MMurphy"
 
    Remaining Stack Trace
    -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    at System.ThrowHelper.ThrowKeyNotFoundException()
    at System.Collections.Generic.Dictionary`2.getItem(TKey key)
    at MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogwindow.BuildWhereElement(SearchFieldItem SearchField)
    at MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogwindow.BuildWhereElement(Int32 ParameterIndex)
    at MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogwindow.BuildSearchWhereClause()
    at MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogwindow.Search()
    at MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogwindow.ExecuteSearch()
    at MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogwindow.tsiSearchClick(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)

Hope this helps.