我正在尝试使用带有CF的WinForm上的应用程序使用sqlite保存和加载图像.我找到了一种方法将图像保存到数据库中,但我不知道它是否正确,因为我找不到加载存储在数据库中的图像的方法.
我有一个代码将我的图像转换为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); } }
这是我将图像保存到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(); }
我有一个代码与ImageToBase64相反,但首先我需要从db加载图像.有没有想过这样做?
编辑我正在尝试使用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();}
但是当我ExcecuteNonQuery()时,它会捕获InvalidCastException错误.
有什么建议?
解决方案此代码将图像保存到数据库中,然后加载图像以在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,imageBytes.Length); ms.Write(imageBytes,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(); } } }
谢谢你的帮助!
要从数据库加载映像,可以使用sql select语句来获取数据,就像使用任何其他类型的数据一样. base64编码的图像作为字符串存储在sqlite数据库中.因此,您将检索它作为字符串,就像您在数据库中存储任何其他字符串(例如,您的名字)一样.
原文链接:https://www.f2er.com/sqlite/197693.htmlstring 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 supports the BLOB data type,所以我会考虑使用它.