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?

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

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.

Thanks Greg! I ALWAYS appreciate advice.

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.

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?

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.

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.