保存并加载图像 SQLite C#

若要从数据库加载图像，请使用 SQL select 语句来获取数据，就像处理任何其他类型的数据一样。base64 编码的图像作为字符串存储在 SQLite 数据库中。因此，您将将其作为字符串检索，就像在数据库中存储任何其他字符串（例如，您的姓名）一样。

string LoadImage() {
    string query = "select Photo from Table;"; 
    string conString = @" Data Source = 'Program Files'Users.s3db ";            
    SQLiteConnection con = new SQLiteConnection(conString); 
    SQLiteCommand cmd = new SQLiteCommand(query, con);
    string base64EncodedImage = null;
    con.Open(); 
    try {
        IDataReader reader = cmd.ExecuteReader();
        reader.Read(); // advance the data reader to the first row
        base64EncodedImage = (string) reader["Photo"];
        reader.Close();
    }
    catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
    con.Close();
    return base64EncodedImage;
}

与您的保存代码一样，我加载图像的示例代码仅使用保存到表中的一个图像。要加载和保存多个图像，您需要在表中插入一个 ID 字段，然后在 SQL select 语句上使用 where 子句。

不确定我个人是否会将图像存储为 base64 编码的字符串。图像的字符串表示形式将比二进制表示形式大得多。SQLite支持BLOB数据类型，所以我会考虑使用它。

VB.net 中的示例代码：

Imports System.IO
Imports System.Data.SQLite
Public Class Form1
    '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
    'USE THIS FUNCTION TO CREATE A BLOB FROM AN IMAGE FILE'
   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
    'USE THIS FUNCTION TO CREATE A BLOB FROM AN IMAGE VARIABLE IN MEMORY'
    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
    
    'USE THIS SUB TO CREATE A DB AND TABLE'
    Private Sub btnCreateDB_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim SQLconnect As New SQLiteConnection()
        Dim SQLcommand As New SQLiteCommand
        SQLconnect.ConnectionString = "Data Source = " & Application.StartupPath & "'imgdb.sqlite3;"
        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()
        SQLcommand.Dispose()
        SQLconnect.Close()
    End Sub
    'USE THIS SUB TO INSERT IMAGE INTO SQLITE DB.  IT GETS AN id INTEGER FROM 
    Textbox1'
    Private Sub btnInsertImage_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim d As New OpenFileDialog
        d.Filter = "image (*.png)|*.png|(*.jpg)|*.jpg|All files|*.*"
        If d.ShowDialog() = DialogResult.OK Then
            Dim myimage As Bitmap = Clipboard.GetImage()
            Dim SQLconnect As New SQLite.SQLiteConnection()
            Dim SQLcommand As New SQLiteCommand
            SQLconnect.ConnectionString = "Data Source = " & Application.StartupPath & "'imgdb.sqlite3;"
            SQLconnect.Open()
            SQLcommand = SQLconnect.CreateCommand
            'Insert Image, DO NOT single-quote @image'
            SQLcommand.CommandText = "INSERT INTO foo (id,image) VALUES('" + TextBox1.Text + "', @image)"
            'Define @image'
            SQLcommand.Parameters.Add(ImageToBlob("@image", myimage))
            SQLcommand.ExecuteNonQuery()
            SQLcommand.Dispose()
            SQLconnect.Close()
            MessageBox.Show("Pic Inserted")
        End If
    End Sub
    'USE THIS SUB TO GET THE BLOB FROM THE DB AND CONVERT IT BACK TO AN IMAGE'
    Private Sub btnGetImageFromDB_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim SQLconnect As New SQLite.SQLiteConnection()
        Dim SQLcommand As New SQLiteCommand
        SQLconnect.ConnectionString = "Data Source = " & Application.StartupPath & "'imgdb.sqlite3;"
        SQLconnect.Open()
        SQLcommand = SQLconnect.CreateCommand
        SQLcommand.CommandText = "SELECT image FROM foo WHERE id = '" + TextBox1.Text + "'"
        Dim SQLreader As SQLiteDataReader = SQLcommand.ExecuteReader()
        Dim myimage As Bitmap 
        While SQLreader.Read
            myimage = BlobToImage(SQLreader("image"))
            PictureBox1.Image = Nothing
            PictureBox1.Image = myimage
        End While
        SQLcommand.Dispose()
        SQLconnect.Close()
        
    End Sub