Create custom objects and save them to SQL server

Question

0.00/5 (No votes)

See more:

I have to create an application to manage custom objects with the following characteristics:
1) From a form the user can create basic custom objects which will represent parts/components. For example create a "temperature sensor" object type. This object will have custom properties such as "temperature tag (string)", "Minimum temperature (float)" and "Max temperature (float)". Another object type could be Motor with properties such as speed, power, etc.
Note: The object types would be created using a form with the possibility of editing them later by adding or removing properties.
2) On another form the user would be able to create object instances (sensor1, sensor2, motor1, etc) and see and enter the property values in a table format (we will have hundreds of instances).
3) From another form the user would be able to create higher level objects which would contain basic properties (strings, floats, etc) but also basic objects such as "temperature sensor" or "Motor".
For example an object type called "Air Con" which would have 1 basic property called "setpoint degC" (string), a measuredtemperature object (type temperature_sensor) and a fan object (type Motor).
4) After defining the basic types (sensor and Motor) and the high level object types (Air Con) we could then create multiple instances of this high level object, eg AirCon1, AirCon2, etc.
If we then modify the object type at any level, the changes should cascade across all instances.

The object types and its properties should be stored on a database such as SQL Server.

I'm not looking for code to solve this but more of guidance on the best approach. There are probably libraries or technologies out there that can do this quite easily...

What I have tried:

Options as I see it:
1 - write code that builds the database tables dynamically in runtime as we define the object types, ie add a column for each property and delete/rename as the objects are edited. Probably hard to modify the table definitions once there is already data in them. Seems messy.
2 - Create static tables with lots of spare columns which will get allocated to object properties. Messy, not sure how to create object types that contain more object types inside.
3 - Create a static table with a column of the xml type and try to put all the properties in xml format. probably complicated to map xml from a column to multiple datagrid columns?

Posted 7-Feb-19 7:04am

mariomiguelmigueis

Updated 7-Feb-19 8:49am

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2019-02-07T07:13:00

Solution 1

XML serialization is the first thing that comes to mind, you already mentioned it.

Another option would be using an ORM like Entity Framework, NHibernate, Dapper etc.
See overview here: https://www.slant.co/topics/16667/~orms-for-c[^]

But beware, an ORM like NHibernate has a steep learning curve and the documentation is mediocre, so it might be best to start with a more lightweight one like Dapper.

Another thing you might be interested in is a Rule Engine, here is an example: GitHub - NRules/NRules: Rules engine for .NET, based on the Rete matching algorithm, with internal DSL in C#.[^]
And on CodeProject: SimpleRules.Net - Easy to use Rules Engine[^]

Posted 7-Feb-19 7:13am

RickZeeland

Updated 8-Feb-19 7:20am

v4

Comments

Maciej Los 7-Feb-19 14:20pm

Good one!

mariomiguelmigueis 8-Feb-19 9:12am

Dapper seems like a good idea to make my life easier however it doesn't seem to be able to actually create/modify the tables if more properties/attributes are added to an object type. XML serialization also seems to be a lot of work for what I have in mind.
In essence what I need is something like a table for each object type with a column for each property. The number of properties will not change frequently so it might be an option to simply create new tables and alter them via SQL commands. Alternatives would be something like EAV schemas but that would take away the simplicity of just scrolling through the object instances in a simple table format. I also intend to use Excel as interface for bulk changes so it would be much better to have a simple flat format.

MadMyche · Answer 2 · 2019-02-07T07:52:00

Solution 3

This could be done with as little as 3 tables in a relational database such as SQL:

Table 1 (Object) would have an ObjectID, ObjectName, and other basic items such as price.
Table 2 (Attribute) would be a simple list (AttributeID, AttributeName)
Table 3 would bridge Tables 1 & 2, and have an additional column for the values

Likewise, there would be multiple classes to represent these tables. The difference being that the ObjectClass (ugh the name) would have a collection of Key-Value pairs for all of the Attributes assigned to the object and values.

You could of course make this more complex, perhaps a 4th table for values (to eliminate 13000 vs 13,000 vs 13K values). Or expanding the Attribute table for valid ranges.

This method is how many eCommerce sites do the parametric searches & filters.

Posted 7-Feb-19 7:52am

MadMyche

Comments

Maciej Los 7-Feb-19 14:14pm

Good one!

Richard Deeming 7-Feb-19 16:45pm

AKA the Entity-Attribute-Value model[^]. :)

mariomiguelmigueis 8-Feb-19 9:25am

The problem with this is that it would probably take away the simplicity of just scrolling through the object instances in a simple table format. I also intend to use Excel as interface for bulk changes so it would be much better to have a simple flat format.
Can you give me some detailed schema examples on how this can be done? It looks like it would require an extra table to store the definition of each object (which attributes each object type has).
If I go with your proposed approach, do you think there is an easy way of linking the tables together in such a way that it is still easy to display and edit the data in a datagridview?

MadMyche 8-Feb-19 10:17am

1. For the flattened approach you can use the PIVOT in SQL or Excel
2. Tables can be linked via an INNER JOIN

Jörgen Andersson 10-Feb-19 13:27pm

Hold on to that thought, EAV has affected the sanity of many developers. More on that here: https://www.red-gate.com/simple-talk/blogs/when-the-fever-is-over-and-ones-work-is-done/

That said, it's probably the best way to go still, and as you already have realized you need (at least) two tables, one for the actual data and one for the attributes.
Next step is to keep it all as simple as possible. Whenever management comes to you wanting the statistics on one object combined with a second, depending on the values of a third while keeping the options open for a fourth still nonexistent object, it's time to look for a new job. :-)

Note that using xml or json in a document database is just another method of doing the same thing. but slightly less rigid and slightly more prone to break.

Oh, and don't do option 2, ever!

Maciej Los · Answer 3 · 2019-02-07T08:20:00

Depending on the way (method) you save your objects, you can dump them into sql database in the same as any other object (such as pdf, docx, xlms, etc.).
There's special field type: BLOB[^]

For further details, please see: How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET[^]

k5054 · Answer 4 · 2019-02-07T07:37:00

Solution 2

Quote:
The object types and its properties should be stored on a database such as SQL Server.

Is SQL a definite requirement, then? This seems like a good place to try NoSql such as MongoDB or Redis. Alternatively, maybe you can store your Form in a JSON column in SQL Server.

Posted 7-Feb-19 7:37am

k5054

James Walsh Jr · Answer 5 · 2019-02-07T08:49:00

Solution 5

This seems like a use case for a document DB, such as Cosmos DB, or Mongo DB.

Configure your object definitions via JSON, and include versioning information along with classification information.

Posted 7-Feb-19 8:49am

James Walsh Jr