how to insert data into db as a serialized object

Question

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 ...

help !? :/

Answer 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.

Answer 2

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.

Answer 3

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.

Answer 4

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