Reading BLOBs from SQL Server and
display it in a Windows Form PictureBox

More Information on installing the .Net Framework click here.
Download full Visual Studio C# .NET Examples from this Article.

Overview

It's common for modern databases to contain large binary objects, more commonly referred to as BLOB's (Binary Large Objects). BLOBs are typically graphical images such as photos contained in .bmp, .jpg or .tif files. Although the database is fully capable of storing BLOB data, the potential size of these objects means that they must be accessed and managed differently than standard text and numeric data types.

SQL Server used three different data types for BLOB storage:

Text - The Text data type can accommodate up to 2GB of non-Unicode text data.

nText - The nText data type can accommodate up to 1GB of Unicode text data.

Image - The Image data type can store up to 2GB of binary data, which also enables it to store standard text data.

The following example shows how to retrieve Image data from the SQL Server database Northwind and displaying it in the Windows Form Control PictureBox. If you are using a DataSet, you can retrieve an employee's name and photo by using a simple command such as SELECT name, photo FROM employees WHERE id = 1.

Binding to BLOB Fields

The Windows Forms data binding works fine with BLOB fields (that is, images), but not in the default way. If you try to bind the Employees.Photo column to the Image property of a PictureBox, you get an exception.

The exception originates from a clear incompatibility between the desired type (System.Drawing.Image) and the contents of the Photo field, which is resolved to System.Byte[]. To make things even more interesting, the Northwind's Employees pictures need some work to become really usable.

The net effect of all these issues is that you need more than a simple conversion to successfully bind to images. Nevertheless, the Format event lets you easily accomplish the tasks. So your code would look something like this:

Binding bdPhoto = new Binding("Image",_dataSet,"Employees.Photo");
bdPhoto.Format += new ConvertEventHandler(this.PictureFormat);
Photo.DataBindings.Add(bdPhoto);

The event handler does the job of transforming an array of bytes into a Bitmap object that can be safely assigned to the Image property of a PictureBox control.

private void PictureFormat(object sender, ConvertEventArgs e)
{
    // e.Value is the original value
    Byte[] img = (Byte[])e.Value;

    MemoryStream ms = new MemoryStream();
    int offset = 78;
    ms.Write(img, offset, img.Length - offset);
    Bitmap bmp = new Bitmap(ms);
    ms.Close();

   // Writes the new value back
    e.Value = bmp;
}

The array of bytes read from the SQL Server table is first copied into a MemoryStream object. This step is necessary because in .NET you can't create graphic objects directly from an array of bytes. Instead, wrapping the same bytes into a stream object meets the expectations of at least one constructor of the Bitmap class. Normally, the database BLOB field contains only the image itself. Unfortunately, this is not the case with Northwind, in which images are prefixed with a 78-byte header. So, to create a valid object, you must skip those bytes and pass the excerpt to the Bitmap's constructor. Generally speaking, this is a practical demonstration of how you can perform any kind of task prior to binding data. When a reasonable match between the source and target types has been reached, you replace the current content of the Value property. After that, the method returns.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.IO;
using System.Data;
using System.Data.SqlClient;

namespace Akadia.SqlBlob
{
    // Load Photo (Image) from a SQL Server Database into a Picture Box
    public class SqlBlob : System.Windows.Forms.Form
    {
        private System.Windows.Forms.Button ButtonNext;
        private System.Windows.Forms.Button ButtonPrev;
        private System.Windows.Forms.Label Label4;
        private System.Windows.Forms.PictureBox Photo;
        private System.Windows.Forms.Label Label5;
        private System.Windows.Forms.Label Label3;
        private System.Windows.Forms.Label Label2;
        private System.Windows.Forms.StatusBar AppStatusBar;
        private System.Windows.Forms.TextBox hired;
        private System.Windows.Forms.TextBox position;
        private System.Windows.Forms.TextBox lastName;
        private System.Windows.Forms.TextBox firstName;
        private System.ComponentModel.Container components = null;

        // Fields
        private BindingManagerBase _bmbEmployees;
        private DataSet _dataSet;

        // Constructor
        public SqlBlob()
        {
            InitializeComponent();
        }

        .....
        .....

        // Load Data from the Database into the Dataset
        // (Runs when Form is loaded)
        private void SqlBlob_Load(object sender, System.EventArgs e)
        {
            _dataSet = new DataSet();

            // Fetch data
            SqlDataAdapter da;
            da = new SqlDataAdapter("SELECT * FROM Employees",
                "SERVER=xeon;DATABASE=northwind;UID=sa;PASSWORD=manager");
            da.Fill(_dataSet,"Employees");

            // Set bindings for textbox controls
            BindControls();

            // Refresh the UI
            EnableControls(true);
            DisplayRecordPosition();
        }

        // Bind data bound controls to binding expression
        private void BindControls()
        {
            firstName.DataBindings.Add(new Binding("Text",_dataSet,"Employees.FirstName"));
            lastName.DataBindings.Add(new Binding("Text",_dataSet,"Employees.LastName"));
            position.DataBindings.Add(new Binding("Text",_dataSet,"Employees.Title"));

            // The Format event lets you easily format columns
            // before displaying it in the control
            Binding bdHireDate = new Binding("Text",_dataSet,"Employees.HireDate");
            bdHireDate.Format += new ConvertEventHandler(this.HireDateFormat);
            hired.DataBindings.Add(bdHireDate);

            Binding bdPhoto = new Binding("Image",_dataSet,"Employees.Photo");
            bdPhoto.Format += new ConvertEventHandler(this.PictureFormat);
            Photo.DataBindings.Add(bdPhoto);

            // Store the instance of the BindingContext for the
            // control bound to the Employees table in the given DataSet.
            // We use these for the Row Position Management.
            _bmbEmployees = this.BindingContext[_dataSet,"Employees"];
        }

        // Update the status bar to reflect the current record position
        private void EnableControls(bool activate)
        {
            firstName.Enabled = activate;
            lastName.Enabled = activate;
            position.Enabled = activate;
            hired.Enabled = activate;

            ButtonPrev.Enabled = activate;
            ButtonNext.Enabled = activate;
        }

        // Update the status bar to reflect the current record position
        private void DisplayRecordPosition()
        {
            AppStatusBar.Text = "Currently on Record # " +
                (_bmbEmployees.Position + 1).ToString();
        }

        // Move to the previous record
        private void ButtonPrev_Click(object sender, System.EventArgs e)
        {
            _bmbEmployees.Position -= 1;
            DisplayRecordPosition();
        }

        // Move to the next record
        private void ButtonNext_Click(object sender, System.EventArgs e)
        {
            _bmbEmployees.Position += 1;
            DisplayRecordPosition();
        }

        // Convert the image bits into a Bitmap object that
        // can be assigned to a PictureBox control
        private void PictureFormat(object sender, ConvertEventArgs e)
        {
            // e.Value is the original value
            Byte[] img = (Byte[])e.Value;

            // Normally, the database BLOB field contains only the image itself.
            // Unfortunately, this is not the case with Northwind, in which images
            // are prefixed with a 78-byte header. So, to create a valid object,
            // you must skip those bytes.
            MemoryStream ms = new MemoryStream();
            int offset = 78;
            ms.Write(img, offset, img.Length - offset);
            Bitmap bmp = new Bitmap(ms);
            ms.Close();

            // Writes the new value back
            e.Value = bmp;
        }

        // Format the hire date into a more convenient output format
        private void HireDateFormat(object sender, ConvertEventArgs e)
        {
            DateTime dt = (DateTime) e.Value;
            e.Value = dt.ToString("dd.MM.yyyy");
        }

        [STAThread]
        static void Main()
        {
            Application.Run(new SqlBlob());
        }
    }
}

Reading BLOBs from SQL Server and display it in a Windows Form PictureBox

Reading BLOBs from SQL Server and
display it in a Windows Form PictureBox