我可以在SQL Server数据库中保存“对象”吗?

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中的数据库中的一个字段中。是可能的吗?我是否必须将对象转换成某种东西，例如字节数组，并在检索它时将其转换回?

5 个解决方案

#1

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.

如果您愿意，可以在SQL Server中使用VARBINARY(MAX)字段类型。您可以在其中存储任何类型的对象，大小为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

马克

#2

I would use JSON to 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 one a couple times in the past.

我发布的链接以多种语言(包括c#)链接到多个库，我过去曾多次使用过这个。

#3

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 not want to use any serialization technique that is platform-specific or version-specific. You will often see people mention BinaryFormatter for 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 XmlSerializer or 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)中)。如果您不关心人类可读性，“协议缓冲区”(快速/二进制)将会很好(存储在varbinary(max)中)，并且大多数平台(包括c# /. net /等)都可以使用。

#4

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

#5

To do this you need to serialize your object. You can look here at examples:

为此，需要序列化对象。你可以看看这些例子:

http://www.c-sharpcorner.com/UploadFile/bipinjoshi/serializingObjectsinCS11102005234746PM/serializingObjectsinCS.aspx

#1