Geek Answers Handbook

Generics and Database - Design Problem

The situation is that I have a table that models the entity. This object has a number of properties (each of which is identified by a column in the table). The fact is that in the future I will need to add new properties or delete some properties. The problem is how to model both the database and the corresponding code (using C #) so that when such a case arises, it would be very simple to "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 for reading and updating it. Then came the second property, quickly copied, the changed name and type and a bit of SQL, and here it is. Obviously, this is not a suitable model in the future. By this time, some of you may offer ORM (EF or other), because it will automatically generate SQL and code, but for now this is not an option for me.

I thought about the fact that I have only one procedure for reading one property (by property name) and the other for updating it (by name and value), and then some general procedures for reading a bunch or all properties for an object in that same expression. This may seem easy in C # if you are considering using generics, but the database does not know generics, so it is impossible to have a strongly typed solution.

I would like to have a solution that is “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 the code so that you do not guess what you have and use magic strings and the like. Then the process of adding a new property to the system will only mean adding a new column to the table and adding a new “definition” property to the code (for example, an enumeration).

+5
source share
2 answers

It looks like you want to do this:

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

You have a table created for this, but you do not want to continue changing this table when adding

x.AnotherProperty = "Some String";

You need to normalize the table data as follows:

-> BaseTable
   RecordId, Col1, Col2, Col3

-> BaseTableProperty
   PropertyId, Name

-> BaseTableValue
   ValueId, RecordId, PropertyId, Value

:

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

DL, . , , (BaseTableProperty.Name == MyObj.<PropertyName>), .

, BaseTableProperty, BaseTableValue.

:

 RecordId
 ========
 1

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

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

: , . , SomeProperty - typeof(MyObj).GetProperty("SomeProperty")? ? ? Int? , "100" int, :

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

.

+2

, , , ORM. , ( , ), . ORM, Dapper.Net. , , , , .

0

More articles:


All Articles