Friday, August 07, 2009

Reading Lookup Table Structures and Values for a Specific List of Lookup Tables

I am writing a fairly complicated piece of code that uses lookup tables heavily.  As I am ramping down on this part of the project, I took one more look at performance.  Originally I was reading all of the lookup tables to have the structures and values handy for processing.  The problem is the client has HUGE lookup tables and many of them, so I was a little concerned.  I occurred to me early on that I should be reading the lookup tables just for project custom fields that are backed by a lookup table.  In this case, I am only processing project custom fields so I don’t need the lookup tables associated with task or resource custom fields.

How to read lookup table structure and values for a subset of the lookup tables?  It took me a few minutes to figure it out so I thought I would share it with you.

The filtering capability in the PSI is a bit under documented.  Take a look at this as a primer.

When you create a filter, you can only specify a single table to filter rows on in the dataset, so you really can’t achieve what I am trying to achieve by creating a single filter, because I need lookup table structures and values, which reside in two separate tables.  You have to create two filters and make two separate calls to ReadLookupTablesMultiLang and then merge the resultant datasets to get the structures and the values in the same dataset.  Here is how it works:

LookupTableMultiLangDataSet tableStructures = lookupTableProxy.ReadLookupTablesMultiLang(
LookupTableFilters.LookupTableStructures(lookupTableIDs),
false);

LookupTableMultiLangDataSet tableValues = lookupTableProxy.ReadLookupTablesMultiLang(
LookupTableFilters.LookupTableValues(lookupTableIDs),
false);

tableStructures.Merge(tableValues);


return tableStructures;
 
The key here is to read the lookup tables structure and values and then merge the datasets!  Here is what LookupTableFilters.LookupTableStructures and LookupTableFilters.LookupTableValues look like:
 
public static string LookupTableValues(string[] lookupTableGuids)
{
Filter filter = new Filter();


using (LookupTableMultiLangDataSet lookupTable = new LookupTableMultiLangDataSet())
{
filter.FilterTableName = lookupTable.LookupTableValues.TableName;
filter.Fields.Add(new Filter.Field(lookupTable.LookupTableValues.LT_UIDColumn.ColumnName));
filter.Fields.Add(new Filter.Field(lookupTable.LookupTableValues.LT_STRUCT_UIDColumn.ColumnName));
filter.Fields.Add(new Filter.Field(lookupTable.LookupTableValues.LT_VALUE_TEXTColumn.ColumnName));
filter.Fields.Add(new Filter.Field(lookupTable.LookupTableValues.LT_VALUE_DATEColumn.ColumnName));
filter.Fields.Add(new Filter.Field(lookupTable.LookupTableValues.LT_VALUE_DURColumn.ColumnName));
filter.Fields.Add(new Filter.Field(lookupTable.LookupTableValues.LT_VALUE_NUMColumn.ColumnName));


Filter.IOperator[] operators = new Filter.IOperator[lookupTableGuids.Length];


for (int i = 0; i < lookupTableGuids.Length; i++)
{
string lookupTableGuid = lookupTableGuids[i];

operators[i] = new Filter.FieldOperator(Filter.FieldOperationType.Equal,
lookupTable.LookupTableValues.LT_UIDColumn.ColumnName,
lookupTableGuid);
}


Filter.LogicalOperator logicalOperator = new Filter.LogicalOperator(Filter.LogicalOperationType.Or, operators);

filter.Criteria = logicalOperator;
string filterString = filter.GetXml();
return filterString;
}
}

public static string LookupTableStructures(string[] lookupTableGuids)
{
Filter filter = new Filter();


using (LookupTableMultiLangDataSet lookupTable = new LookupTableMultiLangDataSet())
{
filter.FilterTableName = lookupTable.LookupTableStructures.TableName;
filter.Fields.Add(new Filter.Field(lookupTable.LookupTableStructures.LT_UIDColumn.ColumnName));
filter.Fields.Add(new Filter.Field(lookupTable.LookupTableStructures.LT_STRUCT_UIDColumn.ColumnName));
filter.Fields.Add(new Filter.Field(lookupTable.LookupTableStructures.LT_PARENT_STRUCT_UIDColumn.ColumnName));
filter.Fields.Add(new Filter.Field(lookupTable.LookupTableStructures.LT_STRUCT_COOKIEColumn.ColumnName));


Filter.IOperator[] operators = new Filter.IOperator[lookupTableGuids.Length];

for (int i = 0; i < lookupTableGuids.Length; i++)
{
string lookupTableGuid = lookupTableGuids[i];

operators[i] = new Filter.FieldOperator(Filter.FieldOperationType.Equal,
lookupTable.LookupTableValues.LT_UIDColumn.ColumnName,
lookupTableGuid);
}


Filter.LogicalOperator logicalOperator = new Filter.LogicalOperator(Filter.LogicalOperationType.Or, operators);

filter.Criteria = logicalOperator;


string filterString = filter.GetXml();
return filterString;
}
}

Notice the use of the logical “or” operators in both methods.  This allows me to filter only on the LT_UIDs I am interested in.
 
Hope this helps somebody!

No comments:

Disclaimer

Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.