Serialization in Database
 
Published: 08 Jan 2007
Abstract
Serialization in databases is mainly used to store images as BLOBs (Binary Large OBjects), but it can also be used to store customized objects as per user's requirements, without altering or loosing the state of the object. This article aims at throwing some light about the basics of serialization and its use in databases.
by Bhuban Mohan Mishra
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 46978/ 121

Introduction

Serialization is the process of converting an object into a stream of bytes which is easily transferable over the network and storing its current state on any permanent storage media like file or database for later use. De-serialization is the reverse process of Serialization, where the stored object is taken and the original object is recreated.

.NET provides classes through its System.Runtime.Serialization namespaces that can be used for serializing and de-serializing objects.

Figure 1: Serialization and De-serialization

 

Serialization can be divided into following types:

·         Binary Serialization: Binary serialization allows the serialization of an object into a binary stream and restoration from a binary stream into an object.

·         XML Serialization: XML serialization allows the public properties and fields of an object to be reduced to an XML document that describes the publicly visible state of the object.

·         SOAP Serialization: SOAP serialization is similar to XML serialization in that the objects being serialized are persisted as XML.

·         Custom Serialization: If the default serialization classes are insufficient due to the requirements, then this can be customized by implementing the ISerializable interface.

We will be dealing only with Binary Serialization as it is used for serialization in databases. In .NET, this facility is provided by the BinaryFormatter class present in System.Runtime.Serialization.Formatters.Binary namespace.

Serialization in Databases

In databases, serialization is mainly done to store images. All databases provide the feature to store images. For example:

·         IMAGE data type in MS SQL: It can hold variable length binary data with a maximum length of 2 GB.

·         BLOB, MEDIUMBLOB and LONGBLOB in MySQL: BLOB refers to Binary Large Objects that can store up to 64 KB of data. MEDIUMBLOB can store up to 16 MB and LONGBLOB stores up to 4 GB of binary data. BLOBs can store a variable amount of data.

Let us consider a small table DEMO consisting of 3 columns.

Column

Data type

ID

INTEGER (AUTO_INCREMENT)

NAME

VARCHAR(100)

PHOTO

IMAGE (in case of MS SQL)

 

MEDIUMBLOB (in case of MySQL)

Case I (Uploading image from file)

Serializing and uploading an image on to a database is a three step process.

1.    Create a MemoryStream object from an image file. A MemoryStream class creates a stream in memory instead of a disk. In MemoryStream, data is stored as an unsigned byte array that is directly accessible in memory.

2.    Serialize the MemoryStream into binary format through a BinaryFormatter. BinaryFormatter is the class that is used to serialize and de-serialize any object into a binary stream.

3.    Upload the bytes formed from the MemoryStream to the database. This is achieved by a simple Insert or Update operation that is carried for any other field in the table. The difference is that the image to be uploaded would be an array of bytes.

NOTE: The Listings here are based on MySQL database. The connection and command objects used here belong to the namespace MySql.Data.MySqlClient. This is found from the MySQL .NET Connector which is an ADO.NET driver for MySQL.

The Listing below consists of two functions.

SerializeNSaveToDB()

This function first creates an Image object from a file and serializes it into a MemoryStream. It then calls the Call_Upload_Object() function to save the serialized object to the database.

Call_Upload_Object(string query, byte[] image, string column)

It takes three arguments, the query, an array of bytes that is the serialized object, in this case the image, and the column name (used in the Query). It then performs the necessary database operation with the help of the classes provided by the MySQL .NET Connector.

Listing 1: Serialization of Image from file

using MySql.Data.MySqlClient;
 
private string imagePath = string.Empty;
private string MySqlConnString =
  "Server = localhost; Uid = root; Pwd = password;
Database = demo;";
private void SerializeNSaveToDB()
{
  MemoryStream str = new MemoryStream();
  try
  {
    string column = "?IMAGE ";
    Image img = Image.FromFile(imagePath);
    BinaryFormatter oBFormatter = new BinaryFormatter();
    oBFormatter.Serialize(str, img);
    byte[]oSerializedImg = str.ToArray();
    string Query = "INSERT INTO DEMO (NAME, PHOTO) VALUES('Image 1',  ? IMAGE)
      ";Call_Upload_Object(Query, oSerializedImg, column);
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
  finally
  {
    str.Close();
  }
}
 
private void Call_Upload_Object(string query, byte[]image, string column)
{
  MySqlConnection oMySqlConnection = new MySqlConnection(MySqlConnString);
  try
  {
    MySqlCommand oMySqlCommand = new MySqlCommand();
    oMySqlConnection.Open();
    oMySqlCommand.Connection = oMySqlConnection;
    oMySqlCommand.CommandText = query;
    oMySqlCommand.Parameters.Add(column, image);
    oMySqlCommand.ExecuteNonQuery();
  }
  catch (Exception ex)
  {
    throw ex;
  }
  finally
  {
    oMySqlConnection.Close();
  }
}

Case II (Downloading an image from Database)

This is also a three step process.

1.    First, the image is fetched into a byte array and is then converted into a stream like MemoryStream or FileStream.

2.    The stream of bytes is now de-serialized by using a BinaryFormatter.

3.    This Stream is then either written to a file or can be directly displayed in a PictureBox.

Listing 2 consists of a single function.

DeserializeNSaveToFile()

This function fetches the serialized image from the database with the help of MySqlClient Classes. This object is converted into a MemoryStream object which is then de-serialized with the help of a BinaryFormatter. This is in turn converted into an Image and can be easily stored into a file or can be directly displayed by a PictureBox in our application.

Listing 2: De-serialization of Image from Database

private void DeserializeNSaveToFile()
{
  DataTable ds = new DataTable();
  MySqlConnection oMySqlConn = new MySqlConnection(MySqlConnString);
  try
  {
    string Query = "SELECT * FROM DEMO";
    MySqlCommand cmd = new MySqlCommand(Query, oMySqlConn);
    oMySqlConn.Open();
    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
    da.Fill(ds);
    MemoryStream oStr = new MemoryStream((byte[])ds.Rows[0]["PHOTO"]);
    BinaryFormatter oBFormatter = new BinaryFormatter();
    Image img = (Image)oBFormatter.Deserialize(oStr);
    img.Save("C:\\demo_image.jpg", ImageFormat.Jpeg);
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
  finally
  {
    oMySqlConn.Close();
  }
}
Working with Custom Objects

Images are one of the primitive objects that one can think of to store in the database. Apart from these, we can also store customized objects in the database. But for this purpose, the class must be serialized. Let us consider an example of an AppSettings class. Here we will save the setting of an application like the window size, position, etc. to a table named SETTINGS.

Listing 3

[Serializable]
public class AppSettings
{
  public int screenX;
  public int screenY;
  public int screenW;
  public int screenH;
  public DateTime lastLogin;
}

The above class can now be used to store the last used settings in the database and on the next login can be retrieved and applied to the application. The example here is a simple class, but this can be customized according to the requirements.

The serialization of the AppSettings object is same as the Image example given above. Here we will use the following two functions.

SerializeNSaveToDB()

This function first creates an AppSettings object and initializes all the required variables and serializes it into a MemoryStream. It then calls the Call_Upload_Object() function to save the serialized object to the database.

DeserializeNSaveToFile()

This function fetches the serialized AppSettings object from the database with the help of MySqlClient Classes. The object is converted into a MemoryStream object which is then de-serialized with the help of a BinaryFormatter. This is in turn converted into an AppSettings object and the settings can now be easily applied to our application.

Listing 4: Working with Custom Objects

private void SerializeNSaveToDB()
{
  MemoryStream str = new MemoryStream();
  try
  {
    string column = "?OBJECT";
    AppSettings oSettings = new AppSettings();
    oSettings.screenX = this.Left;
    oSettings.screenY = this.Top;
    oSettings.screenW = this.Width;
    oSettings.screenH = this.Height;
    oSettings.lastLogin = DateTime.Now;
    BinaryFormatter oBFormatter = new BinaryFormatter();
    oBFormatter.Serialize(str, oSettings);
    byte[]oSerializedObj = str.ToArray();
    string Query =
      "INSERT INTO SETTINGS (NAME, SETTINGS) VALUES('Settings 1',  ? OBJECT)
      ";Call_Upload_Object(Query, oSerializedObj, column);
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
  finally
  {
    str.Close();
  }
}
 
private void DeserializeNSaveToFile()
{
  DataTable ds = new DataTable();
  MySqlConnection oMySqlConn = new MySqlConnection(MySqlConnString);
  try
  {
    string Query = "SELECT * FROM SETTINGS";
    MySqlCommand cmd = new MySqlCommand(Query, oMySqlConn);
    oMySqlConn.Open();
    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
    da.Fill(ds);
    MemoryStream oStr = new MemoryStream((byte[])ds.Rows[0]["SETTINGS"]);
    BinaryFormatter oBFormatter = new BinaryFormatter();
    AppSettings oSettings = (AppSettings)oBFormatter.Deserialize(oStr);
    this.Left = oSettings.screenX;
    this.Top = oSettings.screenY;
    this.Width = oSettings.screenW;
    this.Height = oSettings.screenH;
    txtLastLogin.Text = oSettings.lastLogin.ToString();
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
  finally
  {
    oMySqlConn.Close();
  }
}
References

Conclusion

This was a small effort to bring forward some aspects of Serialization and De-Serialization in the database, especially in MySQL with .NET. The Serialization is one of the most efficient and secured way of transferring data over the network. This is the reason, why it is used mostly in Remoting applications. The image example is one of the common examples one can think of, but Serialization in Database is not limited to this. Serialization can also be used for storing custom objects or custom data types that are not supported by any database and even Application Settings.

Thanks,

Bhuban M. Mishra

Mindfire solutions

 



User Comments

Title: good   
Name: lavanya
Date: 2012-12-27 2:36:26 AM
Comment:
gud article
Title: Good   
Name: Bimla
Date: 2011-12-31 9:34:41 PM
Comment:
I like this article very much..
Title: gud   
Name: kranthi
Date: 2011-06-14 3:05:39 AM
Comment:
GUD...
Title: great   
Name: nadeem
Date: 2010-11-30 10:26:32 AM
Comment:
great thats to cool i have been searching it for around d2 days finally and with gods grace i happen to land here cheers to you weell done !!!
Title: Serialization   
Name: Alekhya
Date: 2009-06-19 8:59:30 AM
Comment:
Excellent
Title: WONDERFUL, BUT...   
Name: GIADDOM VIGALE
Date: 2009-05-20 11:46:18 PM
Comment:
THIS ARTICLE WAS ACTUALLY WHAT I WANTED BUT COULD YOU MAKE AVAILABLE FOR ME AN ARTICLE ON "BINARY SERIALIZATION OF MYSQL BACKEND FOR CLIENT GRID DATA HANDLING"

THANKS.
Title: At Last!!   
Name: Peter Derwa
Date: 2008-10-23 5:13:10 AM
Comment:
At last one proper example of the thing i'm searching for all day.
Thanks allot!
Title: Thank You!   
Name: Vaibhav
Date: 2008-01-23 11:13:33 AM
Comment:
Very well written article and accompanying code.
Title: Thanks   
Name: Emrah Uslu
Date: 2007-12-30 2:09:05 PM
Comment:
The article is very useful and explanatory. Thanks a lot.
Title: gud   
Name: miffa
Date: 2007-09-17 9:44:33 AM
Comment:
It'a a good demo...........
Title: sqls   
Name: Percival
Date: 2007-08-01 3:26:54 PM
Comment:
u hav a greate things and now I'l search here only
Thanks a lot
Title: Serialization   
Name: Bhuban
Date: 2007-06-27 1:17:36 PM
Comment:
Hello Nitin,

Yes, serialization is same where ever you do. The matter is where do you store your serialized data i.e. in a file or in the database.
Title: Serialization   
Name: Nitin Sharma(.Net Developer)
Date: 2007-06-27 3:08:49 AM
Comment:
But how is DataBase Serialization different from the normal serilization?????????????..or is it same??????
Title: Awesome!   
Name: Mike J
Date: 2007-04-12 3:55:02 PM
Comment:
Thanks a ton, Bhuban!
Title: Re: Change in custom class   
Name: Bhuban M Mishra
Date: 2007-04-12 2:18:06 PM
Comment:
Hello Mike,

Just forgot to tell you that if you mark the variable as [NonSerialized], then you will not be able to serialize that variable. If you want to store the new variable in the database, then try the [OptionalField] attribute for the new variable. This should fulfill your requirement.

You can go through the following article as a reference. Look into Versioning.

http://msdn.microsoft.com/msdnmag/issues/04/10/AdvancedSerialization/

Thanks,
Bhuban
Title: Re: Change in custom class   
Name: Bhuban M Mishra
Date: 2007-04-12 1:56:31 PM
Comment:
Hello Mike,

If you want to change the structure and in the same time want to access the old data, then the thing you are referring to is called Versioning. Here, you use your old data as well as new data simultaneously.

The only way to do this is by marking the new property or variable (in your case the screenColorDepthBit variable) with NonSerialized attribute. This is what is referred to as Selective Serialization.

Ex. [NonSerialized] public int screenColorDepthBit;

Though I have not implemented it, I will try to implement it as soon as possible and update you.

Thanks,
Bhuban
Title: Change in custom class   
Name: Mike J
Date: 2007-04-12 1:24:37 PM
Comment:
This is a great write-up. I am planning on using your serialization to store a custom class similarly to your demonstration. I was wondering how a change in that structure might affect existing records in the database. For example, if I were add a property to your AppSettings class called:

public int screenColorDepthBit;

What would happen if I tried to deserialize an object into the new structure? I'll post another if I find out before anyone else does. Thanks!






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2020 ASPAlliance.com  |  Page Processed at 2020-11-28 9:01:44 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search