Introduction


This article will walk through steps to display an image (working with Image class) read from a SQL-Server table into a suitable image format or steam for various types of desktop or ASP.NET projects. While the main focus is on reading an image suggestions focus away from working with DataTable containers and instead using a concrete class and list of a concrete class. The reason for working with classes rather than DataTable containers is that DataTable containers carry along extra baggage which is not needed to display an image which will make sense when moving away from desktop projects to web-centric projects. 

Images are generally displayed in an image control, in desktop applications this may be a PictureBox or directly in a grid such as a DataGridView. In many cases displaying images in a DataGridView means either reducing image size to fit into the DataGridView rows or increasing the height of the DataGridView rows to permit viewing images which means other style changes are required for the DataGridView control.

A better method where space is available on a form is to use a PictureBox which uses bindings so that when a row is changed in a DataGridView or perhaps a BindingNavigator the image is updated to match the current record. 

Note that there is always an alternative to storing images directly in SQL-Server tables which is in a FILESTREAM. Using the Image field type verses storing files in the FILESTREAM or file system will depend on your business requirements and physical resources.



Walkthrough

In the following code block, a connection is made to a SQL-Server database followed by a command object setup with a SQL SELECT statement to read an Image field using a WHERE condition on the table’s primary key. This is followed by executing the SQL statement via ExecuteReader, check to ensure there is a result coming back from the ExecuteReader.  If ExecuteReader did not come back results an empty image is used with “Error” text which when back in the calling method a red image will be displayed. If there are results from ExecuteReader the field representing an image is read into Byte array followed by closing the Data reader as we are done with the reader.

A check is made to ensure the Byte array has data and if so read the byte array into a memory stream which in turn utilizes Image.FromStream to create an image suitable to return to the calling method.

The Return statement returns a valid image if all went as expected or an empty image with "Error" text.

''' <summary>
''' Read Picture from table by primary key
''' </summary>
''' <param name="pIdentifier"></param>
''' <returns></returns>
Public Function GetImage(pIdentifier As Integer) As Image
 
    Dim fruitImage As Image = _pinvalidImage
 
    Using cn As New SqlConnection(ConnectionString)
        Using cmd As New SqlCommand("SELECT Picture FROM dbo.Fruits WHERE id = @Id", cn)
 
            cmd.Parameters.AddWithValue("@id", pIdentifier)
 
            Dim reader As SqlDataReader
 
            Try
                cn.Open()
 
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 
                If reader.Read Then
 
                    Dim imageData = CType(reader(0), Byte())
 
                    reader.Close()
 
                    If imageData IsNot Nothing Then
                        Using ms As New MemoryStream(imageData, 0, imageData.Length)
                            ms.Write(imageData, 0, imageData.Length)
                            fruitImage = Image.FromStream(ms, True)
                        End Using
                    End If
 
                End If
            Catch ex As Exception
                mHasException = True
                mLastException = ex
            End Try
 
        End Using
 
    End Using
 
    Return fruitImage
 
End Function

In a desktop application, in a button click event

Private Sub cmdGetSingleImage_Click(sender As Object, e As EventArgs) Handles cmdGetSingleImage.Click
    Dim primaryKey = CType(ListBoxForButtonClickExample.SelectedItem, Fruit).Id
    PictureBoxForSingleClick.Image = _ops.GetImage(primaryKey)
End Sub

For a web application, the MemoryStream is needed, not the image as shown below.

Public Function GetImageStream(pIdentifier As Integer) As Stream
 
    Dim stream As Stream = New MemoryStream()
 
    Using cn As New SqlConnection(ConnectionString)
        Using cmd As New SqlCommand("SELECT Picture FROM dbo.Fruits WHERE id = @Id", cn)
 
            cmd.Parameters.AddWithValue("@id", pIdentifier)
 
            Dim reader As SqlDataReader
 
            Try
                cn.Open()
 
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 
                If reader.Read Then
 
                    Dim imageData = CType(reader(0), Byte())
 
                    reader.Close()
 
                    If imageData IsNot Nothing Then
                        Return New MemoryStream(imageData, 0, imageData.Length)
                    Else
                        Throw New ImageLoadException($"{pIdentifier} record has an issue with the image")
                    End If
 
                End If
            Catch ex As Exception
                mHasException = True
                mLastException = ex
            End Try
 
        End Using
 
    End Using
 
    Return stream
 
End Function

For reading all images

''' <summary>
''' Read all records with all fields
''' </summary>
''' <returns></returns>
Public Function GetFruitsWithImagesFromList() As List(Of Fruit)
    Dim fruitImage As Image = Nothing
    Dim fruitList As New List(Of Fruit)
 
    Using cn As New SqlConnection(ConnectionString)
        Using cmd As New SqlCommand("SELECT id ,[Description], Picture FROM dbo.Fruits", cn)
            Try
                cn.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader()
 
                While reader.Read()
 
                    Dim imageData = CType(reader(2), Byte())
 
                    If imageData IsNot Nothing Then
 
                        Using ms As New MemoryStream(imageData, 0, imageData.Length)
                            ms.Write(imageData, 0, imageData.Length)
                            fruitImage = Image.FromStream(ms, True)
                        End Using
 
                    Else
                        fruitImage = InvalidImage
                    End If
 
                    fruitList.Add(New Fruit() With
                                {
                                    .Id = reader.GetInt32(0),
                                    .Description = reader.GetString(1),
                                    .Picture = fruitImage
                                })
                End While
 
            Catch ex As Exception
                mHasException = True
                mLastException = ex
            End Try
        End Using
    End Using
 
    Return fruitList
 
End Function

If only a subset is needed an overload would be needed which passes in several keys.
Public Function GetFruitsWithImagesFromList(pKeys As String) As List(Of Fruit)
    Dim fruitImage As Image = Nothing
    Dim fruitList As New List(Of Fruit)
 
    Using cn As New SqlConnection(ConnectionString)
        Using cmd As New SqlCommand($"SELECT id ,[Description], Picture FROM dbo.Fruits WHERE id IN ({pKeys})", cn)
            Try
                cn.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader()
 
                While reader.Read()
 
                    Dim imageData = CType(reader(2), Byte())
 
                    If imageData IsNot Nothing Then
 
                        Using ms As New MemoryStream(imageData, 0, imageData.Length)
                            ms.Write(imageData, 0, imageData.Length)
                            fruitImage = Image.FromStream(ms, True)
                        End Using
 
                    Else
                        fruitImage = InvalidImage
                    End If
 
                    fruitList.Add(New Fruit() With
                                        {
                                        .Id = reader.GetInt32(0),
                                        .Description = reader.GetString(1),
                                        .Picture = fruitImage
                                        })
                End While
 
            Catch ex As Exception
                mHasException = True
                mLastException = ex
            End Try
        End Using
    End Using
 
    Return fruitList
 
End Function

Called as follows (where the keys are hard coded yet can be dynamic also). 

_ops.GetFruitsWithImagesFromList("1,3")

For working with a DataTable the data is loaded using the Load method of the DataTable as shown below.

Public Function DataTable() As DataTable
 
    Dim dt As New DataTable
 
    Dim selectStatement = "SELECT id ,[Description], Picture FROM dbo.Fruits"
 
    Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = selectStatement}
            Try
                cn.Open()
                dt.Load(cmd.ExecuteReader)
                dt.Columns("Id").ColumnMapping = MappingType.Hidden
                dt.Columns("Picture").ColumnMapping = MappingType.Hidden
                dt.Columns("Picture").ReadOnly = True
            Catch ex As Exception
                mHasException = True
                mLastException = ex
            End Try
        End Using
    End Using
 
    Return dt
 
End Function

In the calling form to display in a DataGridView, in this case, the DataTable is assigned to the DataSource of a BindingSource component. The DataGridView.DataSource is set to the BindingSource. 

_bs.DataSource = _ops.DataTable()
dgvFruitPictures.DataSource = _bs

To handle display an image in a PictureBox a Binding is needed and subscribe to the Format event.
Dim imageBinding As New Binding("Image", _bs, "Picture")
AddHandler imageBinding.Format, AddressOf BindImage
PictureBoxForDataGridView.DataBindings.Add(imageBinding)

Format event which asserts the type is an image followed by using the exact same logic as in the prior methods, write the bytes to a MemoryStream followed by loading the image.

Private Sub BindImage(sender As Object, e As ConvertEventArgs)
 
    If e.DesiredType Is GetType(Image) Then
        Using ms As New MemoryStream(CType(e.Value, Byte()))
            Dim logo = Image.FromStream(ms)
            e.Value = logo
        End Using
    End If
 
End Sub
''' <summa

Before running the project


Before running the project, open the .sql file under data scripts folder, run the script to create the database, table, and data. Next, change the SQL-Server name from KARENS-PC to your server name or .\SQLEXPRESS for the Express edition of SQL-Server.

Conclusion


This article along with source code below on GitHub provides a solid foundation for reading images from a SQL-Server database table into an Image suitable for image controls such as a PictureBox or to store these images in an ImageList or to work within a web application. More focus has been placed on lightweight containers rather than heavyweight containers so not to slow down reading and presenting images.

Resources

INSERT Image into SQL-Server using stored procedures  
SQL-Server insert binary files  
Compare options for storing blobs in SQL-Server  

Source code

https://github.com/karenpayneoregon/ReadSqlServerImagesInVisualBasic

See also

Storing Images and Photos in SQL-Server