How to serialize .NET object to SQL Server

NOTE: PDF version of this article can be downloaded from here (How to serialize .NET object to SQL Server)

While .NET serialization to a file is pretty common, serialization to SQL database might not be popular scenario. I just got into the situation where I need to do this unpopular(?) scenario. Basically the principal of serialization and deserialization of .NET object is the same. but there sure are some additional steps to save/retrieve to/from database.

Depending on which serialization formatter one uses, the serialization data can be stored into XML or VARBINARY data field.

(1) How to Save .NET Serailization to XML data field

In order to save .NET serialziation data to XML field, not to mention, you have to define a column whose data type is XML.

For .NET serialization, let's use the following simple class. Note that [id] field won't be serialzied since [NonSerialized] attribute is specified.

Now here is the code for Xml serialization using SoapFormatter. First we serialize an object to memory stream using SoapFormatter and then convert bytes to string. Once we got SOAP XML, we save it to XML data field (SettingValue in this example) by using LINQ to SQL.

Afte saving it to SQL, the sample code also explain how to deserialize .NET object from SQL Server. First, it search for a record by using SettingName field and then move data in SettingValue (XML data type) to memory stream. Once the xml data is in memory, first rewind pointer and then call Deserialize() method of SoapFormatter.

(2) How to save .NET serialization to VARBINARY data field

If one wants to use BinaryFormatter, the serialzation data can be stored in varbinary column. If binary data is expected to exceed more than 8000 bytes, one should specifies varbinary(max).

With the same serialization object, the following code illustrates how to (de)serialize an object to/from SQL table.