3

I would like to store a c# object in SQL server. I thought about the following options:

  1. Read object byte memory stream and save them into the database (but not readable in sql)
  2. Json, readable, easy to convert but what data type? (only a datatype for sql 2016)
  3. XML, a bit less readable, easy to convert, there is an XML dataType

What's the best practice to store a C# object in a sql column and why? I am using SQL 2014, so I think option 3 is the best?

Edit: Note: it's not data to query, I just want to load a object which I have cached into a c# object in memory. And perform some logic on that in c#. It just takes a while to get the data from another database, therefore I save all my data in a custom object. Therefore I don't think I should use ORM

Improve this question
10
  • 2
    People usually use ORM tools like Entity Framework. Usually you don't just want to store them but also query. Commented Jun 30, 2017 at 12:15
  • This is really going to be a matter of preference between options 2 and 3. I would prefer XML myself, but there are valid arguments for either XML or JSON. Commented Jun 30, 2017 at 12:16
  • Only store dynamic objects like this - if you have a typed class, use an ORM as @AlexeyZimarev suggests. Commented Jun 30, 2017 at 12:16
  • You don't need a JSON data type. Just store it as nvarchar(max) and done... XML is slower and takes much more space Commented Jun 30, 2017 at 12:18
  • @AlexeyZimarev if the OP is serializing the object, why are you suggesting the OP cannot read it back afterwards? I'd agree to use an ORM, if the OP wants another table and not just one column Commented Jun 30, 2017 at 12:19

3 Answers 3

Reset to default
1

If it's just to throw in a database to read back at some point later by a key, then go with (2) and just use an nvarchar(max) field type.

If it's data to query, then you should probably design a schema to match and use an ORM.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

For an ORM to work here, the OP would need another table and a foreign key, you should include that in the details
I can't use an ORM. Because I want to have all data +- 200MB in memory to do my analysis on. But it takes approx. 2 days to gather the 200MB of data (from azure table storage). Therefore I want to store it somewhere just in case my application needs to restart and my memory is cleaned.
Why would an ORM prevent you from storing 200MB quickly, or retrieving it quickly? And even if that did decrease speed, why not just install SQL Express on your local machine, and store the data there? That way, you can use an ORM without speed issues.
It's not about getting the data or storing it fast. It's just because I need to do heavy calculations on all the data (not a subset). Which is way faster in C#. And all my logic is in c#, including a lot of unit tests to validate the logic.
@CamiloTerevinto - yup - for an ORM it may need multiple tables and lots of FK's, hence the "design a schema to match".
|
1

If you are more positive towards option B, then you can store json-serialized string of any object[or datatype] in sql server as NVARCHAR(MAX) field.

And when you want to read it you can easily de-serialize that string in original format.

e.g. 

Demo d1=new Demo();

//store this json into database.
string json= JsonConvert.SerializeObject(d);

// Now while reading fron db
Demo d2= JsonConvert.DeserializeObject<Demo>(json);
Improve this answer

Comments

0

I'd go for JSON serialisation, it's just text, so when storing things like "user profile settings" or other types of structural data you're covered as you can read and write JSON in any language. Now SQL server has also understood this, like the XML support that was such a hype 8-10 years ago one can now store JSON with a good deal of TSQL support for those that need to update the data, like when you need to fix all updates for all user where...

anyway, have a look at the article. JSON in SQL Server 2016-2017 When going to and from JSON you should test your properties as some data types might not convert back and forward nice depending on things like regional specific settings like date and decimal values.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.