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
– David Brabant, Commented Feb 10, 2013 at 12:56

Community · Accepted Answer · 2017-05-23 11:56:23Z

4

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

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Feb 10, 2013 at 12:41

user1968030

Sign up to request clarification or add additional context in comments.

Comments

Christian Phillips · Accepted Answer · 2013-02-10 12:45:21Z

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, 2013 at 12:45

Christian Phillips

18.8k8 gold badges55 silver badges85 bronze badges

3 Comments

Brian P Over a year ago

Why JSON an a third party tool when you can achieve the same goal using XML and native functionality?

Security Hound Over a year ago

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

Christian Phillips Over a year ago

@BrianP, you can get JSON.NET via NuGet and it's widely used. I suggest JSON since it's more lightweight than xml.

Collectives™ on Stack Overflow

Store a C# object in an SQL Server column

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related