C# 我可以在 SQL Server 数据库中保存“对象”吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1299410/
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
Can I save an 'Object' in a SQL Server database?
提问by Ahmad Farid
I want to save an object (of any type) into a field in a database in SQL Server 2005. Is it possible? Do I have to convert the object into something, like a byte array for example and cast it back when retrieving it?
我想将一个对象（任何类型）保存到 SQL Server 2005 数据库中的一个字段中。这可能吗？我是否必须将对象转换为某些东西，例如字节数组，并在检索它时将其转换回？
采纳答案by marc_s
You can use the VARBINARY(MAX)field type in SQL Server, if you like. You can store any type of object in there, up to 2 GB in size.
VARBINARY(MAX)如果愿意，您可以在 SQL Server 中使用字段类型。您可以在其中存储任何类型的对象，最大大小为 2 GB。
To access it, you can use ADO.NET - something like this:
要访问它，您可以使用 ADO.NET - 像这样：
object yourMysteryObject = (whatever you like it to be);
MemoryStream memStream = new MemoryStream();
StreamWriter sw = new StreamWriter(memStream);
sw.Write(yourMysteryObject);
SqlCommand sqlCmd = new SqlCommand("INSERT INTO TableName(VarBinaryColumn) VALUES (@VarBinary)", sqlConnection);
sqlCmd.Parameters.Add("@VarBinary", SqlDbType.VarBinary, Int32.MaxValue);
sqlCmd.Parameters["@VarBinary"].Value = memStream.GetBuffer();
sqlCmd.ExecuteNonQuery();
Marc
马克
回答by Thinker
To do this you need to serialize your object. You can look here at examples:
为此，您需要序列化您的对象。您可以在此处查看示例：
回答by Marc Gravell
As others have said, serialization may be the key here (assuming you don't want to use ORM to store the properties as columns in a table, which seems much more direct).
正如其他人所说，序列化可能是这里的关键（假设您不想使用 ORM 将属性存储为表中的列，这似乎更直接）。
Some caveats though; a database is:
不过有一些警告；一个数据库是：
- long term storage
- not related to your .NET code
- 长期储存
- 与您的 .NET 代码无关
As such, you do notwant to use any serialization technique that is platform-specific or version-specific. You will often see people mention BinaryFormatterfor persistance, but this falls into both of the above traps. You would be scuppered if you ever changed platform, or even if you just change some properties.
因此，您不希望使用任何特定于平台或特定于版本的序列化技术。你会经常看到人们提到BinaryFormatter坚持，但这落入了上述两个陷阱。如果你改变了平台，或者即使你只是改变了一些属性，你都会被挫败。
You need an implementation-independent approach; the simplest (which also retains the ability to be human readable) is xml or json, perhaps via XmlSerializeror Json.NET (stored in a [n]varchar(max)). If you don't care about human readable, "protocol buffers" (fast/binary) would do well (stored in a varbinary(max)), and is available for most platforms(including C#/.NET/etc).
您需要一种独立于实现的方法；最简单的（也保留了人类可读的能力）是 xml 或 json，可能是通过XmlSerializer或 Json.NET（存储在[n]varchar(max). 如果您不关心人类可读性，“协议缓冲区”（快速/二进制）会做得很好（存储在 a 中varbinary(max)），并且可用于大多数平台（包括 C#/.NET/等）。
回答by Badaro
I would use JSONto convert the object to a string, and store it in a VARCHAR or TEXT field. Not only the data is stored in a human-readable format, but it's also also readable from different languages, since pretty much every mainstream language has a JSON parser available.
我会使用JSON将对象转换为字符串，并将其存储在 VARCHAR 或 TEXT 字段中。不仅数据以人类可读的格式存储，而且还可以从不同的语言读取，因为几乎每种主流语言都有可用的 JSON 解析器。
The link I posted has links to several libraries in many languages (including C#), I have used this onea couple times in the past.
我发布的链接包含指向多种语言（包括 C#）的多个库的链接，我过去曾多次使用过这个库。
回答by Ivo Stoyanov
Here is an example if you are using Entity Framework (EF):
如果您使用的是实体框架 (EF)，则这是一个示例：
using (DbContext db = new DbContext())
{
// The object that you want to serialize. In this case it is just an empty instance
YourObject objectToSerialize = new YourObject();
IFormatter formatter = new BinaryFormatter();
using (MemoryStream stream = new MemoryStream())
{
formatter.Serialize(stream, objectToSerialize);
// EF model. In one of its properties you store the serialized object
YourModel modelObject = new YourModel();
// In your model 'SerializedObject' should be of type byte[]. In the database it should be of type varbinary(MAX)
modelObject.SerializedObject = stream.ToArray();
db.YourModel.Add(modelObject);
db.SaveChanges();
}
}
And this is how to de-serialize the object:
这是反序列化对象的方法：
// De-serialize
IFormatter formatter = new BinaryFormatter();
Stream stream = new MemoryStream(serializedObject);
YourObject deserializedYourObject = (YourObject)formatter.Deserialize(stream);
stream.Close();
