ARTICLE

Save and Retrieve Image From SQL in VB.NET

Posted by Kishor Patil Articles | Visual Basic .NET October 14, 2012
This article is about storing and retrieving images from SQL Database using a VB.NET.
Reader Level:
Download Files:
 

Introduction
 
When we create any application that we need to save images then we save the image in a folder and store the path of the image to the database as a string type. If you save an image to a folder, you might accidentally delete an image from that folder. If this happens then you will a get an error the next time the image is retrieved. It is very difficult to prevent this accident.

storing-and-retrieving-images-from-SQL.gif
 
So, if you save an image into a database then 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 shown in the above image. Then import namespaces as follows:
 
 
Imports System.Data.SqlClient
 
Imports System.IO
 

Create database
 

Create a SQL database as follows in Solution Explorer, click on project name and right-click on it then Add -> New item -> SQL Database; name it "Database1.mdf " then click ok. Click on database1 and create a table in it named "information" with the fields as in the following diagram:

images-in-SQL.gif
 
Using the code
 

Actually the IMAGE field is just holding the 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
 

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

images-list-in-SQL.gif
 
The application uploads images from a database and displays them in a DataGridView. When you click on the 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

COMMENT USING

Trending up