Serialize C# class directly to SQL server?

Question

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