Generics and database - a design issue

Question

The situation is that I have a table that models an entity. This entity has a number of properties (each identified by a column in the table). The thing is that in the future I'd need to add new properties or remove some properties. The problem is how to model both the database and the corresponding code (using C#) so that when such an occasion appears it would be very easy to just "have" a new property.

In the beginning there was only one property so I had one column. I defined the corresponding property in the class, with the appropriate type and name, then created stored procedures to read it and update it. Then came the second property, quickly copy-pasted, changed name and type and a bit of SQL and there it was. Obviously this is not a suitable model going forward. By this time some of you might suggest an ORM (EF or another) because this will generate the SQL and code automatically but for now this is not an option for me.

I thought of having only one procedure for reading one property (by property name) and another one to update it (by name and value) then some general procedures for reading a bunch or all properties for an entity in the same statement. This may sound easy in C# if you consider using generics but the database doesn't know generics so it's not possible to have a strong typed solution.

I would like to have a solution that's "as strongly-typed as possible" so I don't need to do a lot of casting and parsing. I would define the available properties in code so you don't go guessing what you have available and use magic strings and the like. Then the process of adding a new property in the system would only mean adding a new column to the table and adding a new property "definition" in code (e.g. in an enum).

Answer 1

It sounds like you want to do this:

MyObj x = new MyObj();
x.SomeProperty = 10;

You have a table created for that, but you dont want to keep altering that table when you add

x.AnotherProperty = "Some String";

You need to normalize the table data like so:

-> BaseTable
   RecordId, Col1, Col2, Col3

-> BaseTableProperty
   PropertyId, Name

-> BaseTableValue
   ValueId, RecordId, PropertyId, Value

Your class would look like so:

 public class MyObj
 {
      public int Id { get; set; }
      public int SomeProperty { get; set; }
      public string AnotherProperty { get; set; }
 }

When you create your object from your DL, you enumerate the record set. You then write code once that inspect the property as the same name as your configuration (BaseTableProperty.Name == MyObj.<PropertyName> - and then attempt the type cast to that type as you enumerate the record set.

Then, you simply add another property to your object, another record to the database in BaseTableProperty, and then you can store values for that guy in BaseTableValue.

Example:

 RecordId
 ========
 1

 PropertyId          Name
 ==========          ====
 1                   SomeProperty

 ValueId     RecordId       PropertyId      Value
 =======     ========       ==========      =====
 1           1              1               100

You have two result sets, one for basic data, and one joined from the Property and Value tables. As you enumerate each record, you see a Name of SomeProperty - does typeof(MyObj).GetProperty("SomeProperty") exist? Yes? What it it's data type? int? Ok, then try to convert "100" to int by setting the property:

 propertyInfo.SetValue(myNewObjInstance, Convert.ChangeType(dbValue, propertyInfo.PropertyType), null);

For each property.