Has anyone built a dynamic query engine using tables defined by columns?
I have a table called Fields that contains the fields of the application. In this table, I store metadata for each field (TableName, ColumnName, JoinType (internal / external).
I have something working, but it's not as clean as I would like.
Has anyone solved this before?
I am looking for some ideas around best practices.
a source to share
Dynamic data / fields are always fun.
The way I have approached this before is to have a table that defines the fields I want. A very simple example:
GroupId int, <- allows me to group fields together for a common purpose
FieldId int, <- unique identifier for connections
FieldName varchar (100), <- Obvious
DataType int <- attached to a table that contains available types such as text, telephone, email, which may have special processing characteristics.
DisplayOrder int, <- what order will be displayed on the screen.
Then I have another table that stores the actual data: EntryId int, <- groups the values into a unique entry point. GroupId int, FieldId int, varchar (max) value <-use any amount you think will contain data; or max if you have the correct version of SQL Server
Finally, I dynamically generate views that turn the actual data 90 degrees for easier reporting.
This way you have a lot of freedom on what you collect without changing the underlying code.
a source to share
I've written a query generator in the past that could automatically join tables and merge them to create dynamic reports.
I think today I would take NHibernate. Although it is an ORM, it manages the joins and creates queries according to the mapping data, which is very similar to your metadata.
Of course, you still have to do specific things that are not covered by NHibernate. But this is a great start.
a source to share