Knowledge Base

HOW TO: Copy a Picture from a Database Directly to a PictureBox Control with Visual Basic .NET

Article ID: 317670

Article Last Modified on 9/5/2003

APPLIES TO

Microsoft .NET Framework 1.1 Service Pack 1
Microsoft ADO.NET 1.1
Microsoft Visual Basic .NET 2002 Standard Edition
Microsoft Visual Basic .NET 2003 Standard Edition

This article was previously published under Q317670

For a Microsoft Visual C# .NET version of this article, see 317701.

For a Microsoft Visual Basic 6.0 version of this article, see 250577.

This article refers to the following Microsoft .NET Framework Class Library namespaces:

System.Data.SqlClient
System.IO
System.Drawing.Imaging

IN THIS TASK

SUMMARY

Requirements
Sample
Troubleshooting

REFERENCES

SUMMARY

This step-by-step article describes how to copy an image stored in a database directly into a PictureBox control on a Windows Form without needing to save the image to a file.

In Microsoft Visual Basic 6.0, the only way to display an image from a database in a PictureBox control, without the intermediate step of saving the binary large object {BLOB) data to a file, is to bind the PictureBox to a data source such as an ActiveX Data Objects (ADO) Data control or Recordset object. There is no way (without data binding) to programmatically load a BLOB into a control without saving the image to a file for use by the LoadPicture statement.

This article uses the MemoryStream object from the System.IO base class to copy the image data from the database directly into the PictureBox control.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you will need:

Microsoft Visual Studio .NET installed on a compatible Microsoft Windows operating system
An available instance of Microsoft SQL Server or an available Microsoft Access database for testing

This article assumes that you are familiar with the following topics:

Visual Basic .NET Windows Forms applications
Binary large object (BLOB) storage in databases
ADO.NET data access

back to the top

Sample

Create a SQL Server or Access table with the following structure:

CREATE TABLE BLOBTest
(
BLOBID INT IDENTITY NOT NULL,
BLOBData IMAGE NOT NULL
)

Start Visual Studio .NET, and then create a new Visual Basic Windows Forms application.
Drag a PictureBox control and two Button controls from the toolbox to the default Form1. Set the Text property of Button1 to File to Database, and then set the Text property of Button2 to Database to PictureBox.

Add the following Imports statements at the top of the form's code module:

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Drawing.Imaging

Add the following declaration for the database connection string below Public Class Form1, and then modify the connection string as necessary for your environment:

    Dim strCn As String = "Data Source=<server>;" & _
            "Initial Catalog=<database>;Integrated Security=SSPI"

Add the following code in the Click event procedure of Button1 (File to Database). Modify the file path to an available sample image file as necessary. This code reads the image file from disk (by using a FileStream object) into a Byte array, and then inserts the data into the database by using a parameterized Command object.

        Dim cn As New SqlConnection(strCn)
        Dim cmd As New SqlCommand("INSERT INTO BLOBTest (BLOBData) " & _
            "VALUES (@BLOBData)", cn)
        Dim strBLOBFilePath As String = _
            "C:\Documents and Settings\All Users\Documents" & _
            "\My Pictures\Sample Pictures\winter.jpg"
        Dim fsBLOBFile As New FileStream(strBLOBFilePath, _
            FileMode.Open, FileAccess.Read)
        Dim bytBLOBData(fsBLOBFile.Length() - 1) As Byte
        fsBLOBFile.Read(bytBLOBData, 0, bytBLOBData.Length)
        fsBLOBFile.Close()
        Dim prm As New SqlParameter("@BLOBData", SqlDbType.VarBinary, _
            bytBLOBData.Length, ParameterDirection.Input, False, _
            0, 0, Nothing, DataRowVersion.Current, bytBLOBData)
        cmd.Parameters.Add(prm)
        cn.Open()
        cmd.ExecuteNonQuery()
        cn.Close()

Add the following code in the Click event procedure of Button2 (Database to PictureBox). This code retrieves the rows from the BLOBTest table in the database into a DataSet, copies the most recently added image into a Byte array and then into a MemoryStream object, and then loads the MemoryStream into the Image property of the PictureBox control.

        Dim cn As New SqlConnection(strCn)
        Dim cmd As New SqlCommand("SELECT BLOBID, " & _
            "BLOBData FROM BLOBTest ORDER BY BLOBID", cn)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet()
        da.Fill(ds, "BLOBTest")
        Dim c As Integer = ds.Tables("BLOBTest").Rows.Count
        If c > 0 Then
            Dim bytBLOBData() As Byte = _
                ds.Tables("BLOBTest").Rows(c - 1)("BLOBData")
            Dim stmBLOBData As New MemoryStream(bytBLOBData)
            picBLOB.Image = Image.FromStream(stmBLOBData)
        End If

Run the project.
Click File to Database to load at least one sample image into the database.
Click Database to PictureBox to display the saved image in the PictureBox control.

If you want to be able to insert the image from the PictureBox control directly into the database, add a third Button control, and then add the following code in its Click event procedure. This code retrieves the image data from the PictureBox control into a MemoryStream object, copies the MemoryStream into a Byte array, and then saves the Byte array to the database by using a parameterized Command object.

        Dim cn As New SqlConnection(strCn)
        Dim cmd As New SqlCommand("INSERT INTO BLOBTest (BLOBData) " & _
            "VALUES (@BLOBData)", cn)
        Dim ms As MemoryStream = New MemoryStream()
        picBLOB.Image.Save(ms, ImageFormat.Jpeg)
        Dim bytBLOBData(ms.Length - 1) As Byte
        ms.Position = 0
        ms.Read(bytBLOBData, 0, ms.Length)
        Dim prm As New SqlParameter("@BLOBData", SqlDbType.VarBinary, _
            bytBLOBData.Length, ParameterDirection.Input, False, _
            0, 0, Nothing, DataRowVersion.Current, bytBLOBData)
        cmd.Parameters.Add(prm)
        cn.Open()
        cmd.ExecuteNonQuery()
        cn.Close()

Run the project. Click Database to PictureBox to display a previously saved image in the PictureBox control. Click the newly added button to save the image from the PictureBox into the database, and then click Database to PictureBox again to confirm that the image was saved correctly.

back to the top

Troubleshooting

This test will not work with the Photo column in the Employees table of the sample Northwind database distributed with Access and SQL Server. The bitmap images stored in the Photo column are wrapped with the header information created by the Visual Basic 6.0 OLE Container control.
If you need to use an Access database to test this code, you must create the column in the Access table as type OLE Object, and use the System.Data.OleDb namespace with the Microsoft Jet 4.0 Provider instead of the System.Data.SqlClient namespace.

back to the top

REFERENCES

For additional information about using BLOB data with Visual Basic .NET, click the article number below to view the article in the Microsoft Knowledge Base:

308042 HOW TO: Read and Write BLOB Data by Using ADO.NET with Visual Basic .NET

316887 HOW TO: Read and Write a File to and from a BLOB Column by Using ADO.NET and Visual Basic .NET

back to the top

Additional query words: BLOB

Keywords: kbhowtomaster kbio kbsqlclient kbsystemdata kbgdi kbdraw KB317670

Microsoft KB Archive/317670

Contents

HOW TO: Copy a Picture from a Database Directly to a PictureBox Control with Visual Basic .NET

IN THIS TASK

SUMMARY

Requirements

Sample

Troubleshooting

REFERENCES