Click here to Skip to main content
Click here to Skip to main content

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

, 10 Aug 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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:

    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.

    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.

    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)

    Share

    About the Author

    Patil Kishor
    Software Developer
    India India
    No Biography provided

    Comments and Discussions

     
    GeneralMy vote of 5 PinmemberUmesh Bhosale19-Aug-14 2:02 
    Questionerror PinmemberMember 1088304413-Jun-14 6:48 
    Questionerror on re-saving image Pinmemberkhei-chan00712-Jun-14 17:52 
    QuestionHow to download image on button click event.? PinmemberAmiet_Mhaske13-Apr-14 4:04 
    QuestionPicture Inserting to Database PinmemberC.Kumarasinghe6-Apr-14 5:57 
    QuestionSave Image & Retrive PinmemberMember 1043034927-Nov-13 8:43 
    AnswerRe: Save Image & Retrive PinmemberPatil Kishor27-Nov-13 14:27 
    QuestionExcellent Pinmemberkevininstructor31-Oct-13 2:11 
    Questionout of memory PinmemberBasheer Ahammed5-Aug-13 0:07 
    QuestionRetrieve images from Sql Server 2008 to VB.NEt Form PinmemberSubha Ranjan Saha28-Jun-13 5:33 
    AnswerRe: Retrieve images from Sql Server 2008 to VB.NEt Form PinmemberPatil Kishor5-Jul-13 4:50 
    Questionerror when update Pinmemberindra ruslan10-Jun-13 20:34 
    Questiona lote of images Pinmembermohammed sezer1-Jun-13 23:55 
    GeneralMy vote of 5 PinmemberNeenaM4-Apr-13 12:27 
    GeneralImage update PinmemberWilson Fernandes199112-Feb-13 4:54 
    GeneralRe: Image update PinmemberPatil Kishor18-Feb-13 19:31 
    QuestionHey guys!Anyone help me please Pinmemberrichmasua30-Sep-12 1:41 
    Questionbroo PinmemberGun Gun Febrianza10-Sep-12 20:53 
    GeneralMy vote of 5 PinmemberGun Gun Febrianza10-Sep-12 20:53 
    GeneralMy vote of 3 Pinmemberingenvzla26-Aug-12 14:59 
    GeneralMy vote of 5 Pinmemberjihad8625-Aug-12 20:51 
    Generalwhen ret a img ,got out of memory err message, pls send correct code (vsraba@gmail.com) Pinmemberbaarsv18-Jan-13 1:37 
    GeneralMy vote of 3 PinmemberRene Pilon16-Aug-12 6:12 
    GeneralMy vote of 4 PinmemberAgyemang14-Aug-12 23:29 
    QuestionGood work PinmemberAgyemang14-Aug-12 23:25 
    GeneralMy vote of 5 PinmemberPolinia11-Aug-12 15:23 
    To great exemplo....
    GeneralMy vote of 2 PinmemberJαved10-Aug-12 21:30 
    SuggestionNo Download Files PinmemberTarun Y Mangukiya9-Aug-12 21:50 
    QuestionSource code Pinmembercmarcotte9-Aug-12 12:23 
    QuestionImage is old, obsolete type PinmemberJakub Müller9-Aug-12 8:12 
    AnswerRe: Image is old, obsolete type Pinmemberstooboo9-Aug-12 10:41 
    GeneralRe: Image is old, obsolete type PinmemberJakub Müller9-Aug-12 23:45 
    GeneralMy vote of 4 PinmemberChristian Amado9-Aug-12 8:11 

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

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

    | Advertise | Privacy | Mobile
    Web01 | 2.8.141029.1 | Last Updated 10 Aug 2012
    Article Copyright 2012 by Patil Kishor
    Everything else Copyright © CodeProject, 1999-2014
    Terms of Service
    Layout: fixed | fluid