Binary Large Objects

“How do we store an image in a database, and how do we read it back?” is a frequently asked question. This article gives the answer. In this article we will see how ADO.NET is used to store BLOBs (images) in a database and read them back.

We will create two applications. One that will store information of books into a database, and another that will retrieve this information and display it. The information consists of the book name, the author name and an image representing the cover page of the book. For this we have maintained a database called ‘books’, having a table called ‘bookinfo’ and having entries such as bookname (char), author (char) and logo (image). Logo represents the cover page of the book.

Create a WinForm application and design the form as shown in the following figure.

The controls and their names are shown in the following table

Using the ‘Browse’ button the user selects the image file of the book cover. On clicking the ‘Insert’ button the book information gets stored in the database.

First of all, to add entries in the table we need to open the connection to the data source. For this we must declare a connection string and a connection object. We have done it in the Form1 class as shown below:

string constr ;
SqlConnection con ;

Declare the System.Data.SqlClient namespace at the beginning of the program to make available the SqlConnection class. In the constructor of the Form1 class add the following statement to initialise the connection string and connection object and also to open the connection.

constr = “server=kicitsqlserver;
database=books;
uid=sa;pwd=kicit” ;
con = new SqlConnection ( constr ) ;
con.Open( ) ;

Once the connection is established, the user is free to input the book information. The user would enter the book name and author name and then select the image file by clicking the ‘Browse’ button. So, next we would add the Click event handler for the ‘Browse’ button. The handler is given below:

private void browse_Click (
object sender, EventArgs e )
{
OpenFileDialog ofd = new OpenFileDialog( ) ;
ofd.Filter = “Image files |*.jpg” ;
if ( ofd.ShowDialog( ) == DialogResult.OK )
file.Text = ofd.FileName ;
}

Here, we have first created an object of the OpenFileDialog class and collected its reference in ofd. Next we have used the Filter property to specify the file type we wish to browse. Here we have specified that we wish to browse for all the ‘jpg’ files. We have then displayed the Open File Dialog by calling the ShowDialog( ) method and checked whether the user clicked on the OK button of this dialog or not. If he has, then we have initialised the Text property of the file text box with the file name returned by the FileName property.

After filling in all the information, we need to click on the ‘Insert’ button. As soon as we click on this button the insert_Click( ) handler is called. This handler is given below:

private void insert_Click ( object sender, EventArgs e )
{
FileStream f = new FileStream (
file.Text, FileMode.Open ) ;
byte[ ] buff = new byte [ f.Length ] ;
f.Read ( buff, 0, ( int ) f.Length ) ;
string cmdstr = “Insert into bookinfo values (
@b, @a, @l )” ;
SqlCommand com = new SqlCommand (
cmdstr, con ) ;
com.Parameters.Add ( “@b”, book.Text ) ;
com.Parameters.Add ( “@a”, author.Text ) ;
com.Parameters.Add ( “@l”, buff ) ;
com.ExecuteNonQuery( ) ;
con.Close( ) ;
}

Here we have first created a FileStream object by passing the file name and FileMode to it. We have read the file name from the file textbox. Next we have created an array of bytes called buff having length equal to the length of the data that is in the file stream. Then we have read the file into the byte array using the Read( ) method. Next, we have created a command string that would insert the information into the database.

We have used the parameterised command to add the values to the fields in the data source. So, in the command string we have specified the parameters as @b, @a and @l for the book name, author name and cover page logo respectively. The advantage of using parameterised commands is that we can change the values of some or all the fields again and again without having to create the command string again and again. The Parameters property of the SqlCommand class returns the collection of parameters in the form of a reference to SqlParameterCollection class object. We have added the parameter to this collection by calling the Add( ) method of the SqlParameterCollection class. Next we have called the ExecuteNonQuery( ) method that would actually fill the database. Lastly we have closed the connection with the Close( ) method.

To be able to use the FileStream class we need to use the System.IO namespace in our application.

Let us now create an application for accessing and retrieving BLOBs stored in the bookinfo table. Create a Winform application. Add two Labels and one PictureBox to display the book name, author name and the cover page respectively. The UI is shown in the following figure.

Change the names of controls as shown in the following table.

On clicking on the ‘Next’ button the information of the next book stored in the database gets displayed.

Add the following data members to the Form1 class.

string constr ;
SqlConnection con ;
string cmdstr ;
SqlCommand com ;
SqlDataReader r ;
Initialise them in constructor after the call to the InitializeComponent( ) method as shown below:
constr = “server=kicitsqlserver;
database=books;uid=sa;
pwd = kicit” ;
con = new SqlConnection ( constr ) ;
cmdstr = “Select * from bookinfo” ;
com = new SqlCommand ( cmdstr, con ) ;

Also add the code to open the connection and display the details of first book as shown below:

con.Open( ) ;
r = con.ExecuteReader( ) ;
if ( r.Read( ) )
{
bookl.Text = r [ 0 ].ToString( ) ;
authorl.Text = r [ 1 ].ToString( ) ;
byte[ ] b = ( byte[ ] ) r [ 2 ] ;
MemoryStream st = new MemoryStream( ) ;
st.Write ( b, 0, b.Length ) ;
Image i = Image.FromStream ( st ) ;
img.Image = i ;
}

After opening connection to database we have retrieved the reference of the SqlDataReader object in r by making a call to the ExecuteReader( ) method. Then we have checked whether the Read( ) method returns a true or false depending on the existence of a row. If it returns a true, we have extracted the book name and the author name and displayed them in the corresponding textboxes.

Now we need to retrieve the BLOB. We have collected the BLOB in a byte array b. Realise that we cannot directly display the contents of b in the PictureBox because it is in not in a bitmap format. Hence we must first convert the byte array into a bitmap.

To do so we have created an object of the MemoryStream class and collected its reference in st. MemoryStream class is used when the backing store is memory. Recall how we had created a FileStream object in the previous example where we had read from a file. Next, using the Write( ) method of the MemoryStream class we have written the contents of the byte array into the stream. Then we have created a bitmap by passing the reference of the MemoryStream object to the constructor of the Image class. Next we have displayed the image on the PictureBox.

As soon as the user clicks the ‘Next’ button the next_Click( ) handler gets called. This handler is shown below:

private void next_Click ( object sender, EventArgs e )
{
if ( r.Read( ) )
{
bookl.Text = r [ 0 ].ToString( ) ;
authorl.Text = r [ 1 ] .ToString( ) ;
byte[ ] b = ( byte[ ] ) r [ 2 ] ;
MemoryStream st = new MemoryStream( ) ;
st.Write ( b, 0, b.Length ) ;
Image i = Image.FromStream ( st ) ;
img.Image = i ;
}
}

In this handler we have used the same logic. Every time the user clicks this button, information of the next book stored in the database gets displayed.

Yashavant Kanetkar, one of the first Express Computer columnists, is an established software expert, speaker and author with several best-sellers to his credit, including titles like “Let Us C” and the “Fundas” series. Contact him at kanetkar@dcubesoft.com