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

Storing C# data structure into a SQL database

提问by alienfluid

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!

谢谢！

采纳答案by Jason Hymanson

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 服务器中。我偶尔会这样做，并使用 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 个字符串的列表，例如：

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。

回答by Joel Coehoorn

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.

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

回答by Greg Beech

Assuming the objects are marked with [Serializable]or implement ISerializablethe the BinaryFormatterclass gives a simple way to do this.

假设对象都标有[Serializable]或实现ISerializable了的BinaryFormatter类给出了一个简单的方法来做到这一点。

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

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

回答by Vyrotek

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

回答by Ash

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

回答by jle

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 序列化几乎相同，但不允许泛型类型。

回答by Chuck

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

无论如何，我很欣赏新手对此事的看法。让我们都保持新鲜..