0

I am preparing to do an upgrade of a fairly large program that is currently written entirely in MS Access, both GUI and data (code and data in separate files). One of the key features of the product is that end users can add additional columns to several of the tables at run time. Each new column can be one of several datatypes (string, date, lookup key to another table, integer, etc). The values in the columns can be a value directly entered in, or a formula based off of other columns. The databases are shipped with a few base columns, and the rest are all defined in the field by the various end users.

In the application, which will be either winforms or WPF to start, we will need to bind those fields to controls and collections of rows to grids. Furthermore, there is a table that contains the metadata about the fields.

The base table has a schema something like:

ID as integer
Name as string
CategoryID as integer
Price as Decimal

There will typically be 70-90 additional columns defined by the user. The metadata table has the following (and more) columns for each field (using data for ID after each)

FieldName as String ("Name")
Visible as Boolean (True)
DisplayName as string ("Item Name")
ReportName as string ("Name")
DisplayWidth as integer (20)
ReportWidth as integer (10)
etc (20+ more columns)

All of the metadata is update-able.

Should I use the built in data table class to handle all of this, or would I be better off to go with a base class containing the known fields and dynamic code compiled at runtime that creates all of the appropriate properties. If I go with the dynamic class, should I then put all of the metadata into a custom attribute?

  • 1
    How is the actual custom field data (not the metadata) stored in the database? – Robert Harvey Apr 27 '15 at 20:51
  • Normal data tables. We actually extend the schema of the tables when a user adds a new field. If the user adds a date field named PurchaseDate, we do ALTER TABLE ADD COLUMN [PurchaseDate] DATE – LeeG Apr 27 '15 at 20:54
  • Which means that you can get all of the columns with SELECT *. Do you need more structure than that? – Robert Harvey Apr 27 '15 at 20:55
  • Sure, I can select it. Now I need to bind it to a grid, or a custom control, and be able to update it, and refresh dependent objects when it is updated. I know I can do it all with a datatable. What I don't know is if that is the best solution. This data is used in about a hundred different forms. Some will be grids, but most will only display some of the columns based on the context of the meta data. – LeeG Apr 27 '15 at 21:00
  • What do you mean by "best?" Do you have specific non-functional requirements that you are trying to meet? – Robert Harvey Apr 27 '15 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.