SQLite Writing/Reading a BLOB Image into the Table

引用來源:SQLite in VB.net - Page 2
--
Imports System.Data.SQLite
Imports System.IO

Public Class sqlitetutorial
   
    'Image BLOB Functions
    Private Function BlobToImage(ByVal blob)
        Dim mStream As New System.IO.MemoryStream
        Dim pData() As Byte = DirectCast(blob, Byte())
        mStream.Write(pData, 0, Convert.ToInt32(pData.Length))
        Dim bm As Bitmap = New Bitmap(mStream, False)
        mStream.Dispose()
        Return bm
    End Function

    Public Overloads Function ImageToBlob(ByVal id As String, ByVal filePath As String)
        Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)
        Dim br As BinaryReader = New BinaryReader(fs)
        Dim bm() As Byte = br.ReadBytes(fs.Length)
        br.Close()
        fs.Close()
        'Create Parm
        Dim photo() As Byte = bm
        Dim SQLparm As New SQLiteParameter("@image", photo)
        SQLparm.DbType = DbType.Binary
        SQLparm.Value = photo
        Return SQLparm
    End Function

    'NOT USED IN THE SOURCE.
    Public Overloads Function ImageToBlob(ByVal id As String, ByVal image As Image)
        Dim ms As New MemoryStream()
        image.Save(ms, System.Drawing.Imaging.ImageFormat.Png)
        'Create Parm
        Dim photo() As Byte = ms.ToArray()
        Dim SQLparm As New SQLiteParameter("@image", photo)
        SQLparm.DbType = DbType.Binary
        SQLparm.Value = photo
        Return SQLparm
    End Function

        Private Sub btn_insertimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_insertimage.Click
        Dim f As New SaveFileDialog
        f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*"
        Dim d As New OpenFileDialog
        d.Filter = "Image (*.png)|*.png|All Files|*.*"
        If f.ShowDialog() = DialogResult.OK And d.ShowDialog() = DialogResult.OK Then
            Dim SQLconnect As New SQLite.SQLiteConnection()
            Dim SQLcommand As SQLiteCommand
            SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
            SQLconnect.Open()
            SQLcommand = SQLconnect.CreateCommand
            'SQL query to Create Table
            SQLcommand.CommandText = "CREATE TABLE foo(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, description TEXT, image BLOB);"
            SQLcommand.ExecuteNonQuery()
            'Insert image, DO NOT single quote @image
            SQLcommand.CommandText = "INSERT INTO foo (image) VALUES(@image)"
            'Define @image
            SQLcommand.Parameters.Add(ImageToBlob("@image", d.FileName))
            SQLcommand.ExecuteNonQuery()
            SQLcommand.Dispose()
            SQLconnect.Close()
        End If
    End Sub

    Private Sub btn_viewimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_viewimage.Click
        Dim f As New OpenFileDialog
        f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*"
        If f.ShowDialog() = DialogResult.OK Then
            Dim SQLconnect As New SQLite.SQLiteConnection()
            Dim SQLcommand As SQLiteCommand
            SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
            SQLconnect.Open()
            SQLcommand = SQLconnect.CreateCommand
            SQLcommand.CommandText = "SELECT image FROM foo WHERE id = '1'"
            Dim SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader()
            While SQLreader.Read()
                pic_viewimage.Image = BlobToImage(SQLreader("image"))
            End While
            SQLcommand.Dispose()
            SQLconnect.Close()
        End If
    End Sub

    Private Sub btn_updateimage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_updateimage.Click
        Dim f As New OpenFileDialog
        f.Filter = "SQLite 3 (*.sqlite3)|*.sqlite3|All Files|*.*"
        Dim d As New OpenFileDialog
        d.Filter = "Image (*.png)|*.png|All Files|*.*"
        If f.ShowDialog() = DialogResult.OK And d.ShowDialog() = DialogResult.OK Then
            Dim SQLconnect As New SQLite.SQLiteConnection()
            Dim SQLcommand As SQLiteCommand
            SQLconnect.ConnectionString = "Data Source=" & f.FileName & ";"
            SQLconnect.Open()
            SQLcommand = SQLconnect.CreateCommand
            'Update image, DO NOT single quote @image
            SQLcommand.CommandText = "UPDATE foo SET image = @image WHERE id = '1'"
            'Define @image
            SQLcommand.Parameters.Add(ImageToBlob("@image", d.FileName))
            SQLcommand.ExecuteNonQuery()
            SQLcommand.Dispose()
            SQLconnect.Close()
        End If
    End Sub
   
End Class