VB.NET Reading images from SQL-Server
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