>>The ability to fill a business object from a stored procedure ( which in essence fills a datatable and then creates strongly typed properties from the currentview ) is built into Strataframe, so I is just FillmutlipleDataTables("dbo.Fill_lookup_manager") with the params indicating the BOs that will receive the data, in sequence
>>
>>
>>So the sproc looks something like
>>
>>

>>CREATE PROCEDURE [dbo].[Fill_lookup_manager]
>>AS 
>>    BEGIN
>>
>>
>>
>>-- 1 Retrieve the code data result set
>>        SELECT  code.ckey,
>>                code.clockey,
>>                code.ccodetype,
>>                code.cdescription,
>>...
>>                code.[timestamp]
>>        FROM    code
>>        ORDER BY ccodetype  
>>        
>>-- 2 Retrieve the agent data result set
>>        SELECT  agent.ckey,
>>                agent.caddinskey,
>>                agent.clockey,
>>                agent.cfileas,
>>                agent.ccompany,
>>  ...
>>                agent.[timestamp]
>>        FROM    agent
>>        ORDER BY ccompany ;
>>
>>-- 3 Retrieve the association data result set
>>        SELECT  association.ckey,
>>                association.clockey,
>>                association.ccode,
>>                association.cname,
>>  ...
>>                association.[timestamp]
>>        FROM    association
>>        ORDER BY cname ;
>>
>>-- 4 Retrieve the broker data result set
>>        SELECT  broker.ckey,
>>                broker.clockey,
>>                broker.cinfotokey,
>>                broker.cfileas,
>>                broker.csalutation,
>>    ...
>>                broker.[timestamp],
>>                ( SELECT    cdescription
>>                  FROM      code
>>                  WHERE     broker.cinfotokey = code.ckey
>>                ) AS cinfoto
>>        FROM    broker
>>        ORDER BY ccompany ;
>>...
>>

>>
>
>In this SP last lookup (broker) - would it be more efficient to do an inner join (subqueries usually perform worse than regular joins)?
>
>
>>I have a lookup manager that creates has properties for each lookup bo and that fills them from the sproc on startup
>>
>>

>>            '-- use sproc to fill em all 
>>            Using cmd As New SqlCommand("dbo.fill_lookup_manager")
>
>If you specified the command here, does the setting CommandText become redundant?
>
>
>>                cmd.CommandType = CommandType.StoredProcedure
>>                cmd.CommandText = "fill_lookup_manager"
>
>>                BusinessLayer.FillMultipleDataTables(cmd, _
>>                                                     _code, _agent, _association, _broker, _carrier, _
>>                                                     _ccagent, _ccarrier, _ccassociation, _cccdiscount, _ccchaggrat, _
>>                                                      _ccchazard, _ccchcomponent, _ccchdeductible, _ccchdiscount, _ccchexplvl, _
>>                                                     _ccchinccov, _ccchperil, _cccomponent, _cccoverage, _cccperil, _
>>                                                      _ccendfee, _ccendorsement, _ccendtodo, _ccfee, ccfinopt, _
>>                                                     _ccmga, _classtype, _codetype, _finopt, _location, _
>>                                                     _mga, _nextnumbers, _taxclass, _taxcoverage, _taxjuris)
>>            End Using
>>

>>
>
>Is BusinessLayer a static class? Do we have Static classes in VB.NET ?
>
>Also, what are _code, _agent, etc. here - ?
>
>
>>This is a framework method, of course, but well within the normal abiliites of ADO.

Businesslayer is the class from which business objects inherit, but it has exposed static ( shared ) methods that can be addressed in this way.

VB .net has shared classes and in fact the chunks of code I am showing here are part of a Lookupmanager which is part of a base.dll for the app which is not inheritiable and which has shared methods. the _agent etc are private properties which are of type AgentBO etc. The Public property Agent is shared, so from anywhere in the app, one can reference the lookup cursor as ( assuming App.base is referenced and imported ) Lookupmanger.AgentBO etc. And since the fields are strongly typed, you can say Lookupmanger.AgentBO.cAgentname or whatever to get information from the lookup.