Save and Retrieve Image from a SQL Server Database using VB.NET

Introduction

This article is about storing and retrieving images from a SQL Server database using VB.NET. When we create an application where we need to save images then we save images in a folder and store the path of the image in the database as string type.

• If you save an image to a folder, you might accidentally delete the image from that folder. If this happens, you will get an error when retrieving the image. It is very difficult to handle these accidents.
• So if you save an image into a database, you can enforce security by using the security settings of the database.

The application

Create a Windows application in VB.NET 2005 and design it as show in the above image. Then import namespaces as follows:

Imports System.Data.SqlClient
Imports System.IO

Create the database

Create a SQL Server database as follows. In Solution Explorer, click on project name and right click on it, then Add -> New item -> SQL,dDatabase name "Database1.mdf", then OK. Click on database1 and create a table in it named information with fields as follows:

Using the code

Actually the IMAGE field is just holding a reference to the page containing the binary data so we have to convert our image into bytes.

Imports System.Data.SqlClient
Imports System.IO

    Public Class Form1
    'path variable use for Get application running path
    Dim path As String = (Microsoft.VisualBasic.Left(Application.StartupPath, Len(Application.StartupPath) - 9))
    Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & _
            path & "Database1.mdf;Integrated Security=True;User Instance=True")
    Dim cmd As SqlCommand
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
       If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            PictureBox1.BackgroundImage = Image.FromFile(OpenFileDialog1.FileName)
            Label1.Visible = True
            TextBox1.Visible = True
            Label1.Text = "Name"
            TextBox1.Clear()
        End If
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the
        ' 'Database1DataSet.Information' table. You can move, or remove it, as needed.
        Me.InformationTableAdapter.Fill(Me.Database1DataSet.Information)
        con.Open()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button2.Click
        If TextBox1.Text = "" Then
            MsgBox("Fill the Name Field")
        Else
            Dim sql As String = "INSERT INTO Information VALUES(@name,@photo)"
            Dim cmd As New SqlCommand(sql, con)
            cmd.Parameters.AddWithValue("@name", TextBox1.Text)
            Dim ms As New MemoryStream()
            PictureBox1.BackgroundImage.Save(ms, PictureBox1.BackgroundImage.RawFormat)
            Dim data As Byte() = ms.GetBuffer()
            Dim p As New SqlParameter("@photo", SqlDbType.Image)
            p.Value = data
            cmd.Parameters.Add(p)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Name & Image has been saved", "Save", MessageBoxButtons.OK)
            Label1.Visible = False
            TextBox1.Visible = False
        End If
    End Sub
    Private Sub Button3_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles Button3.Click
        GroupBox2.BringToFront()
        GroupBox2.Visible = True
        Label1.Visible = False
        TextBox1.Visible = False
    End Sub

    Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, ByVal e As _
                System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
        cmd = New SqlCommand("select photo from Information where name='" & _
                  DataGridView1.CurrentRow.Cells(0).Value() & "'", con)
        Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
        If Not imageData Is Nothing Then
            Using ms As New MemoryStream(imageData, 0, imageData.Length)
                ms.Write(imageData, 0, imageData.Length)
                PictureBox1.BackgroundImage = Image.FromStream(ms, True)
            End Using
        End If
        GroupBox2.SendToBack()
        GroupBox2.Visible = False
        Label1.Visible = True
        Label1.Text = DataGridView1.CurrentRow.Cells(0).Value()
    End Sub
End Class

Retrieving images from the database is the exact reverse process of saving images to the database. The following code is used for retrieval.

The application uploads images from the database and displays it in a DataGridView. When you click on a datagridview cell then an image is displayed in the picture box.

Private Sub DataGridView1_CellMouseClick(ByVal sender As Object, _
               ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) _
               Handles DataGridView1.CellMouseClick
    cmd = New SqlCommand("select photo from Information where name='" & _
              DataGridView1.CurrentRow.Cells(0).Value() & "'", con)
    Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
    If Not imageData Is Nothing Then
        Using ms As New MemoryStream(imageData, 0, imageData.Length)
            ms.Write(imageData, 0, imageData.Length)
            PictureBox1.BackgroundImage = Image.FromStream(ms, True)
        End Using
    End If
    GroupBox2.SendToBack()
    GroupBox2.Visible = False
    Label1.Visible = True
    Label1.Text = DataGridView1.CurrentRow.Cells(0).Value()
End Sub