将C＃类直接序列化到SQL服务器？

can anyone suggest the best way to serialize data (a class actually) to a DB?

任何人都可以建议将数据（实际上是一个类）序列化到数据库的最佳方法吗？

I am using SQL server 2008 but i presume i need to serialize the class to a string / or other data type before storing in the database?

我正在使用SQL Server 2008，但我认为我需要在存储到数据库之前将类序列化为字符串/或其他数据类型？

I presume that this field needs to be text or binary??

我认为这个字段需要是文本还是二进制？

Does SQL server 2008 (or .net 3.5) support serializing directly tothe database ??

SQL Server 2008（或.net 3.5）是否支持直接对数据库进行序列化？

Any help really appreciated

任何帮助真的很感激

6 个解决方案

#1

You can xml serialize the class into an xml field. We use this all the time for exception logging in an ETL.

您可以将xml序列化为xml字段。我们一直使用它来在ETL中进行异常记录。

Using the XmlSerializer you may want a helper method somewhere which serializes the class to a string...

使用XmlSerializer，您可能需要一个帮助器方法，将类序列化为字符串...

public static string SerializeToXml<T>(T value)
{
    StringWriter writer = new StringWriter(CultureInfo.InvariantCulture);
    XmlSerializer serializer = new XmlSerializer(typeof(T));
    serializer.Serialize(writer, value);
    return writer.ToString();
}

Then just put the string into the db like any other.

然后就像其他任何一样将字符串放入db中。

#2

The best way to store data in a database is in columns (per property), so that it is queryable and indexable. ORM tools will help with this.

在数据库中存储数据的最佳方法是在列（每个属性）中，以便它是可查询和可索引的。 ORM工具将有助于此。

However, it is also possible to serialize a class as a CLOB/BLOB (varchar(max)/varbinary(max) etc).

但是，也可以将类序列化为CLOB / BLOB（varchar（max）/ varbinary（max）等）。

It this is what you want, avoid anything implementation-specific or version-intolerant; so in particular, don't use BinaryFormatter. Anything contract-based should work; XmlSerializer, DataContractSerializer, etc. Or for fast binary, protobuf-net might be worth a look.

这就是你想要的，避免任何特定于实现或版本不容忍;特别是，不要使用BinaryFormatter。任何以合同为基础的都应该有效; XmlSerializer，DataContractSerializer等。或者对于快速二进制，protobuf-net可能值得一看。

But I stress; columns would be better.

但我强调;列会更好。

#3

Without generics (better sollution)

没有仿制药（更好的溶剂）

public static string SerializeToXml(object value)
{
  StringWriter writer = new StringWriter(CultureInfo.InvariantCulture);
  XmlSerializer serializer = new XmlSerializer(value.GetType());
  serializer.Serialize(writer, value);
  return writer.ToString();
}

#4

I've serialized objects as XML and thrown those into the database just fine. Since we knew the max amount of text we used the varchar(max) datatype instead of getting into TEXT or Binary formats.

我已将对象序列化为XML并将其抛入数据库中。由于我们知道文本的最大数量，因此我们使用varchar（max）数据类型而不是进入TEXT或二进制格式。

This was a OLTP web application and one thing we found was that using a column with an xml datatype invoked some significant cpu usage as the xml was validated on every insert. In our case the xml was never queried for anything so not having the xml query capabilities worked out ok for us.

这是一个OLTP Web应用程序，我们发现有一件事是使用带有xml数据类型的列调用了一些重要的cpu用法，因为xml在每个插入上都经过验证。在我们的例子中，xml从未被查询过任何东西，所以没有xml查询功能对我们来说没问题。

#5

Check out Linq-to-SQL (questions on SO, resource on MSDN) or other O-R Mapping options.

查看Linq-to-SQL（SO上的问题，MSDN上的资源）或其他O-R Mapping选项。

#6

There are couple of options:

有几种选择：

Runtime serialization, serializable objects are marked with the Serializable attribute, in which case the IFormatter class does all the work of serialization. A serializable object can ISerializable, but then you will need to implement the GetObjectData( ) method. The problem with runtime serialization is that program reading the xml data needs to have the knowledge of the CLR types.

运行时序列化，可序列化对象使用Serializable属性标记，在这种情况下，IFormatter类执行序列化的所有工作。可序列化对象可以是ISerializable，但是您需要实现GetObjectData（）方法。运行时序列化的问题是读取xml数据的程序需要具备CLR类型的知识。

Xml serialization: Unline runtime serialization, you will get good interoperability in this case. The XmlSerializer type contains the methods Serialize( ) and Deserialize( ), thus any object can be serialized to XML and saved into the database and when you retreive it back, you can deserialize it easily.

Xml序列化：取消运行时序列化，在这种情况下，您将获得良好的互操作性。 XmlSerializer类型包含方法Serialize（）和Deserialize（），因此任何对象都可以序列化为XML并保存到数据库中，当您将其返回时，可以轻松地反序列化它。

To read data from the database, you can use the SqlCommand class method that executes SQL queries, namely ExecuteXmlReader( ). ExecuteXmlReader( ) returns an instance of XmlReader and that will read your xml data.

要从数据库中读取数据，可以使用执行SQL查询的SqlCommand类方法，即ExecuteXmlReader（）。 ExecuteXmlReader（）返回XmlReader的一个实例，它将读取您的xml数据。

#1