Save and Load image SQLite C#

Question

I'm trying to save and load images with SQLite with an app on WinForm with CF. I found a way to save an image into the db but I don't know if it's right because I couldn't find a way to load the image stored in the db.

I have a code to convert my image into a Base64:

public void ImageToBase64(Image image, System.Drawing.Imaging.ImageFormat format){
        using (MemoryStream ms = new MemoryStream()){
            // Convert Image to byte[]
            image.Save(ms, format);
            byte[] imageBytes = ms.ToArray();

            // Convert byte[] to Base64 String
            string base64String = Convert.ToBase64String(imageBytes);
            SaveImage(base64String);
        }
    }

This is my code to save the image into the db:

void SaveImage(string pic){
        string query = "insert into Table (Photo) values (@pic);"; 
        string conString = @" Data Source = \Program Files\Users.s3db ";            
        SQLiteConnection con = new SQLiteConnection(conString); 
        SQLiteCommand cmd = new SQLiteCommand(query, con);
        cmd.Parameters.Add("@pic",DbType.String);
        con.Open(); 
        try{
            cmd.ExecuteNonQuery();
        }
        catch (Exception exc1){
            MessageBox.Show(exc1.Message);
        }
        con.Close();
    }

I have a code to make the opposite of ImageToBase64 but first I need to load the image from the db. Any idea to do that?

EDIT I am trying to use the blob to save the image now as Charlie suggested. I tried this code:

Image photo = new Bitmap(@"\Photos\Image20120601_1.jpeg");
SaveImage(photo);

void SaveImage(Image pic){
string conString = @" Data Source = \Program Files\Users.s3db ";            
SQLiteConnection con = new SQLiteConnection(conString);
SQLiteCommand cmd = con.CreateCommand();
cmd.CommandText = String.Format("INSERT INTO Table (Photo) VALUES (@0);");
SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary);
param.Value = pic;
cmd.Parameters.Add(param);
con.Open(); 
try{
    cmd.ExecuteNonQuery();
}
catch (Exception exc1){
    MessageBox.Show(exc1.Message);
}
con.Close();}

But when I ExcecuteNonQuery() it catch an error of InvalidCastException.

Any suggest?

SOLUTION This code save an image into the database and then it load the image to show it in a pictureBox:

namespace ImagenSQLite
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            Image photo = new Bitmap(@"\Photos\Image20120601_1.jpeg");
            byte[] pic = ImageToByte(photo, System.Drawing.Imaging.ImageFormat.Jpeg);
            SaveImage(pic);
            LoadImage();
        }

        public byte[] ImageToByte(Image image, System.Drawing.Imaging.ImageFormat format){
            using (MemoryStream ms = new MemoryStream())
            {
                // Convert Image to byte[]
                image.Save(ms, format);
                byte[] imageBytes = ms.ToArray();
                return imageBytes;
            }
        }
        //public Image Base64ToImage(string base64String)
         public Image ByteToImage(byte[] imageBytes)
        {
            // Convert byte[] to Image
            MemoryStream ms = new MemoryStream(imageBytes, 0, imageBytes.Length);
            ms.Write(imageBytes, 0, imageBytes.Length);
            Image image = new Bitmap(ms);
            return image;
        }
        /***************** SQLite **************************/
        void SaveImage(byte[] imagen){
            string conStringDatosUsuarios = @" Data Source = \Program Files\GPS___CAM\Data\DatosUsuarios.s3db ";            
            SQLiteConnection con = new SQLiteConnection(conStringDatosUsuarios); 
            SQLiteCommand cmd = con.CreateCommand();
            cmd.CommandText = String.Format("INSERT INTO Empleados (Foto) VALUES (@0);");
            SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary);
            param.Value = imagen;
            cmd.Parameters.Add(param);
            con.Open();

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception exc1)
            {
                MessageBox.Show(exc1.Message);
            }
            con.Close();
        }
        void LoadImage(){
            string query = "SELECT Photo FROM Table WHERE ID='5';";
            string conString = @" Data Source = \Program Files\Users.s3db ";
            SQLiteConnection con = new SQLiteConnection(conString); 
            SQLiteCommand cmd = new SQLiteCommand(query, con);            
            con.Open();
            try
            {
                IDataReader rdr = cmd.ExecuteReader();
                try
                {
                    while (rdr.Read())
                    {
                        byte[] a = (System.Byte[])rdr[0];
                        pictureBox1.Image = ByteToImage(a);
                    }
                }
                catch (Exception exc) { MessageBox.Show(exc.Message); }
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
            con.Close();
        }       
    }
}

Thanks for your help!

Answer 1

To load the image from the database, you use a SQL select statement to get the data back, just like you would for any other kind of data. The base64-encoded image is stored as a string in the SQLite database. So you will retrieve it as a string, just like if you were storing any other string (like, for example, your name) in the database.

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;
}

Like your saving code, my example code to load the image uses only one image saved to the table. To load and save more than one image, you would need to insert an ID field into the table, and then use a where clause on your SQL select statement.

---

I'm not sure that I, personally, would store the image as a base64-encoded string. The string representation of the image will be much larger than the binary representation. SQLite supports the BLOB data type, so I would look into using that.

Answer 2

Here is sample Code in 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