Hi all
Now a days it's common to interact with database and to store and retrieve images
in/from the database. Here I have used SQL Server database. This code snippet
will show how to store and retrieve images to/from SQL Server.
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
Dim cn As SqlClient.SqlConnection
Public Const strConn As String = "packet size=4096;user id=sa;data source=192.168.99.111;persist security info=False;initial
catalog=My_Database"
Dim haveData81 As Boolean = False : Dim haveData82 As Boolean = False
Dim Conn As SqlConnection = New SqlConnection
Dim comAdd As SqlCommand
Dim da As SqlDataAdapter
Dim ds As DataSet = New DataSet
'Browse Button
Private Sub btn_Browse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
btn_Browse.Click
'Display Picture File
OpenFileDialog1.InitialDirectory = "d:\pic"
OpenFileDialog1.DefaultExt = "gif"
'Set filter
OpenFileDialog1.Filter = "Bmp Files(*.bmp)|*.bmp|Gif Files(*.gif)|*.gif|Jpg Files(*.jpg)|*.jpg"
OpenFileDialog1.ShowDialog()
PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)
TextBox1.Text = "" + OpenFileDialog1.FileName.Substring(CInt(OpenFileDialog1.FileName.LastIndexOf("\"))
+ 1)
End Sub
'Add Button
Private Sub btn_Add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
btn_Add.Click
' To Insert Image
Dim st As New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)
Dim s As String = TextBox1.Text
Dim mbr As BinaryReader = New BinaryReader(st)
Dim buffer(st.Length) As Byte
mbr.Read(buffer, 0, CInt(st.Length))
st.Close()
InsertImage(buffer, s)
End Sub
'Function For Inserting in the Procdeure in the Database
Public Function InsertImage(ByRef buffer, ByVal str)
cn = New SqlClient.SqlConnection(SqlConnection1.ConnectionString)
cn.Open()
Dim cmd As New SqlClient.SqlCommand("sp_InsertPhoto", cn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = TextBox1.Text
cmd.Parameters.Add("@image", SqlDbType.Image).Value = buffer
cmd.ExecuteNonQuery()
MsgBox("Image inserted")
cn.Close()
End Function
'Function to Display Image
Private Sub ShowImage(ByVal s As String)
Dim i As Integer
Dim str As String
cn = New SqlClient.SqlConnection(SqlConnection1.ConnectionString)
cn.Open()
' Dim str As String = "SELECT photo FROM Photos1 WHERE name='" &
s & "'"
For i = 0 To getCountImage() - 1
str = "SELECT photo FROM Photos1 WHERE (name='" & getNameImage(i)
& "')"
Dim cmd As New SqlClient.SqlCommand(str, cn)
TextBox1.Text = s
Dim b() As Byte
b = cmd.ExecuteScalar()
If (b.Length > 0) Then
Dim stream As New MemoryStream(b, True)
stream.Write(b, 0, b.Length)
DrawToScale(New Bitmap(stream), i)
stream.Close()
End If
Next
cn.Close()
End Sub
Private Sub ShowImage(ByVal s As String, ByVal imgIndex As Integer)
Me.ListView1.LargeImageList = ImageList1
Me.ListView1.View = View.LargeIcon
Dim osItem As ListViewItem.ListViewSubItem
Dim oitem As ListViewItem = New ListViewItem
oitem.Text = s
osItem = New ListViewItem.ListViewSubItem
osItem.Text = s
oitem.SubItems.Add(osItem)
osItem = New ListViewItem.ListViewSubItem
osItem.Text = s & s
oitem.SubItems.Add(osItem)
If imgIndex >= 0 Then
With ListView1
.Items.Add(oitem)
End With
oitem.ImageIndex = imgIndex
End If
End Sub
Private Function getCountImage() As Integer
Dim sqlgetGroup As String
Dim i As Integer
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
If haveData81 = True Then
ds.Tables("cntPicture").Clear()
End If
sqlgetGroup = "SELECT COUNT(*) AS cntPicture"
sqlgetGroup &= " FROM photos1"
da = New SqlDataAdapter(sqlgetGroup, Conn)
da.Fill(ds, "cntPicture")
If ds.Tables("cntPicture").Rows.Count <> 0 Then
haveData81 = True
Return CInt(ds.Tables("cntPicture").Rows.Item(0).ItemArray.GetValue(0))
Else
Return 0
haveData81 = False
End If
End Function
Private Function getNameImage(ByVal imgNumber As Integer) As String
Dim sqlgetName As String
Dim i As Integer
With Conn
If .State = ConnectionState.Open Then .Close()
.ConnectionString = strConn
.Open()
End With
If haveData82 = True Then
ds.Tables("PicName").Clear()
End If
sqlgetName = "SELECT name FROM photos1"
da = New SqlDataAdapter(sqlgetName, Conn)
da.Fill(ds, "PicName")
If ds.Tables("PicName").Rows.Count <> 0 Then
haveData82 = True
Return CStr(ds.Tables("PicName").Rows.Item(imgNumber).ItemArray.GetValue(0))
Else
Return 0
haveData82 = False
End If
End Function
'Function to Create Instance For the Image From the Buffer
Private Sub DrawToScale(ByVal bmp As Image, ByVal imgindex As Integer)
PictureBox1.Image = New Bitmap(bmp)
ImageList1.Images.Add(New Bitmap(bmp))
Me.ListView1.LargeImageList = ImageList1
Me.ListView1.View = View.LargeIcon
Dim osItem As ListViewItem.ListViewSubItem
Dim oitem As ListViewItem = New ListViewItem
oitem.Text = "MainDetail"
osItem = New ListViewItem.ListViewSubItem
osItem.Text = "subDetail"
oitem.SubItems.Add(osItem)
ListView1.Items.Add(oitem)
oitem.ImageIndex = imgindex
End Sub
'Show Button
Private Sub btn_ShowRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles btn_ShowRecord.Click
Dim i As String
ListView1.Items.Clear()
i = "test Show Image"
ShowImage(i)
End Sub
'Exit Button
Private Sub Exit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Exit.Click
Me.Dispose()
End Sub
'Image deletion
Private Sub DeleteRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
DeleteRecord.Click
cn = New SqlClient.SqlConnection(SqlConnection1.ConnectionString)
cn.Open()
Dim s As String = InputBox("Enter name to delete")
Dim cmd As New SqlClient.SqlCommand("delete from photos where name='" & s & "'", cn)
cmd.ExecuteNonQuery()
MsgBox("Image deleted")
cn.Close()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
MessageBox.Show("All Image=" & getCountImage())
ImageList1.Images.Clear()
End Sub
End Class
----------------------
Happy coding,
Preetham