保存并加载图像SQLite C＃ [英] Save and Load image SQLite C#
问题描述
我正在尝试使用带有CF的WinForm上的应用程序使用SQLite保存和加载图像。我找到了一种方法将图像保存到数据库中，但我不知道它是否正确，因为我找不到加载存储在数据库中的图像的方法。
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.
我有一个代码将我的图像转换为Base64：
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();
}
我有一个与ImageToBase64相反的代码，但首先我需要加载来自db的图像。有没有想过这样做？
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?
编辑我正在尝试使用blob来保存图像，就像Charlie建议的那样。我试过这段代码：
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();}
但是当我 ExcecuteNonQuery（）它捕获 InvalidCastException 的错误。
But when I ExcecuteNonQuery() it catch an error of InvalidCastException.
有任何建议吗？
解决方案此代码将图像保存到数据库中，然后加载图像以在pictureBox中显示：
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();
}
}
}
感谢您的帮助！
推荐答案
要从数据库加载图像，请使用SQL select 用于获取数据的语句，就像您对任何其他类型的数据一样。 base64编码的图像作为字符串存储在SQLite数据库中。因此，您将检索它作为字符串，就像您在数据库中存储任何其他字符串（例如，您的名字）一样。
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;
}
与保存代码一样，我加载图片的示例代码只使用一张图片保存到表中。要加载和保存多个图像，您需要在表中插入一个ID字段，然后在SQL 上使用声明。 where 子句选择
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.
我不确定我个人会将图像存储为base64编码的字符串。图像的字符串表示将比二进制表示大得多。 SQLite支持BLOB数据类型，因此我会考虑使用它。
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.
这篇关于保存并加载图像SQLite C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
