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

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.