June 17, 2022 at 8:48 am
In my application, we want to give users the option to create new fields in a table in addition to application's out-of-box functionality. Data for the Out-of-box functionality is accessed through SQL views from a C# application. If new fields are to be a part of this accessed data then I will have to recreate views at runtime when a new column is added. Or avoid sql views completely and build queries at runtime based on the columns in the table (plus any joins as decided by the logic). I want to know how this can be achieved, is there any sql server feature that can help achieve this? if not what would be the approach?
June 17, 2022 at 9:43 am
Common approach for these is to have a set of metadata tables that contains all the information related to tables/views in such a way that you can query those tables to generate the sql to (re)create any table or view.
for your specific case I would envisage flags to ensure that user can't drop/rename "base" columns.
your code that allows new columns to be added/removed would then need to update the above metadata tables and generate create/alter statements based on what was changed.
do note that if you alter a view you also need to refresh any stored proc that references those views.
not an easy task - needs to be considered very well in terms of having all required information to also allow recreating indexes and optionally add /drop new indexes, including options to create filtered indexes.
June 17, 2022 at 1:51 pm
Right, in that case another approach I think of is, to give 5 text, 5 dates, 5 numeric fields by default on the entity. Considering that would not grow beyond 10/15 custom fields per entity. In that case I can keep the views intact and no need to build at runtime. I will only have to maintain the metadata of the cutom fields-to-business use mapping.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply