将 C# 类直接序列化到 SQL 服务器?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1212555/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-06 11:03:47  来源:igfitidea点击:

Serialize C# class directly to SQL server?

c#.netsql-serverserialization

提问by mark smith

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

任何帮助真的很感激

采纳答案by NeedHack

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.

然后像其他任何字符串一样将字符串放入数据库中。

回答by Marc Gravell

The bestway 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 isalso 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.

但我强调;列会更好。

回答by Cheeso

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

查看 Linq-to-SQL(关于 SO 的问题MSDN上的资源)或其他OR 映射选项。

回答by John Farrell

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 或 Binary 格式。

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 查询功能对我们来说没有问题。

回答by Srikar Doddi

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 数据。

回答by Ales Ruzicka

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();
}