0

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.

Community
  • 1
  • 1
Dion V.
  • 2,090
  • 2
  • 14
  • 24
  • How big do you think the data sizes are going to be? I am asking for the PDF type, I am not worried about the int. – Hozikimaru Mar 25 '15 at 14:20
  • @SurgeonofDeath Honestly, I have no clue yet. But, given the current situation, it is no problem to store some BLOB's. – Dion V. Mar 25 '15 at 14:23
  • 1
    Marc makes a great point at http://stackoverflow.com/questions/2347842/storing-pdf-files-as-binary-objects-in-sql-server-yes-or-no . It all depends on the data size, if you think there may be data that is going to be more than 1MB as mentioned in the article, using FileStream feature would be advisable. – Hozikimaru Mar 25 '15 at 14:27
  • @SurgeonofDeath Interesting link, thanks! – Dion V. Mar 25 '15 at 14:30

0 Answers0