Click here to Skip to main content
15,867,686 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

     
    GeneralMy vote of 4 Pin
    Agyemang14-Aug-12 23:29
    Agyemang14-Aug-12 23:29 
    QuestionGood work Pin
    Agyemang14-Aug-12 23:25
    Agyemang14-Aug-12 23:25 
    GeneralMy vote of 5 Pin
    Polinia11-Aug-12 15:23
    Polinia11-Aug-12 15:23 
    GeneralMy vote of 2 Pin
    Jαved10-Aug-12 21:30
    professionalJαved10-Aug-12 21:30 
    SuggestionNo Download Files Pin
    Tarun Mangukiya9-Aug-12 21:50
    Tarun Mangukiya9-Aug-12 21:50 
    QuestionSource code Pin
    cmarcotte9-Aug-12 12:23
    cmarcotte9-Aug-12 12:23 
    QuestionImage is old, obsolete type Pin
    Jakub Müller9-Aug-12 8:12
    Jakub Müller9-Aug-12 8:12 
    AnswerRe: Image is old, obsolete type Pin
    stooboo9-Aug-12 10:41
    stooboo9-Aug-12 10:41 
    nvarchar(max) seems a strange choice .. why convert the image to unicode text ???

    varbinary(max) would be a better choice surely ?
    GeneralRe: Image is old, obsolete type PinPopular
    Jakub Müller9-Aug-12 23:45
    Jakub Müller9-Aug-12 23:45 
    GeneralMy vote of 4 Pin
    Christian Amado9-Aug-12 8:11
    professionalChristian Amado9-Aug-12 8:11 

    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.