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?
3
SQL has not been designed for this scenario, but document and key-value stores have been. Have you considered using one of those?
For example MongoDB has a C# driver with Linq support (although I doubt you will actually need that). You can simply store all the “dynamic data” in one mongodb document per SQL entity.
Another alternative would be redis, simply mapping unique entity identifiers to hashes that contain the corresponding key-value-data.
If you don’t need to make queries against the data, you could just store all of that in a single column that is in XML-format. Like
<properties>
<property name="property1">Value1</property>
<property name="property2">Value2</property>
</properties>
If you do need to make queries against the key value pairs you have couple of options. If you are using SQL Server it has support for xml columns so you can use XPath in your queries. You can also create indexes for XPaths. Other database engines might have similar features as well
You could also use a separate search engine like ElasticSearch which would index your content.
6
You should either use a NoSQL ( NosDB – an open source .Net database) database and convert your objects to JSON or you could use NCache and simply serialize your C# objects and store them as is.
If you need to query against those individual values then you should use its SQL capabilities or tag/group your keys since NCache is more than just a simple .net key value store. Read here
store = NCache.InitializeCache("somecache");
DotNetObject dotNetObject = new DotNetObject();
store.Insert("key", DotNetObject);
// ...
dotNetObject = store.get("key"); //voila you have the object deserialized
2
I am going to second the xml solution. I would also say that in LedgerSMB we are looking at doing JSON which is now natively supported under PostgreSQL for extended key/value pairs. I would also point out that currently this can be done in SQL Server by creating some TSQL processing functions. See http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/
However, given the complexity using non-native JSON components, I would say use XML which is the next best thing.
You’re presently taking the traditional approach for this need within a relational database.
One alteration to consider is strongly-typed columns for the values – as in Option 4 from this post: https://stackoverflow.com/questions/1126783.
I have a number of applications that use/store data in a similar manner. I’m not sure exactly where your “pain” is coming from in this instance as it’s usually a fairly simple model to follow.
One thing that may help is creating a custom table datatype in SQL that has Name and Value fields. Then calling your stored proc you can simply pass something like InstanceID, RowID, and a table of your custom type containing x number of name-value pairs.
This makes the interface for your calls consistent regardless of what is actually getting passed.
Something else to potentially look at may be creating static objects in a class to cache that. Manage it through methods in that class, and provide async calls to SQL to keep it updated if need be.