Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

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

Rate me:
Please Sign up or sign in to vote.
4.76/5 (39 votes)
10 Aug 2012CPOL1 min read 369.1K   29K   39   43
How to save and retrieve an image from a SQL Server database in VB.NET.
  • Download source - 4.62 MB
  • Sample Image

    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:

    VB
    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:

    Field Name

    Field Type

    name

    nvarchar(50)

    photo

    Image

    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.

    VB
    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.

    Sample Image - maximum width is 600 pixels

    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.

    VB
    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

    License

    This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


    Written By
    Software Developer
    India India
    This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

    Comments and Discussions

     
    QuestionQuestion Pin
    Member 1372851215-Mar-18 6:07
    Member 1372851215-Mar-18 6:07 
    QuestionGreat Job Pin
    Nur Raymond31-Aug-16 10:08
    Nur Raymond31-Aug-16 10:08 
    QuestionSave Pin
    Member 1242858731-Mar-16 4:41
    Member 1242858731-Mar-16 4:41 
    Questioni can not down load Pin
    Member 1172950931-May-15 22:17
    Member 1172950931-May-15 22:17 
    Questionerror while saving image Pin
    Member 1164433926-Apr-15 21:35
    Member 1164433926-Apr-15 21:35 
    GeneralMy vote of 1 Pin
    Muhammad Saqlain14-Nov-14 23:06
    Muhammad Saqlain14-Nov-14 23:06 
    GeneralMy vote of 5 Pin
    Umesh Bhosale19-Aug-14 2:02
    Umesh Bhosale19-Aug-14 2:02 
    Questionerror Pin
    Member 1088304413-Jun-14 6:48
    Member 1088304413-Jun-14 6:48 
    AnswerRe: error Pin
    Abdisamad Khalif10-Sep-15 1:47
    professionalAbdisamad Khalif10-Sep-15 1:47 
    Questionerror on re-saving image Pin
    khei-chan00712-Jun-14 17:52
    khei-chan00712-Jun-14 17:52 
    QuestionHow to download image on button click event.? Pin
    Amiet_Mhaske13-Apr-14 4:04
    Amiet_Mhaske13-Apr-14 4:04 
    On the button click event of "btnSearch_Download", I want to search image by name and download the image saved into database to computer hard drive by giving option to user with "save file" Dialogue box, where user can choose where he want to save a image file.

    Basic code i have used is
    VB
    Private Sub btnSearch_Download_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch_Download.Click
            cmd = New SqlCommand("select photo from Info where name='" & TextBox2.Text & "'", con)
            Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
            'Tell me What to do here?
            End If
        End Sub

    QuestionPicture Inserting to Database Pin
    C.Kumarasinghe6-Apr-14 5:57
    professionalC.Kumarasinghe6-Apr-14 5:57 
    QuestionSave Image & Retrive Pin
    Member 1043034927-Nov-13 8:43
    Member 1043034927-Nov-13 8:43 
    AnswerRe: Save Image & Retrive Pin
    Patil Kishor27-Nov-13 14:27
    Patil Kishor27-Nov-13 14:27 
    QuestionExcellent Pin
    karenpayne31-Oct-13 2:11
    karenpayne31-Oct-13 2:11 
    Questionout of memory Pin
    Basheer Ahammed5-Aug-13 0:07
    Basheer Ahammed5-Aug-13 0:07 
    AnswerRe: out of memory Pin
    AbdifatahMohiadin8-Mar-15 23:44
    AbdifatahMohiadin8-Mar-15 23:44 
    AnswerRe: out of memory Pin
    Abdisamad Khalif10-Sep-15 1:38
    professionalAbdisamad Khalif10-Sep-15 1:38 
    QuestionRetrieve images from Sql Server 2008 to VB.NEt Form Pin
    Subha Ranjan Saha28-Jun-13 5:33
    Subha Ranjan Saha28-Jun-13 5:33 
    AnswerRe: Retrieve images from Sql Server 2008 to VB.NEt Form Pin
    Patil Kishor5-Jul-13 4:50
    Patil Kishor5-Jul-13 4:50 
    Questionerror when update Pin
    indra ruslan10-Jun-13 20:34
    indra ruslan10-Jun-13 20:34 
    Questiona lote of images Pin
    mohammed sezer1-Jun-13 23:55
    mohammed sezer1-Jun-13 23:55 
    AnswerRe: a lote of images Pin
    Abdisamad Khalif10-Sep-15 1:41
    professionalAbdisamad Khalif10-Sep-15 1:41 
    GeneralMy vote of 5 Pin
    NeenaM4-Apr-13 12:27
    NeenaM4-Apr-13 12:27 
    GeneralImage update Pin
    Wilson Fernandes199112-Feb-13 4:54
    Wilson Fernandes199112-Feb-13 4:54 

    General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

    Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.