Storing C# data structure into a SQL database

Question

I am new to the world of ASP.NET and SQL server, so please pardon my ignorance ...

If I have a data structure in C# (for e.g. let's just say, a vector that stores some strings), is it possible to store the contents of the vector as is in SQL table? I want to do this so that it fast to convert that data back into vector form as fast as possible without having to construct it element by element. Almost like writing binary data to a file and then reading it and copying it to an allocated structure in C.

I've created a table on SQL Server 2008 for which a field is defined as VARBINARY(MAX). I thought I'd start with that.

Could someone show me an example of how I would go about storing and retrieving a vector of, say, 10 strings, into and from that field? Is this even possible (I can't think of why not)?

Thanks!

Answer 1

If you're gonna do that (and I guess it's technically possible), you might just as well use a flat file: there's no point in using a relational database any more.

Answer 2

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.

Answer 3

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.

Answer 4

[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
    }
}

Answer 5

Here's another more general approach for generic lists. Note, the actual type stored in the list must also be serializable

using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
using System.Data.SqlClient;
using System.Runtime.Serialization;

public byte[] SerializeList<T>(List<T> list)
{

    MemoryStream ms = new MemoryStream();

    BinaryFormatter bf = new BinaryFormatter();

    bf.Serialize(ms, list);

    ms.Position = 0;

    byte[] serializedList = new byte[ms.Length];

    ms.Read(serializedList, 0, (int)ms.Length);

    ms.Close();

    return serializedList; 

} 

public List<T> DeserializeList<T>(byte[] data)
{
    try
    {
        MemoryStream ms = new MemoryStream();

        ms.Write(data, 0, data.Length);

        ms.Position = 0;

        BinaryFormatter bf = new BinaryFormatter();

        List<T> list = bf.Deserialize(ms) as List<T>;

        return list;
    }
    catch (SerializationException ex)
    {
        // Handle deserialization problems here.
        Debug.WriteLine(ex.ToString());

        return null;
    }

}

Then in client code:

List<string> stringList = new List<string>() { "January", "February", "March" };

byte[] data = SerializeList<string>(stringList);

One basic way of storing/retrieving this array of bytes could be to use simple SQLClient objects:

SqlParameter param = new SqlParameter("columnName", SqlDbType.Binary, data.Length);
param.Value = data; 

etc...

Answer 6

First off, never ever ever store data as XML in a relational database.

Second off, ignore anybody who suggests that you should store data as XML in a relational database, and mark them in a mental list of people to ignore in the future.

You're going to want to create a database schema that matches the data you're storing. In the case you describe, you can pull that off with a single table. But then you seem to be offering that case as an example, so I won't waste your time describing what that table would look like.

Suffice to say that if you have data that you'd like to store in a relational database, there are well established guidelines for doing so. If you take shortcuts like the ones described by others in this thread, they will come back to bite you.

Answer 7

I have more experience with relational databases than c#, but binary serialization is an acceptable way to go, as it allows the entire object's state to be saved into the database. XML serialization is pretty much the same, although generic types are not allowed.

Answer 8

dear all,

is there any performance analysis comparing traditional method of storing objects mapped to fields in a sql server table and storing serialized objects into sql server table? i mean which method gives us best performance; objects mapped to fields (say for ex. F1,F2,...) or serialized objects (and a single field say for ex. Data).

regards,