You might consider adding a set amount of generic columns to each of the tables (not too many; not too few: good luck!) and writing code to allow your customers to assign particular meanings (and labels) to them. But that’s a big chunk of complex functionality to code and maintain as well. Besides, the end result isn’t quite what your customers really want as they’re locked into the data types you provide, or limited to the particular number of custom columns you provide, or encumbered everywhere they turn (e.g., when writing reports) by columns with abstract meanings, some of which they didn’t need or want to begin with. It’s still a better solution than maintaining 10 (or 100, or 1000) custom variations of your app, but it’s messy, maintenance-intensive, and full of painful compromises.

What if, on the other hand, you could write a single-version application that could discover customer-added columns on its tables at runtime, and dynamically create bound controls for them? What if the app were also smart enough to permit your customers to add as many such columns as they wish, with their choice of names and data types? How much easier would that make your life? How much happier would that make your customers?

DevForce provides you with just such a capability. This week’s Tech Tip will describe the first half of the solution – that of discovering the custom columns at runtime. Next week’s tip will complete the picture by showing you how to create properties and bind user interface controls to them.

Custom Columns in the Object Mapper

As you may know, when the DevForce Object Mapper generates classes for your business objects, it creates both a developer-level class (e.g., “Employee”) and a “DataRow” class (“EmployeeDataRow”) which the former inherits. Columns in the base table map to simple properties whose definitions are written to the “DataRow” class.

In the Simple Properties grid of the Object Mapper, if you uncheck the "Include" checkbox for some columns of a table, no property definitions get generated into the “DataRow” class for those columns. From the viewpoint of the generated class – and objects instantiated from it – those properties do not exist.

How DevForce Retrieves Data for Your Business Objects

On the “Class” sub-tab of the “Class Detail” tab in the Object Mapper, you will find a dropdown list labeled “Column Selection Options”. The default setting for this property is “SelectAllColumns”; an alternate setting is “AllowColumnSelection”.

To make use of the additional data, you must somehow discover which columns have been retrieved for which no property is defined in the business class. If you can do that, you have the first half of your custom columns problem solved.

Getting a List of Properties Defined in the Business Class

To isolate the custom columns, you need two things:

1. a list of the properties currently defined for the business object; and
2. a comprehensive list of the columns retrieved.

The custom columns can be isolated by identifying items in the second list that aren’t in the first.

You can get the list of currently defined properties by calling the Get() method of the IdeaBlade.Util.PropertyDescriptorsList class, letting it know the type about which you want information.

C#:

PropertyDescriptorList pdlist = PropertyDescriptorList.Get(typeof(Employee));

VB.NET:

Dim pdlist As PropertyDescriptorList = PropertyDescriptorList.Get(GetType(Employee))

To get a comprehensive list of the columns defined in the datasource, do the following:

C#:

PersistenceManager.DefaultManager.InitializeSchema(typeof(Employee));
EntityTable employeeTable =
  PersistenceManager.DefaultManager.GetTable(typeof(Employee));
foreach(DataColumn col in employeeTable.Columns) {...}

VB.NET:

PersistenceManager.DefaultManager.InitializeSchema(GetType(Employee))
Dim employeeTable As EntityTable =
  PersistenceManager.DefaultManager.GetTable(GetType(Employee))
For Each col As DataColumn In employeeTable.Columns

The call to InitializeSchema() tells the PersistenceManager to retrieve the schema for the table on which the business object is based from the datasource. In the normal operation of an app, this schema initialization occurs as a byproduct of other operations, such as a GetEntities() call, or creation of an object of the specified type. In most cases the end result is always the same. However, there is at least one scenario in which the schema created in the PersistenceManager’s cache does not reflect the custom columns. If Create() is called on the business class while the client app is disconnected from the datasource, and before a connected data retrieval has taken place, and before the cache has been restored from a locally stored entity set, the Create() method is forced – having no where else to turn – to look to the business class for its schema information. In doing so, it of course discovers only those properties that were defined in code for the class. Furthermore, the schema, once initialized, remains unchanged until its PersistenceManager is either destroyed or reset with a Clear() call. It never learns about the custom columns.

You can see the rest of the steps required to discover the full column set from the code sample shown above: you call GetTable() on the PersistenceManager to get an EntityTable and then simply iterate through its Columns collection to discover what’s there.

Isolating the Custom Columns

Once you have your two lists, isolating the custom columns is easy: just check, for each column discovered in the schema, for a PropertyDescriptor with a PropertyPath the same as the column name. If it isn’t found, you’ve got a custom column.

C#:

foreach(DataColumn col in employeeTable.Columns) {
  PropertyDescriptor pd = pdlist.Find(col.ColumnName);
  if (pd == null) {
    // Customer-added column found!
    CreateAndConfigureOneUiControl(col);
    }
}

VB.NET:

For Each col As DataColumn In employeeTable.Columns
  Dim pd As PropertyDescriptor = pdlist.Find(col.ColumnName)
  If pd Is Nothing Then
    ' Customer-added column found!
    CreateAndConfigureOneUiControl(col)
  End If
Next col

That completes your work of discovery to find the columns your customer added to the table after you shipped him your code. In next week’s Tech Tip, you’ll see how to write code that will, at runtime, create temporary properties on your business object for the data from those custom columns. Then, dynamically, you’ll toss some appropriate controls on a form for them and set up data bindings so their values can be displayed and maintained by your customer’s end users. You set all of this up without knowing the first detail about your customer’s post-delivery additions to your tables!