Store a C# object in an SQL Server column

Question

I have a class in C# which is used to store data (like a struct). It has subclasses as members, which have other subclasses, etc. Each of them contains data (various Strings, ints, floats, etc.)

Is there a way to store one such C# object in a database, in a single column? What type of column would that be and how can I use it?

The only thing I can think of is to convert my struct to a byte sequence using an algorithm and to store it in a field of binary type.

I would then use a decoding algorithm to get it back and make it an object again.

Even so, is there such an algorithm in C# ready to use?

Why would you store an object in your database? I can see a zillion problems hiding behind that strategy. — David Brabant, Feb 10 '13 at 12:56

score 4 · Accepted Answer · edited May 23 '17 at 11:56

see this quetion 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(memStm);

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

score 2 · Answer 2 · answered Feb 10 '13 at 12:45

2

You could serialize the object to JSON, using JSON.NET (http://json.codeplex.com/), and store the JSON data in the database. When you need to re-hydrate the object with the data, this can be done with JSON.NET again...

The documentation is here, and it's easy to use and very fast : http://james.newtonking.com/projects/json/help/

answered Feb 10 '13 at 12:45

Christian Phillips

18,399
8
53
82

2

Why JSON an a third party tool when you can achieve the same goal using XML and native functionality? – Brian P Feb 10 '13 at 13:59
1

@BrianP - Feel free to post a solution using XML, I see many problems with using XML, instead of JSON designed to serialize data exactly like this. – Security Hound Feb 10 '13 at 14:42
2

@BrianP, you can get JSON.NET via NuGet and it's widely used. I suggest JSON since it's more lightweight than xml. – Christian Phillips Feb 11 '13 at 09:38

Store a C# object in an SQL Server column

2 Answers2