Hidden paths of MVVM - Lookup values

To recap, I am getting my feet wet in MVVM using XAML and I got stuck with a seemingly simple task. I have a field with domain values and I needed to display the description stored in another table. Ordinarily, I would have defined a calculated field in the target dataset and make it a lookup field against the dataset containing the description using the domain value as the matching key.

I am using a strongly typed dataset for my data layer and conversant with using EXPRESSION property to derive values. My intial thought was to use the "RowChanging" event handler to populate the description for the domain value but then I thought that was not an efficient way to go and I suspected there was a way to actually implement some form of "lookup" even if it was not implemented in the traditional way.

Well, I did a couple of googling and came across the concept of "RELATIONS" in dataset. Basically, Dot Net Framework allows relationship to be defined at design time amongst datasets and based on the relationship, CALCULATED FiELDS can use EXPRESSION properties to do lookups. As usual, the devil was in the details, very powerful feature but not well documented. I huffed and puffed for about 4 days before I stumbled across a post that attempted to explain how to use it.

Just a quick refresher - Visual Studio provides a Dataset Designer and has the concept of TableAdapters and DataTables. Simple put - a TableAdapter is a container describing a set of columns avaiable for a container with the ability to have one or more Queries than can be used in CRUD operations against the set of columns. Each query defined has two possible standard ways of returning data through methods called FillBy and GetBy. FillBy bascially pumps data into a data table already defined by the developer and suppplied as a paramater to the method call while GetBy method basically returns a Data Table value.

To get the lookup concept working, one needs to define a RELATION for a dataset as well as specify the PARENT and CHILD DataTables and the RELATION. After doing that, at run time, the Parent-Child Relation needs to be referenced from the dataset and the MOST IMPORTANT thing to get this to work is that - One must use the FILLBY method to get data and not the GetBy method, otherwise, the RELATION will not be properly established.

Assuming all the abracadabra involving the setup has been properly done, then, what one needs to do is use the expression property to specify the valuse to displsy as lookup as in Parent.FieldName. The Parent in this case denotes the "lookup" dataset. Sometimee, talking and writing about this does not make much impact until one actaully tries one's hands on the issue.


// This is a method used in retrieving a DataTable that is a RELATION with another table
// Basically, there are two tables or dataset, one has domain values while the other has domain description
// At design time, two Table Adapters were created - one for the DataTable with domain values
// and the other for the DataTable with domain description
// A RELATION was also created for the two DataTables based on the Doman value
public PersonSearchDataSet.PersonSearchDataTable PersonSearchData (
string searchtype,
string lastname,
string firstname,
DateTime? birthdt_fro,
DateTime? birthdt_to,
string gender_cd,
string ssn,
decimal? personid,
string cntypersonnum,
string pmi,
decimal? case_ssis,
string case_cnty,
decimal? work_group,
string address,
string city_name,
decimal? excluded_id)
{
object vCursor;
vCursor = null;
// Define a Pointer to the Dataset - becuase RELATIONS are defined at dataset level
PersonSearchDataSet vv = new PersonSearchDataSet();
// This is really redundant but a way to validate the datatype for the PARENT TableAdapter
CodeTableAdapter _codetableadapter = new CodeTableAdapter(); // This is really redundant but a way to validate the datatype for the PARENT DataTable
PersonSearchDataSet.CodeDataTable vMaster = new PersonSearchDataSet.CodeDataTable();
// This is really redundant but a way to validate the datatype for the CHILD TableAdapter
PersonSearchTableAdapter _personSearchTableAdapter = new PersonSearchTableAdapter(); // This is really redundant but a way to validate the datatype for the CHILD DataTable
PersonSearchDataSet.PersonSearchDataTable vResult = new PersonSearchDataSet.PersonSearchDataTable();
// The RELATION defined at the Dataset Level already has place holders for both the Parent and Child DataTables
// Use the FILLBy Method to populate the PARENT DATATABLE based on the ACTIVE RELATION at the dataset level
_codetableadapter.FillData((PersonSearchDataSet.CodeDataTable) vv.Relations[0].ParentTable);
// use the FillBy Method to populate the CHILD DataTable based on the ACTIVE RELATION at the dataset level _personSearchTableAdapter.FillBy((PersonSearchDataSet.PersonSearchDataTable)vv.Relations[0].ChildTable,
searchtype,
lastname,
firstname,
birthdt_fro,
birthdt_to,
gender_cd,
ssn,
personid,
cntypersonnum,
pmi,
case_ssis,
case_cnty,
work_group,
address,
city_name,
excluded_id,
ref vCursor);
// Assign to a local pointer in case any further processing is required
vResult = (PersonSearchDataSet.PersonSearchDataTable)vv.Relations[0].ChildTable;
// At this point the Lookup value for the calculated field defined in the CHILD datatable
// would have been populated
return vResult;
}