Can I save an 'Object' in a SQL Server database?

Question

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?

Answer 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.

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

Answer 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.

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.