Reading custom data from SQL tables

Question

We have an application that allows the user to add custom columns to our tables (maybe not the best idea, but that's how it is).

We are now (re)designing our dataaccess layer (we didn't really have one before) and now we're going to use parameterized queries in our datamappers when querying the SQL-database (earlier we concatenated the SQL-strings and escaped all input).

Now we're trying to determine the best way of handling the custom columns in order to both query, create and update these records. The custom attributes are going to be stored in a Dictionary on our "business objects" so I was thinking about doing it like this:

Querying data

• Use SELECT * to get all columns and populate our properties and store the rest (custom data) in a dictionary on the business object.

Create/Update

• Iterate all columns in the table (something like: SELECT COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME = 'TableName'
• Generate a SQL-string (with parameterized variablenames) by checking which columns exists in both the dictionary and the table and then adding the values from the dictionary as variables to the SQLCommand

Or are there any better approches while still using parameterized queries?

Answer 1

If you are adding ad-hoc columns, ORM gets very tricky. In some ways, dropping back to DataTable/DataAdapter (of which I am not a fan) may be an option. Personally, I would look first at other options for storing the custom data:

• an xml column
• a set of key/value pairs against each record (in a second table)
• some other delimited format in a [n]varchar(max)

Do you really have to add columns?