How to store an object into a database?
-
Hi! I'm going to serialize/save an object into a MS SQL Database ... but how? Hope anybody can help me!! /Kenneth
Off the top of my head, create a binary field in your database, this should be large enough to hold the serialized object; this depends on how many fields/properties the object has (and any sub-objects). Now create a new memory stream, pass the stream into the binary formatters serialize method to serialize the object to the stream. Now fill the field with the contents of the memory stream. Object is now serialized in your database :) That was my plan for a project where I wanted to do the same thing, but work caught up to me so I never got to implement it. HTH, James Sonork ID: 100.11138 - Hasaki "Smile your little smile, take some tea with me awhile. And every day we'll turn another page. Behind our glass we'll sit and look at our ever-open book, One brown mouse sitting in a cage." "One Brown Mouse" from Heavy Horses, Jethro Tull 1978
-
Hi! I'm going to serialize/save an object into a MS SQL Database ... but how? Hope anybody can help me!! /Kenneth
bulow_dk e-mailed and asked if I could provide a sample doing what I suggested; I'm posting it here so that others can see how to do it as well. If you've done binary serialization to a file, doing the same to a database is pretty easy to do, since it just throws in a slight twist. First create a test class, this is the class that will be serialized to the database.
[Serializable()]
public class TestClass
{
private int foo;
public int Foo
{
get
{
return foo;
}
set
{
foo = value;
}
}public TestClass() { Foo = 0; }}
Now comes the fun part :)
public void SaveToDatabase()
{
TestClass foo = new TestClass();
foo.Foo = 3;// Stream to serialize to MemoryStream memStream = new MemoryStream(); BinaryFormatter bf = new BinaryFormatter(); bf.Serialize(memStream, foo); // Serialize foo SqlConnection conn = new SqlConnection( /\* Get connection string \*/ ); SqlCommand cmd = new SqlCommand("AddObject", conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter parm = new SqlParameter("@ObValue", SqlDbType.VarBinary, 8000); parm.Value = memStream.ToArray(); cmd.Parameters.Add(parm); conn.Open(); try { cmd.ExecuteNonQuery(); } catch(SqlException ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); memStream.Close(); }}
Following MS' best practices I use a stored procedure to do my database work. The "AddObject" stored procedure accepts a single parameter, a varbinary type with maximum length of 8000 (a little less than 8K of memory). Now to deserialize the object from the database.
public void LoadObjectFromDatabase()
{
TestClass foo;
long dataSize;MemoryStream memStream; // This stream will eventually hold the data to deserialize BinaryFormatter bf = new BinaryFormatter(); SqlConnection conn = new SqlConnection( /\* Get connection string \*/ ); SqlCommand cmd = new SqlCommand("GetObject", conn); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); // Execute the SP while(dr.Read()) { // Get the amount of data in the field dataSize = dr.GetBytes(0, 0, null, 0, 0); // Create a new byte array to hold the data byte \[\] data = new byte\[ dataSize \]; // Actually fetch the data from the database dr.GetBytes(0, 0, data, 0, (int) dataSize); // Create the memory stream containing the data memStream = new MemoryStream(data); // Deserialize the -
bulow_dk e-mailed and asked if I could provide a sample doing what I suggested; I'm posting it here so that others can see how to do it as well. If you've done binary serialization to a file, doing the same to a database is pretty easy to do, since it just throws in a slight twist. First create a test class, this is the class that will be serialized to the database.
[Serializable()]
public class TestClass
{
private int foo;
public int Foo
{
get
{
return foo;
}
set
{
foo = value;
}
}public TestClass() { Foo = 0; }}
Now comes the fun part :)
public void SaveToDatabase()
{
TestClass foo = new TestClass();
foo.Foo = 3;// Stream to serialize to MemoryStream memStream = new MemoryStream(); BinaryFormatter bf = new BinaryFormatter(); bf.Serialize(memStream, foo); // Serialize foo SqlConnection conn = new SqlConnection( /\* Get connection string \*/ ); SqlCommand cmd = new SqlCommand("AddObject", conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter parm = new SqlParameter("@ObValue", SqlDbType.VarBinary, 8000); parm.Value = memStream.ToArray(); cmd.Parameters.Add(parm); conn.Open(); try { cmd.ExecuteNonQuery(); } catch(SqlException ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); memStream.Close(); }}
Following MS' best practices I use a stored procedure to do my database work. The "AddObject" stored procedure accepts a single parameter, a varbinary type with maximum length of 8000 (a little less than 8K of memory). Now to deserialize the object from the database.
public void LoadObjectFromDatabase()
{
TestClass foo;
long dataSize;MemoryStream memStream; // This stream will eventually hold the data to deserialize BinaryFormatter bf = new BinaryFormatter(); SqlConnection conn = new SqlConnection( /\* Get connection string \*/ ); SqlCommand cmd = new SqlCommand("GetObject", conn); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); // Execute the SP while(dr.Read()) { // Get the amount of data in the field dataSize = dr.GetBytes(0, 0, null, 0, 0); // Create a new byte array to hold the data byte \[\] data = new byte\[ dataSize \]; // Actually fetch the data from the database dr.GetBytes(0, 0, data, 0, (int) dataSize); // Create the memory stream containing the data memStream = new MemoryStream(data); // Deserialize the