A week or so ago I participated in a thread regarding foreign keys in BOs and how to show a description in place of the key in a devexpress grid. My suggestion was to put a lookup on the column, and I went on to say that the company I work for has developed a method for implementing this without using a BO designed and built for every different foreign key. Richard Keller asked whether I could expand on this method, so I will present it here.

A bit of background, we are developing windows applications using StrataFrame (obviously) and devexpress with SQL Server as the database. We work mainly in the Tanning and Agricultural industries so that may explain my obscure choice of examples. Our Main product is the Tannery Management System, so that explains references to TMS in our code. Our expertise is with SQL server rather than .net programming (although we're learning).

We use GUIDs as keys to all our tables, however this technique dos not depend on that.

Most of our configuration tables have an Active Flag e.g. tblSPCSpecies has a column SPCActive so if the table has rows for Kangaroo and Cow where SPCActive is true then the system is able to use Kangaroo or Cow for processing whereas a row for Goat may have SPCActive set false indicating if that Goat can't currently be used.

We may also have lookups populated by our more transactional tables such as tblBTCBatches returning the batch number and some other details, but filtered by batch type, or other parameters.

So we developed the class boLookupTable.

It has four properties that have attributes that allow the values to be set at design time. These are:

FilterOnActive,   A Boolean that indicates if our form boilerplate will repopulate the lookup BO based on the active flag when editing a new record. This is so that when we are looking at old rows we can see those related to Goats, but if we enter a new one we can only add Kangaroos and Cows.

FilterOnPKOfFormsPrimaryBO,   A Boolean that indicates if our form boilerplate will filter the lookup BO based on the PK of the form's Primary BO. If this is set then the PK of the form's Primary BO will be passed to the stored procedure as parameter "@ParentPK", and provides an automated means of parent child filtering in some of forms (although it has not proved as useful as we had originally thought).

PopulateAtParentFormLoading,   A Boolean that indicates if the lookup BO should be populated at parent form loading. This is useful for the kind of configuration file where there is no active flag and no other filtering, i.e. load once and it's done.

PopulateStoredProcedureName, A string with the name of the stored procedure that will be used to populate the lookup BO.

There are also a number of overloads of a method RefreshBO that can be used to take control of the population of the lookup BO by sending upto 6 parameters to the stored procedure. RefreshBO can be called with zero to 6 pairs of ParameterName As String & ParameterValue As Object.

We've based all our BOs on our own base that inherits from the Strataframe Business Layer (?). Each of our BOs expose a property BOType that is used by our form boiler plate to identify lookup BOs so it can apply the appropriate filtering.

No that we've got our boLookupTable all compiled we need to use the BO Mapper on it. But what do we map it to. Well since it is never updating and deleting and we are going to take care of population through a stored procedure, we can map it to a view. But what view? Well, it doesn't matter as long as it has two columns PK and Name. We use something similar to the following:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'V' AND name = 'vw_boLookupTable')

      BEGIN

            DROP  View  vw_boLookupTable'

      END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[vw_boLookupTable']

AS

SELECT CONVERT(UNIQUEIDENTIFIER, '00000000-0000-0000-0000-000000000000') AS PK,

       '' as Name

GO

Anyway, with this view in place we run the BOM, configure the fields for nulls, and build the partial. As far as Nulls go, we try to avoid them in the database, but haven't entirely, and we map all our BOs with all columns as nullable generic, except for strings which "return alternate on null" with the alternate being "string.empty". I'll admit I'm not entirely sure why we chose this means of handling nulls, but it works well for us.

We're just about ready to drop the boLookupTable instance on our form, but first we will need a stored procedure to populate it, and it has to conform to some rules:

1. It must return two columns PK and Name. In our case PK is always a GUID except in a few cases I'll describe how to handle later. Name is always a string, and it should be a description extracted from the database table that we are looking up possibly combined with related data from other tables. Because the Name is coming from the database, there is no localization necessary, i.e. if the system is being run in Brazil, we would have a species described as Vaca, not one described as Cow that had to be translated to Portuguese.

2. The Stored Procedure must be able to be called with however many parameters the programmer wants to use. This means that all the parameters must have defaults within the stored procedure.

3. All the parameters in the stored procedure must be sql_variant in the stored procedure so an object can be passed to them. There are some problems with efficiency using sql_variant in a select so we always convert the parameter to a variable of the correct type for comparison in the select of the Stored Procedure.

This is an example (not necessarily a good one) of such a stored procedure.

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'stp_cluATTForBTC')

      BEGIN

            DROP  Procedure  stp_cluATTForBTC

      END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE Procedure dbo.stp_cluATTForBTC

      @BTCID sql_variant = Null,

      @ParentPK sql_variant = Null,

      @BatchType sql_variant = '0',

      @Active sql_variant = '0'

AS

BEGIN

      SET NOCOUNT ON;

         declare @MyBTCID as uniqueidentifier

         if @BTCID is not null

         begin

            set @MyBTCID = CONVERT(UNIQUEIDENTIFIER, @BTCID)

         end

         else

         begin

            set @MyBTCID = CONVERT(UNIQUEIDENTIFIER, @ParentPK)

         end

         declare @MyBatchType as int

         set @MyBatchType = CONVERT(int, @BatchType)       

         declare @MyActive as int

         set @MyActive = CONVERT(int, @Active)       

      SELECT    DISTINCT

                dbo.tblATTAttrTypes.ATTID AS PK,

                dbo.tblATTAttrTypes.ATTName AS Name

      FROM      dbo.tblATTAttrTypes INNER JOIN

                    dbo.tblATSAttrTypeStations ON dbo.tblATTAttrTypes.ATTID = dbo.tblATSAttrTypeStations.ATS_ATTID INNER JOIN

                    dbo.tblBTSBatchStations ON dbo.tblATSAttrTypeStations.ATSStationType = dbo.tblBTSBatchStations.BTSStationType INNER JOIN

                    dbo.tblBTCBatch ON dbo.tblBTSBatchStations.BTSBatchType = dbo.tblBTCBatch.BTCType

      WHERE     (dbo.tblATTAttrTypes.ATTType = 0) AND

                    (dbo.tblATTAttrTypes.ATTActive >= @MyActive) AND

                ((dbo.tblBTCBatch.BTCID = @MyBTCID) OR

                ((@MyBTCID Is Null) And (dbo.tblBTSBatchStations.BTSBatchType = @MyBatchType)))

      ORDER BY  Name

      option(recompile)

END

GO

Note We found that "option(recompile)" is useful to ensure that SQL Server doesn't use an incorrect execution plan when the Where can filter in very different ways depending on the parameters.

So now we drop an instance of the boLookupTable on the form, and fill in the population parameters, drag a BusinessBindngSource onto the form, and bind the lookup to the business binding source. And all should work as advertised.

Now what do we do if the PK of the Table we want to lookup is an Integer? What we have done is make a boLookupTableKeyAsInt which inherits boLookupTable, create a new view where the select is changed to "SELECT 1 AS PK, ' ' AS Name" (we could use the same view and use the BOM to change the type mapping, but throughout our development we have chosen not to do this but to use a standard way of mapping BOs so that no special knowledge is required to map or re-map a BO).

This technique came about when I was experimenting with attributes and discovered some of what they could do. I'll readily admit I don't fully understand attributes and know this part of the technique could be greatly improved (for one by ensuring a stored procedure name is entered). However by implementing this we eliminated about 130 different BOs used just for lookups. It works very well for us.

We also have similar methods for dealing with Enum Lookups which I'll expand on later if anyone is interested.

I've included the code for our boLookupTable and boLookupTableKeyAsInt in the attachement. Unfortunately I haven't had the chance to reformat these as a standalone solution, but I hope it shows what we're doing. I hope you find this useful, and would be very interested to hear what you think, and suggestions for improvement.

Peter