Serialize/Deserialize Classes into SQL Server Image Columns using the BinaryFormatter

By Peter A. Bromberg, Ph.D.
Printer - Friendly Version

Peter Bromberg

Recently on our eggheadcafe.com forums somebody posted a question about how to save a large float array into SQL Server. I answered briefly there, but after thinking about it for a while, I realized that not only would it be a good subject for a more comprehensive article, but also that it was code that I'd likely need to use myself later on. So I went ahead and built a little test Winforms app that creates an instance of a sample class, populates the class with two public members, a string value and a random array of floats, and then uses the BinaryFormatter to serialize the class into a MemoryStream and save it to the "Photo" column in the Employees table of the trusty old Northwind SQL Server database. We then have a method that retrieves the Photo (type image) column, deserializes it (again using the BinaryFormatter class) and rehydrates it into a new instance of our sample class and displays the name along with some of the float array as a sort of "proof of concept".



I have two basic methods , "SerializeAndSave", and "RetrieveAndDeserialize", that do the actual serialization , and both call helper methods "SavetoDB" and RetrieveFromDB" that do the actual saving or retrieving to / from the database.

If you have ever decompiled the System.Runtime.Serialization.Formatters.Binary namespace (it's in MsCorLib.dll) then you know it is huge and complex. Actually, there are over 66 separate classes, interfaces and enumeration in it. The reason it is so complex is because it is used in a lot of the .NET platform "under the hood" and it needs to be able to accurately serialize virtually any .NET type into a compact object graph in the form of a byte array - exactly what we need to store our live instance of any class including all its methods, properties, members ( yes, even an array of floats) into a database.

Now you might be asking, "why would I want to store a live instance of a class into a database?". There are a number of reasons why this might be useful.

First, you may have found that storing state data in your business logic layer is both more convenient and faster if you can store the instance of the class itself, rather than coming up with some method to get the data out and then save it into the database.

Second, if you are running some sort of distributed application you may wish for storage of class instances and their data so that another instance of the application on another machine on your network can retrieve the class instance from the database and continue processing on it. Or, it could be used to resurrect the business layer "state" on a different machine in a failover type of situation. It would also be possible to design a sort of "Database Message Queuing" infrastructure where for example, a Windows Service periodically polls a database table for a specific status message that was created by the saving of a class instance into the RDBMS and uses this as it's "queue" message to retrieve a specified class instance and perform some work on it. An interesting alternative to MSMQ, possibly without some of the overhead and problems (at least that I've seen) in using Message Queueing.

Finally, and certainly not least important, it could prove to be a very elegant way to archive or persist business logic state data (which, by the way, can also be encrypted prior to storage in the database) and therefore be able to resurrect it for reporting or auditing purposes at any time.

Without further ado, let's jump into the sample code:



private void button1_Click(object sender, System.EventArgs e)
     {    
       SerializeAndSave();
     }

private void button2_Click(object sender, System.EventArgs e)
     {
        RetrieveAndDeserialize();
      }
public void SerializeAndSave()
     {
       try {
        // instantiate a MemoryStream and a new instance of our class
         
        MemoryStream ms = new MemoryStream();
        ClassToSerialize c=new ClassToSerialize(txtName.Text);
          // create a new BinaryFormatter instan
ce
          BinaryFormatter b=new BinaryFormatter();
         // serialize the class into the MemoryStream

          b.Serialize(ms,c);
          ms.Seek(0,0);
         // Show the information

          textBox1.Text="Ms Length: " + ms.Length.ToString();
          int res=SaveToDB(txtName.Text,ms.ToArray());
          textBox1.Text+="\nDB RetVal: "+res.ToString() + "\n";
          //Clean up

          ms.Close();
         }
         
catch(Exception ex)
         {
           textBox1.Text=ex.Message;
         }
     }
public void RetrieveAndDeserialize()
  {
   MemoryStream ms2 = new MemoryStream();
   byte[] buf = RetrieveFromDB(txtName.Text);
   ms2.Write ( buf,0,buf.Length );
   ms2.Seek(0,0);
   BinaryFormatter b=new BinaryFormatter();
   ClassToSerialize c=(ClassToSerialize)b.Deserialize(ms2);    
  textBox1.Text+="Deserialized Name: " +c.name + "\n";
   textBox1.Text+="Portion of Deserialized float array: \n";
   for(int j =0;j<100;j++)
   {
    textBox1.Text+=c.fltArray[j].ToString() +"\n";
   }
   ms2.Close();
  }
 private int SaveToDB(string imgName, byte[] imgbin
  {
   SqlConnection connection = new SqlConnection("Server=(local);DataBase=Northwind;User Id=sa;Password=;");

   SqlCommand command = new SqlCommand( "INSERT INTO Employees (firstname,lastname,photo)
        VALUES  (@img_name, @img_name, @img_data )", connection );
   // (need to write something to first and lastname columns
   // because of constraints)

  SqlParameter param0 = new SqlParameter( "@img_name", SqlDbType.VarChar,50 );
  param0.Value = imgName;
  command.Parameters.Add( param0 );
  SqlParameter param1 = new SqlParameter( "@img_data", SqlDbType.Image );   
  param1.Value = imgbin;
  command.Parameters.Add( param1 );
  connection.Open();
  int numRowsAffected = command.ExecuteNonQuery();
  connection.Close();
  return numRowsAffected;
  }
  private byte[] RetrieveFromDB(string lastname)
  {
  SqlConnection connection = new   SqlConnection("Server=(local);DataBase=Northwind; User Id=sa;Password=;");   SqlCommand command = new SqlCommand("select top 1 Photo from Employees
    where lastname ='"+lastname +"'", connection );
  connection.Open();
  SqlDataReader dr = command.ExecuteReader();
  dr.Read();
  byte[] imgData = (byte[])dr["Photo"];
  connection.Close();
  return imgData;
 }
}// end class
 [Serializable]
  public class ClassToSerialize {
  public string name;
  public float[] fltArray;
  // constructor initializes name and creates the sample array of floats

  public ClassToSerialize(string theName) {
  this.name=theName;
  float[] theArray= new float[1000];
  Random rnd = new System.Random();
  for(int i =0;i<1000;i++)
  theArray[i]=(float)rnd.NextDouble() *1000;
  fltArray=theArray;
  }
 }
}

I believe the above sample code, if you follow the logic, should provide a pretty good basis for a set of methods that can serialize and store, or retrieve, deserialize and rehydrate any class with a provision for identifying the instance. Of course in real life you'll probably want to have a DateTime column and maybe a few other items that are pertinent to your business situation in terms of being able to identify the record that needs to be retrieved. When you think about it, the combination of the ISerializable interface, the BinaryFormatter and image or varbinary columns in SQL Server to store your data is a very powerful combination indeed.

Download the code that accompanies this article


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.