Storing C# data structure into a SQL database Storing C# data structure into a SQL database sql sql

Storing C# data structure into a SQL database


First, there is the obvious route of simply creating a relational structure and mapping the object to fields in the database.

Second, if you have an object that is serializable, you can store it in SQL server. I have done this on occasion, and have used the Text data type in SQL Server to store the XML.

Opinion: I prefer to store serialized objects as XML instead of binary data. Why? Because you can actually read what is in there (for debugging), and in SQL Server you can use XQuery to select data from the serialized object. From my experience, the performance gain of using binary data will not be worth it compared to having data that is easier to debug and can be used in a psuedo-relational fashion. Take a look at SQL Server's XQuery capabilities. Even if you don't plan on using it right away, there is no reason to put yourself in a corner.

You might look at some examples using the NetDataContractSerializer.

I believe what you call a vector is a List<> in C#. Take a look in System.Collections.Generic. You can use the NetDataContractSerializer to serialize a List of 3 strings like:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Runtime.Serialization;using System.IO;namespace SerializeThingy{    class Program    {        static void Main(string[] args)        {            List<string> myList = new List<string>();            myList.Add("One");            myList.Add("Two");            myList.Add("Three");            NetDataContractSerializer serializer = new NetDataContractSerializer();            MemoryStream stream = new MemoryStream();            serializer.Serialize(stream, myList);            stream.Position = 0;            Console.WriteLine(ASCIIEncoding.ASCII.GetString(stream.ToArray()));            List<string> myList2 = (List<string>)serializer.Deserialize(stream);            Console.WriteLine(myList2[0]);            Console.ReadKey();        }    }}

This example just serializes a list, outputs the serialization to the console, and then proves it was hydrated correctly on the flip side. I think you can see that from here you could either dump the memory stream into a string and write that to the database, or use another stream type than a memory stream to do it.

Remember to reference System.Runtime.Serialization to get access to the NetDataContractSerializer.


[Serializable]public struct Vector3{    public double x, y, z;}class Program{    static void Main(string[] args)    {        Vector3 vector = new Vector3();        vector.x = 1;        vector.y = 2;        vector.z = 3;        MemoryStream memoryStream = new MemoryStream();        BinaryFormatter binaryFormatter = new BinaryFormatter();        binaryFormatter.Serialize(memoryStream, vector);        string str = System.Convert.ToBase64String(memoryStream.ToArray());        //Store str into the database    }}


Assuming the objects are marked with [Serializable] or implement ISerializable the the BinaryFormatter class gives a simple way to do this.

If not, you're looking at (non trivial) custom code.