How to read BLOBs and display them

• Download demo project - 13.9 Kb

Introduction

Recently, I developed an E-Commerce site, a mobile phone shopping mall, where pages and its contents are created dynamically when the customer selects his/ her preferred mobile phone. The image of the phone and logo of the phone vendor are stored in the database as BLOB. I wanted to read this BLOB and display it on customer request. I created a sample application, for reading the BLOB and displaying it, to demonstrate this.

I use the pubs database for this example, which is created when SQL Server is installed. I populate the publication IDs from the pub_info table to a dropdown list, and when the selection is done, the appropriate logo will be displayed.

Using the code

Create a separate aspx file to hold the image. An aspx file for holding the image? Are you kidding? Wait, look what is happening. Create a method in the code-behind file of the aspx page to read a BLOB value. CreateImage(string id) is the method that reads the BLOB. The string id parameter gets the image ID that the user selects from the drop down list. To read the BLOB value, use the ExecuteScalar() method of the SqlCommand class. ExecuteScalar() returns an object, so we should cast it and store in a byte array like this.

byte[] _buf = (byte[]) _cmd.ExecuteScalar();

Then, stream it back to the HTTP response. The complete code is shown below:

void  CreateImage(string id)
{
    // Connectoin string is taken from web.config file.

    SqlConnection _con = new SqlConnection(
      System.Configuration.ConfigurationSettings.AppSettings["DB"]);
        
    try
    {
        _con.Open();
        SqlCommand _cmd = _con.CreateCommand();
        _cmd.CommandText = "select logo from" + 
                           " pub_info where pub_id='" + 
                           id + "'";
        byte[] _buf = (byte[])_cmd.ExecuteScalar();
        
        // This is optional

        Response.ContentType = "image/gif";
        
        //stream it back in the HTTP response

        Response.BinaryWrite(_buf);
                
                
    }
    catch
    {}
    finally
    {
        _con.Close();
                
    }
}

In the Page_Load(), call CreateImage().

private void Page_Load(object sender, System.EventArgs e)
{
    if(!IsPostBack)
    {
        CreateImage(Request["id"]);
    }
}

The page retrieves the image ID using a query string. Now, I'm going to display the image. In order to display the image, use a HtmlServerControl where you want. Set its src property like this.

<img src='<%# "imgs.aspx?id=" + drpIds.SelectedValue %>'>

To evaluate this magical data binding formula, we should call:

Page.DataBind();

in the drop down list's SelectedIndexChanged event.

That's all. Run the application and enjoy it.