C#: How would you store arbitrary objects in an SQL Server?

Question

Lets say you have various objects of arbitrary type that you would like to store in a key+value type of table. Key could for example be an int, string or guid. What would the value be? String, Binary or something else?

And how would you store and load the objects? I would think some sort of serialization, but what kind?

---

I have one solution at the moment where I have a class with these two methods:

public T Get<T>(string key)

public void Set<T>(string key, T value)

In the database I have a table with a string column and a binary column. I then use a BinaryFormatter to serialize and deserialize the value and Linq2Sql to put the binary result in the database table. But is this a good solution? Currently I have only dared trying this with simple values like integers and strings. How do the BinaryFormatter and serialization in general work with more complex types like structs and class? Especially if for example the value contains things like arrays or lists.

Any pointers?

---

At the moment I will be using it to store various last-selected-or-typed-etc type of values. Although they may not always necessarily be typed. For example it may be choosing values from a list. Main point is that they will pretty much be just convenience stuff for the user, so not very critical data.

Answer 1

If you can restrict yourself to specific types which map easily to SQL types, I'd be tempted to keep those in separate columns in the table, making sure you only fill one of them in. That way you have human-readable data in the database, which makes ad-hoc querying easier. It also means you're not forever locked into .NET.

There are loads of different serialization options available. The core framework ones are BinaryFormatter and XmlSerializer of course; XML is much more portable, but at a cost of space. I believe it's also less thoroughly customisable.

There are third party serialization technologies such as Thrift and Protocol Buffers. These will be more restrictive in terms of what they can serialize) but more portable. (Disclaimer: my 20% project is a C# port of Protocol Buffers, so I'm not entirely unbiased here.)

You should also consider versioning - what do you want to happen if you change the data structure you're serializing/deserializing? Maybe you don't need to be able to read "old" records, maybe you do. Maybe you need old code to be able to read "new" records - or maybe not.

Your choice of technology should really be driven by requirements. The more general you try to make it, the more complex it will become - so work out what you really need before you try to come up with a solution.

Answer 2

When we have done this type of thing we have kept the data in a byte array while in C#, and stored it in a varbinary(max) column in SQL Server.

EDIT Based on comment

You could try having a property on your class that was the byte array of your value field.

Answer 3

Usually I use a specific column for a certain kind of value.

Eg.

• Guid: UNIQUEIDENTIFIER
• numbers like int, decimal, byte etc: DECIMAL
• string: NVARCHAR
• DateTime: DATETIME

I'm using NHibernate, there it is very easy to have a subclass for each specific type and map them to the same table, each using a specific field for its specific value. I get a table like this:

create table dbo.Value (
  Name NVARCHAR(80) not null,
  Type NVARCHAR(6) not null,
  TextValue NVARCHAR(500) null,
  GuidValue UNIQUEIDENTIFIER null,
  NumericValue DECIMAL(36, 18) null,
  DateTimeValue DATETIME null
)

The column Type is either 'TEXT', 'GUID', 'NUMBER' or 'DATE'. This is all done by NHibernate, but can easily be used and manipulated without it.

Additionally you could store a reference to another entity by using an NH any type as value (storing a primary key and an entity name).

Answer 4

I'm trying to do the same thing but am having problems. Can you share with me how you prepared your objects prior to inserting and updating the database? I have a List that I want to store as a VarBinery(max) in SQL 2k8.

thanks!