7

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?

Improve this question
3
  • 1
    I believe that this is, unfortunately, the solution. You might consider asking to have this migrated (please don't repost) to the dba.stackexchange site where they are likely to have a deeper knowledge of the problem and answer (or for that matter, the question may already be asked on that site - it would be something to check). Commented Jan 31, 2013 at 17:07
  • 3
    Like the comment above I believe you already have the best solution using a tradition relational database. You are not duplicating data or wasting empty fields. If you had a magic wand to go back in time a document database such as couchdb or mongodb seems like it would be a great fit. Commented Jan 31, 2013 at 17:12
  • hows about app.config? Commented Jul 18, 2016 at 13:16

6 Answers 6

Reset to default
4

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.

Improve this answer
3

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.

Improve this answer
6
  • 16
    Some people, when presented with a problem, think "I know, I'll use XML." <Problem:Worsening> <Problem:TimeDescription>Now</Problem:TimeDescription> <Problem:Posessive>they have</Problem:Posessive> <Problem:Quantity>many, many</Problem:Quantity> <Problem:WorseningDescription>more problems</Problem:WorseningDescription></ProblemWorsening> Commented Feb 18, 2013 at 14:29
  • 3
    @MasonWheeler: While I agree with your general sentiment and personally even wage my little holy war against XML misuse, I think this is a viable solution, because for this particular scenario on this very stack, XML has first class support. (Although I would question why it was added in the first place). Commented Feb 18, 2013 at 18:03
  • 7
    XML is like violence: if it does not solve your problem, you are not using enough of it. Commented Feb 18, 2013 at 19:14
  • 5
    Well, you could always use JSON instead of XML because it is hip and new ;) Commented Feb 20, 2013 at 12:34
  • 4
    JSON is also more concise and uses fewer bytes to convey the same information than XML does. ;) Commented Apr 17, 2013 at 18:12
1

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
Improve this answer
2
  • This answer is a combination of other previous answers. It does not add anything significant. Commented Apr 26, 2016 at 3:48
  • Umm, no. You're wrong imho. Two answers are proposing to store in XML and then store it in SQL Servers. One answer is proposing MongoDB but without traditional SQL capabilities since OP is using the SQL server. One answer is partially supporting JSON. I am proposing to store the .net objects(serialized) and then perform search using SQL over it. Commented Apr 26, 2016 at 4:07
0

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.

Improve this answer
0

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.

Improve this answer
0

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.