Serialize C# class directly to SQL server?

Question

Hi there,

can anyone suggest the best way to serialize data (a class actually) to a DB?

I am using SQL server 2008 but i presume i need to serialize the class to a string / or other data type before storing in the database?

I presume that this field needs to be text or binary??

Does SQL server 2008 (or .net 3.5) support serializing directly tothe database ??

Any help really appreciated

Answer 1

You can xml serialize the class into an xml field. We use this all the time for exception logging in an ETL.

Using the XmlSerializer you may want a helper method somewhere which serializes the class to a string...

public static string SerializeToXml<T>(T value)
{
    StringWriter writer = new StringWriter(CultureInfo.InvariantCulture);
    XmlSerializer serializer = new XmlSerializer(typeof(T));
    serializer.Serialize(writer, value);
    return write.ToString();
}

Then just put the string into the db like any other.

Answer 2

The best way to store data in a database is in columns (per property), so that it is queryable and indexable. ORM tools will help with this.

However, it is also possible to serialize a class as a CLOB/BLOB (varchar(max)/varbinary(max) etc).

It this is what you want, avoid anything implementation-specific or version-intolerant; so in particular, don't use BinaryFormatter. Anything contract-based should work; XmlSerializer, DataContractSerializer, etc. Or for fast binary, protobuf-net might be worth a look.

But I stress; columns would be better.

Answer 3

Check out Linq-to-SQL (questions on SO, resource on MSDN) or other O-R Mapping options.

Answer 4

I've serialized objects as XML and thrown those into the database just fine. Since we knew the max amount of text we used the varchar(max) datatype instead of getting into TEXT or Binary formats.

This was a OLTP web application and one thing we found was that using a column with an xml datatype invoked some significant cpu usage as the xml was validated on every insert. In our case the xml was never queried for anything so not having the xml query capabilities worked out ok for us.

Answer 5

There are couple of options:

Runtime serialization, serializable objects are marked with the Serializable attribute, in which case the IFormatter class does all the work of serialization. A serializable object can ISerializable, but then you will need to implement the GetObjectData( ) method. The problem with runtime serialization is that program reading the xml data needs to have the knowledge of the CLR types.

Xml serialization: Unline runtime serialization, you will get good interoperability in this case. The XmlSerializer type contains the methods Serialize( ) and Deserialize( ), thus any object can be serialized to XML and saved into the database and when you retreive it back, you can deserialize it easily.

To read data from the database, you can use the SqlCommand class method that executes SQL queries, namely ExecuteXmlReader( ). ExecuteXmlReader( ) returns an instance of XmlReader and that will read your xml data.