2

my basic question is how to insert data into DB as a serialized object and how to extract and use it then ... any suggestion !!?
e.g : 

{id:1, userId:1, type:PHOTO, time:2008-10-15 12:00:00, data:{photoId:2089, photoName:A trip to the beach}}

as you see how could I insert data into column Data and then to use it !?
another question is that if I stored the photoName inside Data instead of using JOINS and get the name from it's table (photos) according to it's Id thats will not implement the last update on the photoName (right !?) besides that I'll not be able to make a relation between table photos and the Current table - (Id => photoId) - if I stored data like that .. so part of the problem is that I don't know exactly what kind of information are going to be stored in colum Data So I can't customize a separate column for every type of these information ...

Improve this question

4 Answers 4

Reset to default
1

Typically I see two options for you here.

  1. You can store an XML serialized object into the database, and simply use standard XML Serialization, here is an example that you can adapt for your needs.

  2. You can create a true table for this object, and do things the "Standard" way.

With option 1, filtering/joining/searching on the information in the "data" column although still technically possible, is NOT something i would recommend and would be more for a static storage process in my opinion. Something like a user settings entity, or some other item that is VERY unlikely to be needed for a backend query.

With option 2, yes, you have to do more work, but if you define the object well, it will be possible.

Clarification With regards to my example in #1 above. You would write out to a memory stream, etc for the serialization rather than a file.

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

3 Comments

what if someone refactors the class, wouldn't that break the stored (old) serialization?
you could just store the object in the database as xml and then store some metadata in additional db columns
nice, but if I want the last update to be applied on some data like photoName !!?
1

If you don't want to store the data relationally, you're really better off not using a relational database. Several object databases speak JSON and would be able to handle this kind of problem pretty easily.

Improve this answer

Comments

0

You can store it as JSON string and use JSONSerializer of JSON lib

http://json-lib.sourceforge.net/apidocs/index.html

to convert javabean into json string/object and vice versa.

Generally we use this to store the configuration where no of config parameters are unknown.

Improve this answer

Comments

0

Regarding saving an object in your database; you can serialize your object into xml using XDocument.ToString() and save it in database's xml datatype column.

cmd.Parameters.AddWithValue("@Value", xmldoc.ToString());

Checkout, Work with XML Data Type in SQL Server

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.