图像插入和从MySql数据库检索. [英] Image insert and retrieve from MySql Database.

问题描述

我正在开发一个系统，我必须存储这些人的图像，并告诉我该怎么做.如何在mysql中插入图像并能够在C#Windows应用程序中检索它们.我该怎么办?

I am developing a system and I have to store the images of the persons, tell me how should I do that. How can I insert the images in mysql and able to retrieve them in C# Windows Application. What should I do?

推荐答案

如果您使用的是Windows窗体，则非常简单.

用于存储:

If you are using Windows Forms, its pretty easy.

For storing:

conn = new MySqlConnection("server=" + hostname + ";uid=" + username + ";pwd=" + password + ";database=databaseimage;Charset=latin1;");
                conn.Open();
                FileStream fs;
                Byte[] bindata;
                MySqlParameter picpara;
                cmd = new MySqlCommand("INSERT INTO mypic (pic) VALUES(?pic)", conn);
                picpara = cmd.Parameters.Add("?pic", MySqlDbType.MediumBlob);
                cmd.Prepare();

//txtPicPath is the path of the image, e.g. C:\MyPic.png

                fs = new FileStream(txtPicPath.Text, FileMode.Open, FileAccess.Read);
                bindata = new byte[Convert.ToInt32(fs.Length)];
                fs.Read(bindata, 0, Convert.ToInt32(fs.Length));
                fs.Close();

                picpara.Value = bindata;
                cmd.ExecuteNonQuery();

要检索它:

To retrieve it:

if (conn == null) // Just to make sure that the connection was not severed
            {

           
                    conn = new MySqlConnection("server=" + hostname + ";uid=" + username + ";pwd=" + password + ";database=databaseimage;Charset=latin1;");
                    conn.Open();
               
            }
            MemoryStream ms = new MemoryStream();
            FileStream fs;
            Byte[] bindata;

            cmd = new MySqlCommand("SELECT pic FROM mypic WHERE id=3", conn);
            bindata = (byte[])(cmd.ExecuteScalar());
            


            ms.Write(bindata, 0, bindata.Length);
            pb2.Image = new Bitmap(ms);
          
            fs = new FileStream(name, FileMode.Create, FileAccess.Write);
            ms.WriteTo(fs);

注意:
pb1 是一个图片框，允许我查看预览，而 pb2 是显示检索到的图像的地方.
数据库名称为 databaseimage
表名称为 mypic .
该表有两列， pic (类型为 mediumblob )和 id (类型为 int ，其 autoincreament ). id列可让您选择数据库中的其他图片，例如就我而言，我将进入第3行:

Notes:
pb1 is a picture box that allows me to see the preview and pb2 is where the retrieved image is shown.
The database name is databaseimage
The table name is mypic.
The table has two columns, pic (type is mediumblob) and id (type is int and it is autoincreament). The id column allows one to select different pictures in the db, e.g. for my case i am getting row 3:

cmd = new MySqlCommand("SELECT pic FROM mypic WHERE id=3", conn);

此处有一篇文章:使用ASP在MySQL中存储图像.NET [ ^ ]-它是用于ASP.NET而不是Winforms的，但是数据库机制是相同的.

There is an article here: Storing Images in MySQL using ASP.NET[^] - it is for ASP.NET rather than Winforms, but the database mechanism is the same.

如果需要，可以将图像以二进制形式保存在DB中:

if u want, u can Save images in DB as a binary like this :

MemoryStream msQ = new MemoryStream();
// pic is an image
pic.Save(msQ, ImageFormat.Jpeg);

                        SqlCommand cmd = new SqlCommand("USP_INS_Pic", new SqlConnection(new AppSettingsReader().GetValue("CS", typeof(string)).ToString()));
                        cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();

并检索它:

And retrive it :

MemoryStream msPic = new MemoryStream();
 SqlCommand cmdPic = new SqlCommand("SELECT * FROM  tPic", new SqlConnection(new AppSettingsReader().GetValue("CS", typeof(string)).ToString()));

cmdPic.Connection.Open();
SqlDataReader sdr = cmdPic.ExecuteReader();
while (sdr.Read())
{
    byte[] BPic = (byte[])sdr["pic"];
    if (BPic.Length > 0)
    {
        msPic.Write(BPic, 0, BPic.Length);
// Pic is n Image
        Pic = Image.FromStream(msPic, true);
     }

sdr.Close();
            cmdPic.Connection.Close();

这篇关于图像插入和从MySql数据库检索.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！