Image retrieval from SQL Server

Here is a sample code snippet to show you how the images can be retrieved from the database and are displayed on the screen.

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

By preetham preetham   Popularity  (2092 Views)