ASP.Net & SqlServer – Displaying binary data from database as images

A frequent requirement for ASP.Net websites is to display image data from a database that is stored as binary data in a table.

One such example is the “Production.ProductPhoto” table in the AdventureWorks database. The table has a ThumbNailPhoto and a LargePhoto column which store images as binary data.

So how does one go about displaying this data using ASP.Net server controls?

The first thing you need to know – you cannot provide the binary data directly to any control to display the image.

What you need to do, is to have a separate page that pulls the data from the database and then pushes the data down to the browser as an image. And luckily this is very easy to do.

The following example uses the Production.ProductPhoto and the Production.ProductProductPhoto tables from the AdventureWorks database.

 

The productId is looked up in the “ProductProductPhoto” table and the “ProductPhotoId” is retrieved. The “ProductPhotoId” is then used on the “ProductPhoto” table to extract the correct record with the product image.

Create a web-form page. Call it “DisplayProductImage.aspx”.

Add a label to the page. Set its Text value to “Product ID:”.

Next add a text box to the page. Set its ID to “txtProductID”.

Pull a button from the Toolbox onto the page. (We wont hook up any events to the button. We will use the fact that when the button is clicked – a post-back will automatically occur).

Pull a DetailsView on to the page. In the tasks list, select “New data source”.

Next, select Database and then click Ok

On the next screen point to the AdventureWorks database connection string in your web.config file. (If you dont have one – then you need to create a connection string for it. The simplest method is to create a DataSet using the VS wizard).

Choose “Specify a custom SQL Statement” for “How would you like to retrieve data from your database?”

Paste the following query as the SQL Statement. (The query gets the ProductPhotoID, using the ProductID as a input parameter):

SELECT Production.ProductPhoto.ThumbNailPhoto, Production.ProductPhoto.LargePhoto, Production.ProductProductPhoto.ProductID FROM Production.ProductPhoto INNER JOIN Production.ProductProductPhoto ON Production.ProductPhoto.ProductPhotoID = Production.ProductProductPhoto.ProductPhotoID WHERE (Production.ProductProductPhoto.ProductID = @ProductID)

On the next screen, you need to specify where the query parameter ProductID will get its value. In our example it is the “txtProductID” control.

Click on Finish.

Next, we need to add ImageFields to display the images.

To do this – you need to go to the “Edit Fields” dialog for the DetailsView.

Add 2 imagefields to the DetailsView.

Set the following values for the first ImageField:

 

 ProductImage.aspx?ThumbNail=true&ProductID={0}

Set the following values for the second ImageField:

 

ProductImage.aspx?ThumbNail=false&ProductID={0}

This will create a details view which will display the ProductID, a thumbnail image and a full sized image of the product.

 

The data-binding for the images, sets up the image source to point to a special page within our website (which we will create next), and passes the productID as a query parameter, as well as tells the page where the data is to be retrieved from – using the parameter “ThumbNail”.

Creating the image.

To display the image from the database we need to: 1. retrieve the binary data, 2. Serialize it to an image 3. pass it down to the browser.

1. Add a new web-form page. Call the page “ProductImage.aspx”

2. In the code behind for the page, add the following code for the Page_Load event.

protected void Page_Load(object sender, EventArgs e)
    {
        //retrieve query params
        if (Request.QueryString["ProductID"] == null)
            return;
        int productID = Convert.ToInt32(Request.QueryString["ProductID"]);
        bool isThumbNail = true;
        if (Request.QueryString["ThumbNail"] != null)
            isThumbNail = Convert.ToBoolean(Request.QueryString["ThumbNail"]);

        //using the productId, retrieve the photoid.
        int photoId = -1;
        byte[] imageData = null;
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString))
        {
            SqlCommand command = new SqlCommand("SELECT ProductID, ProductPhotoID FROM Production.ProductProductPhoto WHERE (ProductID = @ProductID)", connection);
            command.CommandType = System.Data.CommandType.Text;
            command.Parameters.AddWithValue("ProductID", productID);
            connection.Open();
            SqlDataReader sdr = command.ExecuteReader();
            if (sdr.HasRows)
            {
                sdr.Read();
                photoId = Convert.ToInt32(sdr["ProductPhotoID"]);
            }
        }
        if (photoId < 0)
            return;
        //using the photoid get the binary data for the thumbnail and actual image
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString))
        {
            SqlCommand command = new SqlCommand("SELECT Production.ProductPhoto.ThumbNailPhoto, Production.ProductPhoto.LargePhoto from Production.ProductPhoto WHERE (ProductPhotoID = @ProductPhotoID)", connection);
            command.CommandType = System.Data.CommandType.Text;
            command.Parameters.AddWithValue("ProductPhotoID", photoId);
            connection.Open();
            SqlDataReader sdr = command.ExecuteReader();
            if (sdr.HasRows)
            {
                sdr.Read();
                if (isThumbNail)
                    imageData = (byte[])sdr["ThumbNailPhoto"];
                else
                    imageData = (byte[])sdr["LargePhoto"];
            }
        }
        
        //serialize the imagedata to an image and send it back as part of the response stream
        int strippedImageLength = imageData.Length;
        if (strippedImageLength > 0)
        {
            byte[] strippedImageData = new byte[strippedImageLength];
            Array.Copy(imageData, 0, strippedImageData, 0, strippedImageLength);
            //Set the response type to an image and write the data to the response
            Response.ContentType = "image/bmp";
            Response.BinaryWrite(strippedImageData);
        }
    }

This code, sets up the ProductImage.aspx page to retrieve the data from the database and then returns it as an image that is part of response stream.

Testing:

Set the start page to “DisplayProductImage.aspx” (the first one we worked with – which has the controls to specify the product ID and a button to retrieve the image).

Run the sample.

Type 770 as a test product id and click “Get Image”

“Get Image” will trigger a post-back. The post-back will make the DataView bind. The ImageList controls will call the “ProductImage.aspx” page with the product ID 770. Depending on which ImageList is data-binding at that  moment, the thumb-nail parameter will either be true or false.

In the Page_Load event of the ProductImage page, the query parameters are retrieved. The product id is then used to query the database and get the binary data for the images. The binary data is then sent back to the browser as an image. The result should look like this:

 

(Other product Ids to test with – 790, 800, 820)