I've been busy with a project which has a requirement to store different kinds of settings in a table. My first thought was to use sql_variant
, but since the project heavily relies on Entity Framework this was not an option (it still has no support).
I figured I could also simply store the data as binary and, given the type, decode it as desired. My table would look something like this;
+------------+-------------+------------+------
| Data | Type | Name | ...
+------------+-------------+------------+------
| varbinary | nvarchar | nvarchar | ...
+------------+-------------+------------+------
And a simple code implementation would then be (using C# here);
public object GetDecodedObject(string settingName)
{
Settings settings = db.Settings.Where(s => s.Name == settingName).FirstOrDefault();
switch(settings.Type)
{
case "pdf":
return DecodeAsPdf(settings.Data);
break;
case "int32":
return DecodeAsInt32(settings.Data);
break;
...
default:
return null;
break;
}
}
Of course, this code is just here as an example.
My question is;
Would this be a viable option, or are there other (and better) ways to do so?
There is not much to find on the internet about this. I have looked into SqlVariant, but the problem with that solution is that it's read-only. I have to write too.
Added the tag "entity-framework" because maybe someone from there has a better solution for a workaround for sql_variant
.