I have a C#/SQL Server program that sometimes needs to store data. The data could be a response from a web service, a result of a database query, or any number of other things. There's no way of knowing before the data is stored how many fields it might have or what the data structure might be. We have this kind of painful table we're using for this... four columns and lots of rows. An example of the data might be easier than an explanation.
InstanceID RowID PropertyName PropertyValue 1 1 Property1 Value1 1 1 Property2 Value2 1 1 Property3 Value3 1 2 Property1 Value1 1 2 Property2 Value2 1 2 Property3 Value3 2 1 OtherProp1 Value1 2 1 OtherProp2 Value2 2 2 OtherProp1 Value1 2 2 OtherProp2 Value2
These values will then be pulled back and fed into a dictionary object, which can be updated, then the fields will be fed back into the database. This can be painful to code against, and also requires a lot of inserts which can make it very slow.
I can't think of a better way of doing this, but I feel like there must be one. Any advice?