How to Save and Retrieve Image from SQL Server Database on VB.Net

In this tutorial, I will discuss how to save and retrieve image from SQL Server database on VB.Net. There are 2 methods involved. First, is saving the image in a folder and storing its path in the database as a string type. Second, is storing the image in the database as an image type. 

I had been using the first method before, but the problem was, when a user accidentally moved or removed the image, the application will return an error, because it cannot find the image path stored in the database. Though, you can handle this error by showing a message like - "Sorry. The image path you are trying to open was not found.". But letting the visitors read this, can be sometimes embarrassing.

I did not say, that I would not reccommend the first method. It's up to you on what method of saving and retrieving images you will use. The important thing is, you should apply a wise code to handle errors.

So, to start, I will begin with the first method. Please follow the step by step instructions to arrive with the correct output.

Step 1. Create an SQL database on Microsoft SQL Server  2005/ or 2008. Name it db_image then add a table named tbl_image. Inside  the table tbl_image, add these fields with corresponding data types

FIELDS	DATA TYPE
id	int
name	varchar(50)
img_path	varchar(MAX)

Step 2. Create a new Visual Basic Windows project on Visual Studio 2008/ 2010/ or 2012 and name it image_app. Design your form by grabbing these components in your toolbox and please use the same name I assigned.

COMPONENT	NAME	TEXT	BACKGROUND IMAGE LAYOUT
Button	btn_browse	BROWSE
Button	btn_save	SAVE
Button	btn_search	SEARCH
Label	lbl_name	Image Name:
Textbox	txt_imgname
Picturebox	img_box		Stretch

You can see the image example below:

Step 4. Insert this code in your form. 

Note: Change the class name Form1 if you have a different form name.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
    'variables used
    Private placeimage As Image = Nothing
    Private orgimage As String
    Private copyimage As String
    Private newlocation As String
    Private old_newlocation As String
    Private imgFound As Image
    Private FoundTitle As String
    Private cmd As New SqlCommand
    Private cnn As New SqlConnection
    Private reader As SqlDataReader
    'database data source
    Public sqldata As String = "Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=db_image;Integrated Security=True"
    Sub openConnection() 'establishing SQL connection
        cnn = New SqlConnection
        cnn.ConnectionString = sqldata
        cnn.Open()
    End Sub
    Private Sub btn_browse_Click(sender As Object, e As EventArgs) Handles btn_browse.Click
        Try
            Me.Cursor = Cursors.WaitCursor
            orgimage = Nothing
            copyimage = Nothing
            newlocation = Nothing
            Dim open As OpenFileDialog
            open = New OpenFileDialog
            open.FileName = ""
            open.Filter = "Image Formats(*.jpg;*.jpeg;*.bmp;*.gif;*.png;*.tif)|*.jpg;*.jpeg;*.bmp;*.gif;*.png;*.tif|JPEG Format(*.jpg;*.jpeg)|*.jpg;*.jpeg|BITMAP Format(*.bmp)|*.bmp|GIF Format(*.gif)|*.gif|PNG Format(*.png)|*.png"
            If open.ShowDialog = Windows.Forms.DialogResult.OK Then
                orgimage = open.FileName.ToUpper
                img_box.BackgroundImageLayout = ImageLayout.Stretch
                img_box.BackgroundImage = Image.FromFile(orgimage)
                copyimage = open.SafeFileName.ToString
                newlocation = "C:\Images\" & copyimage 'Image destination folder
                txt_imgname.Text = copyimage 'Get as image name
            End If
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub
    Private Sub btn_save_Click(sender As Object, e As EventArgs) Handles btn_save.Click
        Try
                'search image name if existing
                openConnection()
            cmd = New SqlCommand("Select * from tbl_image where name='" & txt_imgname.Text & "'", cnn)
                reader = cmd.ExecuteReader()
                If reader.Read = True Then
                    MessageBox.Show("Photo name: " & txt_imgname.Text.ToUpper & " already exist.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    txt_imgname.Focus()
                Else
                    'copy first image
                    My.Computer.FileSystem.CopyFile(sourceFileName:=orgimage, destinationFileName:=newlocation)
                    'save record in database
                    openConnection()
                cmd = New SqlCommand("Insert into tbl_image values(@name,@img_path)", cnn)
                    cmd.Parameters.Add(New SqlClient.SqlParameter("name", txt_imgname.Text))
                    cmd.Parameters.Add(New SqlClient.SqlParameter("img_path", newlocation.ToString))
                    cmd.ExecuteNonQuery()
                    MessageBox.Show("Image saved.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    newlocation = Nothing
                txt_imgname.Text = Nothing
                img_box.BackgroundImage = Nothing
                    Me.Cursor = Cursors.Default
                End If
                Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub
    Private Sub btn_search_Click(sender As Object, e As EventArgs) Handles btn_search.Click
        Try
            FoundTitle = Nothing
            imgFound = Nothing
            'search image name if existing
            openConnection()
            cmd = New SqlCommand("Select * from tbl_image where name='" & txt_imgname.Text & "'", cnn)
            reader = cmd.ExecuteReader()
            If reader.Read = False Then
                MessageBox.Show("Photo name: " & txt_imgname.Text.ToUpper & " does not exist.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
                txt_imgname.Focus()
            Else
                txt_imgname.Text = reader(0).ToString
                FoundTitle = reader(1).ToString()
                imgFound = Image.FromFile(FoundTitle)
                img_box.BackgroundImage = imgFound
                Me.Cursor = Cursors.Default
            End If
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub

End Class

Step 5. Change the code in red color above, if you want to change the directory where the images are stored.

Finally, hit F5 to run the application.

Ok. If you're done with the first method, try this next method too. 
You may use the same database but please change the field img_path / varchar(MAX) to img/ image. 

Step 1. Create an SQL database on Microsoft SQL Server  2005/ or 2008. Name it db_image then add a table named tbl_image. Inside  the table tbl_image, add these fields with corresponding data types

FIELDS	DATA TYPE
id	int
name	varchar(50)
img	image

Step 2. Create a new Visual Basic Windows project on Visual Studio 2008/ 2010/ or 2012 and name it image_app. Design your form by grabbing these components in your toolbox and please use the same name I assigned.

COMPONENT	NAME	TEXT	BACKGROUND IMAGE LAYOUT
Button	btn_browse	BROWSE
Button	btn_save	SAVE
Button	btn_search	SEARCH
Label	lbl_name	Image Name:
Textbox	txt_imgname
Picturebox	img_box		Stretch

You can see the image example below:

Step 4. Insert this code in your form. 

Note: Change the class name Form1 if you have a different form name.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Public Class Form1
    'variables used
    Private cmd As New SqlCommand 'SQl command
    Private cnn As New SqlConnection
    Private reader As SqlDataReader
    'database data source
    Public sqldata As String = "Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=db_image;Integrated Security=True"
    'use  get application running path
    Dim path As String = (Microsoft.VisualBasic.Left(Application.StartupPath, Len(Application.StartupPath) - 9))
    Sub openConnection()
        cnn = New SqlConnection
        cnn.ConnectionString = sqldata
        cnn.Open()
    End Sub
    Private Sub btn_browse_Click(sender As Object, e As EventArgs) Handles btn_browse.Click
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim open As OpenFileDialog
            open = New OpenFileDialog
            open.FileName = ""
            open.Filter = "Image Formats(*.jpg;*.jpeg;*.bmp;*.gif;*.png;*.tif)|*.jpg;*.jpeg;*.bmp;*.gif;*.png;*.tif|JPEG Format(*.jpg;*.jpeg)|*.jpg;*.jpeg|BITMAP Format(*.bmp)|*.bmp|GIF Format(*.gif)|*.gif|PNG Format(*.png)|*.png"
            If open.ShowDialog = Windows.Forms.DialogResult.OK Then
                img_box.BackgroundImageLayout = ImageLayout.Stretch
                img_box.BackgroundImage = Image.FromFile(open.FileName.ToUpper)
                txt_imgname.Text = open.SafeFileName.ToString 'Get as image name
            End If
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub
    Private Sub btn_save_Click(sender As Object, e As EventArgs) Handles btn_save.Click
        Try
            Dim ms As New MemoryStream()

            'search image name if existing
            openConnection()
            cmd = New SqlCommand("Select * from tbl_image where name='" & txt_imgname.Text & "'", cnn)
            reader = cmd.ExecuteReader()
            If reader.Read = True Then
                MessageBox.Show("Photo name: " & txt_imgname.Text.ToUpper & " already exist.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
                txt_imgname.Focus()
            Else
                'save record in database
                openConnection()
                cmd = New SqlCommand("Insert into tbl_image values(@name,@img)", cnn)
                cmd.Parameters.Add(New SqlClient.SqlParameter("name", txt_imgname.Text))
                img_box.BackgroundImage.Save(MS, img_box.BackgroundImage.RawFormat)
                Dim data As Byte() = MS.GetBuffer()
                Dim img As New SqlParameter("@img", SqlDbType.Image)
                img.Value = data
                cmd.Parameters.Add(img)
                cmd.ExecuteNonQuery()
                MessageBox.Show("Image saved.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
                txt_imgname.Text = Nothing
                img_box.BackgroundImage = Nothing
                Me.Cursor = Cursors.Default
            End If
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub
    Private Sub btn_search_Click(sender As Object, e As EventArgs) Handles btn_search.Click
        Try
            'search image name if existing
            openConnection()
            cmd = New SqlCommand("Select * from tbl_image where name='" & txt_imgname.Text & "'", cnn)
            reader = cmd.ExecuteReader()
            If reader.Read = False Then
                MessageBox.Show("Photo name: " & txt_imgname.Text.ToUpper & " does not exist.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
                txt_imgname.Focus()
            Else
                txt_imgname.Text = reader(0).ToString
                'store image value from the database in byte
                Dim imageData As Byte() = reader(1)
                If Not imageData Is Nothing Then
                    Using ms As New MemoryStream(imageData, 0, imageData.Length)
                        ms.Write(imageData, 0, imageData.Length)
                        img_box.BackgroundImage = Image.FromStream(ms, True)
                    End Using
                End If
                Me.Cursor = Cursors.Default
                End If
                Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub
End Class

Finally, hit F5 to run a test.

Now tell me which method is better. I hope you learned something useful in this post on how to save and retrieve image from SQL Server database.