保存和加载图像 SQLite C#

Save and Load image SQLite C#

提问by Ignacio Gómez

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.

我正在尝试使用带有 CF 的 WinForm 上的应用程序使用 SQLite 保存和加载图像。我找到了一种将图像保存到数据库中的方法，但我不知道它是否正确，因为我找不到加载存储在数据库中的图像的方法。

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

我有一个代码可以将我的图像转换为 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?

我有一个与 ImageToBase64 相反的代码，但首先我需要从数据库加载图像。有什么想法可以这样做吗？

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

编辑我现在正按照查理的建议尝试使用 blob 保存图像。我试过这个代码：

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.

但是当我ExcecuteNonQuery() 时，它会捕获InvalidCastException的错误。

Any suggest?

有什么建议吗？

SOLUTIONThis 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!

谢谢你的帮助！

采纳答案by Charlie Kilian

To load the image from the database, you use a SQL selectstatement 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.

要从数据库加载图像，您可以使用 SQLselect语句取回数据，就像处理任何其他类型的数据一样。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;
}

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 whereclause on your SQL selectstatement.

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

---

---

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.

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