Download source - 4.62 MB ![Sample Image](/KB/database/437937/store1.jpg)
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
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
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](data:image/gif;base64,R0lGODlhAQABAAAAACH5BAEKAAEALAAAAAABAAEAAAICTAEAOw==)
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
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.