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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy