Senin, 20 Desember 2010

Save and retrieve image from Access database

Need a table in an Access database with a table named ImageTable with 2 columns:
Name varchar(150)
Image Binary

Need references to the following Namespaces
System.Data.OleDb
System.Drawing
System.Drawing.Imaging
System.IO

Change "YourDBFile" To the name of your MDB file

'First save the image to the table
Private Function SaveImageToDB(ByRef name As String) As Boolean
    Try
        Dim conn As New OleDbConnection
        Dim cmd As OleDbCommand

        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\YourDBFile.mdb;User Id=YourUsername;Password=YourPassword;"
        conn.Open()

        cmd = conn.CreateCommand()
        cmd.CommandText = "INSERT INTO ImageTable(ImageName, Image) VALUES (@Name, @Image)"
        Dim imgByteArray() As Byte
        Try
            Dim stream As New MemoryStream
            Dim bmp As New Bitmap(stream)

            bmp.Save(stream, ImageFormat.Jpeg)
            imgByteArray = stream.ToArray()
            stream.Close()

            cmd.Parameters.AddWithValue("@Name", name)
            cmd.Parameters.AddWithValue("@Image", imgByteArray)

            If DirectCast(cmd.ExecuteNonQuery(), Integer) > 0 Then
                Return True
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try

        conn.Close()
        cmd.Dispose()
        conn.Dispose()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Return False
    End Try
End Function

'Retrieve image from table
Public Function GetImageFromDB(ByRef imageName As String) As Bitmap
    Try
        Dim conn As New OleDbConnection
        Dim cmd As OleDbCommand
        Dim reader As OleDbDataReader

        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\YourDBFile.mdb;User Id=YourUsername;Password=YourPassword;"
        conn.Open()

        cmd = conn.CreateCommand()
        cmd.CommandText = "SELECT Image FROM ImageTable WHERE ImageName = '" & imageName & "'"

        reader = cmd.ExecuteReader

        If reader.Read Then
            Dim imgByteArray() As Byte

            Try
                imgByteArray = CType(reader(0), Byte())
                Dim stream As New MemoryStream(imgByteArray)
                Dim bmp As New Bitmap(stream)
                stream.Close()
                Return bmp
            Catch ex As Exception
                MessageBox.Show(ex.Message)
                Return Nothing                   
            End Try
        End If

        reader.Close()
        conn.Close()

        cmd.Dispose()
        conn.Dispose()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Return Nothing           
    End Try
End Function

Tidak ada komentar:

Posting Komentar