Serialization in Database
page 2 of 5
by Bhuban Mohan Mishra
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 54798/ 114

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

View Entire Article

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-2017 ASPAlliance.com  |  Page Processed at 2017-08-23 8:58:16 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search