Business Entities for Lookup Tables

Mar 27, 2012 at 3:31 PM

hi Serena,

   May be this is general question but i need some guidance that how you handled in this guidance. I have one common lookup Table for saving various types of list values like country, designation, department etc. I have LookMaster with ID and Type as fields, LookParameter with ID, ParamaterName and type ID (foreign key 'ID' field from Lookup Master) as fields.

Example Data :

LookUpMaster
-------------------
ID    Type
1     Deparment
2     Designation
3     Country

LookupParameter
-------------------
ID    ParamaterName   TypeID
1      India                        3
2       US                          3
3       IT                           1
4      Developer                2

Likewise i have data. All these may be populated in dropdown list in the needed places. But how i create entity for this common functionality? and how i create DAL and business methods for this? If i create each type as each entity how many entities i created and also it may increase in future? So can you suggest me a better approach for this or any articles for this?

Regards,

Akhil

May 19, 2012 at 12:14 AM

You might try implementing a custom method in LookupParameterDAC, something like:

 

        public List<LookupParameter> SelectByType(LookUpMaster type)        {            const string SQL_STATEMENT =                "SELECT * FROM LookUpParameter WHERE TypeID = " + LooUpMaster.ID;
            List<LookUpParameter> result = new List<LookUpParameter>();
            // Connect to database.            Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);            using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))            {
                using (IDataReader dr = db.ExecuteReader(cmd))                {                    while (dr.Read())                    {                        // Create a new _Class                        LookUpParameter lookUpParameter = new LookUpParameter();
                        // Read values.                        lookUpParameter.ID = base.GetDataValue<int>(dr, "ID");                        lookUpParameter.ParameterName = base.GetDataValue<string>(dr, "ParameterName");                        // Add to List.                        result.Add(lookUpParameter);                    }                }            }
            return result;        }

 

Sorry for the formatting above, the code snippet tool wasn't working properly...

Nothing special to do in the BC, Service, or Process layers - just add the new method up the chain.

When you want to bring up a particular set of lookup items based on the LookUpMaster (which I'm assuming you will know at that point), you can call LookUpParameterController.SelectByType(myLookUpMaster).

 

HTH