将C＃数据结构存储到SQL数据库中

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

我是ASP.NET和SQL服务器的新手，所以请原谅我的无知......

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.

如果我在C＃中有一个数据结构（例如，让我们说，存储一些字符串的向量），是否可以像在SQL表中那样存储向量的内容？我想这样做，以便尽快将数据转换回矢量形式，而不必逐个元素地构造它。几乎就像将二进制数据写入文件然后读取它并将其复制到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.

我在SQL Server 2008上创建了一个表，其中一个字段被定义为VARBINARY（MAX）。我以为我会从那开始。

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)?

有人可以向我展示一个例子，说明如何在该字段中存储和检索10个字符串的向量？这甚至可能（我想不出为什么不）？

Thanks!

谢谢！

7 个解决方案

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

其次，如果您有一个可序列化的对象，则可以将其存储在SQL Server中。我偶尔也这样做了，并使用SQL Server中的Text数据类型来存储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.

意见：我更喜欢将序列化对象存储为XML而不是二进制数据。为什么？因为您实际上可以读取其中的内容（用于调试），并且在SQL Server中，您可以使用XQuery从序列化对象中选择数据。根据我的经验，与使数据更容易调试并且可以以伪相关方式使用相比，使用二进制数据的性能提升将是不值得的。看看SQL Server的XQuery功能。即使您不打算立即使用它，也没有理由让自己陷入困境。

You might look at some examples using the NetDataContractSerializer.

您可以使用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:

我相信你所谓的向量是C＃中的List <>。看看System.Collections.Generic。您可以使用NetDataContractSerializer序列化3个字符串的List，如：

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.

请记住引用System.Runtime.Serialization来访问NetDataContractSerializer。

#2

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

#3

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

假设对象标有[Serializable]或实现ISerializable，BinaryFormatter类提供了一种简单的方法。

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

如果没有，您正在查看（非平凡的）自定义代码。

#4

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.

如果你要这样做（我想这在技术上是可行的），你也可以使用平面文件：再也不用使用关系数据库了。

#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:

存储/检索此字节数组的一种基本方法是使用简单的SQLClient对象：

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

etc...

#6

There are reasons to be flexible. Rules, or guidelines, for database structure should not be allowed to impede creativity. Given the first thread here, I can see a hybrid approach for storing both serialized and constraining columns as well. Many an application could be vastly improved by keeping ones mind open to possibilities.

有理由保持灵活性。不应允许数据库结构的规则或指南妨碍创造力。鉴于这里的第一个线程，我可以看到用于存储序列化和约束列的混合方法。通过让人们保持开放的可能性，可以大大改善许多应用程序。

Anyway, I appreciated a newbies perspective on the matter. Keeps us all fresh..

无论如何，我对新事物的看法表示赞赏。保持新鲜感......

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

我对关系数据库比c＃有更多的经验，但二进制序列化是一种可接受的方式，因为它允许将整个对象的状态保存到数据库中。 XML序列化几乎相同，但不允许使用泛型类型。

#1