将C＃数据结构存储到SQL数据库中 [英] Storing C# data structure into a SQL database

问题描述

我对ASP.NET和SQL Server的世界很新，所以请原谅我的无知...

如果我在C＃中有一个数据结构（for例如，让我们说一个存储一些字符串的向量），是否可以在SQL表中存储向量的内容？我想做到这一点，以便快速将该数据转换为矢量格式尽可能快，而无需按元素构造它。几乎像将二进制数据写入文件，然后读取它并将其复制到C中的分配结构。

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

有人可以向我展示一个例子，说明我将如何存储和检索10个字符串的向量，从那个领域？这是可能的（我不能想到为什么不）？

谢谢！

解决方案

首先，简单地创建一个关系结构并将对象映射到数据库中的字段是明显的。

其次，如果你有一个可序列化的对象，您可以将其存储在SQL Server中。我已经做到了这一点，并且在SQL Server中使用了Text数据类型来存储XML。

意见：我喜欢将序列化对象存储为XML而不是二进制数据。 为什么？因为你可以读取那里的内容（用于调试），而在SQL Server中，你可以使用XQuery从序列化对象中选择数据。从我的经验来看，使用二进制数据的性能获益并不值得，因为数据更容易调试，可以以伪装关系的方式使用。请查看 SQL Server的XQuery功能。即使你不打算立即使用它，也没有理由把自己放在一个角落。

你可以看一些例子，使用 NetDataContractSerializer 。

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

  using System; 
使用System.Collections.Generic; 
使用System.Linq; 
使用System.Text; 
使用System.Runtime.Serialization; 
使用System.IO; 
 
命名空间SerializeThingy 
 {
类程序
 {
 static void Main（string [] args）
 {
列表&字符串> 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（）））; 
列表< string> myList2 =（List< string>）serializer.Deserialize（stream）; 
 Console.WriteLine（myList2 [0]）; 
 Console.ReadKey（）; 
} 
} 
} 
  

这个例子只是序列化一个列表，将序列化输出到控制台，然后证明它在正确的水平正确。我想你可以看到，从这里你可以将内存流转储成一个字符串，并将其写入数据库，或使用另一种流类型而不是内存流来执行。

请记住参考System.Runtime.Serialization以访问NetDataContractSerializer。

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!

解决方案

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.

这篇关于将C＃数据结构存储到SQL数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！