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?
-
2You could also serialize out to XML which would be easier to inspect inside the db than binary data – pjp Aug 19 '09 at 11:49
-
1It's not a great DB design. Will you need to query the attributes of the object at any point? e.g. get all objects where object.Foo = 1. – Paddy Aug 19 '09 at 11:49
-
No i don't need to know or query the contents of the tables stored – Ahmad Farid Aug 19 '09 at 11:51
5 Answers
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.
To access it, you can use ADO.NET - something like this:
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
-
-
but this is an image read from a file. What if I have a class or an object created during runtime. Nothing to do with files. – Ahmad Farid Aug 19 '09 at 11:57
-
any type of object can be written to a MemoryStream and thus assigned to the value of the SqlParameter which will then write it down into the database. – marc_s Aug 19 '09 at 12:00
-
how can i write an object to a memory stream? without dealing with the hard disk? can u give me an example plz? – Ahmad Farid Aug 19 '09 at 12:06
-
Look at my code - create a MemoryStream and a StreamWriter and write your object to this MemoryStream -- that's all done in memory, no disk – marc_s Aug 19 '09 at 12:12
-
LOL @ yourMysteryObject :D thx dude :) and when retrieving it back, normal type casting right? – Ahmad Farid Aug 19 '09 at 12:16
-
Well, if you want it back, you'll have to know what you get back! SQL Server is just going to send you any number of bytes - it's YOU who has to know what it is and how to convert the stream of bytes into an object again. – marc_s Aug 19 '09 at 12:18
-
oh what do you mean. I know that it is, I mean its type. Can't I just say like: DataTable dt; while(reader.Read()) { dt = (DataTable)reader[2]; } – Ahmad Farid Aug 19 '09 at 13:08
-
7Huh? If you pass sw.Write() a generic Object, it just calls that Object's ToString method! That doesn't serialize it! – AnotherParker Jan 31 '13 at 15:10
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.
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.

- 3,460
- 1
- 19
- 18
Here is an example if you are using Entity Framework (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();

- 11,507
- 3
- 43
- 82

- 16,256
- 8
- 62
- 65
-
How do you cast it back to the object we just saved... knowing what the original object was obviously. – Piotr Kula Aug 28 '15 at 12:20
-
1@ppumpkin. I have updated my answer with information how to de-serialize the object. Happy coding :) – Ivo Stoyanov Aug 28 '15 at 13:33
-
1The class being serialized must be marked with the Serializable attribute. – Elton Apr 28 '17 at 20:27
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).
Some caveats though; a database is:
- long term storage
- not related to your .NET code
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.
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).

- 1,026,079
- 266
- 2,566
- 2,900
To do this you need to serialize your object. You can look here at examples:

- 14,234
- 9
- 40
- 55
-
1But I don't want to save it on hard disk. I just want to put it in a databse. – Ahmad Farid Aug 19 '09 at 11:53
-
11