how to retrieve image in MySQL database longblob?

问题:

I am using Windows Form and MySQL for my project. In that i want to save a image and retrieve that.

I have created a table named 'image' in that,

CREATE TABLE `image` (
    `id` INT(15) NOT NULL AUTO_INCREMENT,
    `extension` VARCHAR(50) NOT NULL,
    `image` LONGBLOB NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=2

And

OpenFileDialog open = new OpenFileDialog();
            // image filters
            open.Filter = "Image Files(*.jpg; *.jpeg; *.gif; *.bmp; *.png)|*.jpg; *.jpeg; *.gif; *.bmp; *.png";
            if (open.ShowDialog() == DialogResult.OK)
            {                
                txt_imagePath.Text = open.FileName;
            }

            hp.getConnStr();
            try
            {
                MySqlConnection connection = new MySqlConnection(hp.myConnStr);
                MySqlCommand command = connection.CreateCommand();                
                command.CommandText = "insert into image (image) values ('"+txt_imagePath.Text +"')";
                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }

in Browse button click event,..

The file is successfully saved. Now i want to retrieve that picture and show in picture box. So that i try this bellow code,..

 MySqlConnection connection = new MySqlConnection(hp.myConnStr);
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;
                command.CommandText = "select image from image";
                connection.Open();
                Reader = command.ExecuteReader();
                while (Reader.Read())
                {
                   pictureBox1.Image = new Bitmap(Reader[0].ToString()); 
                }
                connection.Close(); 

but no use.

Please help me.

回答1:

You should read the blob field as a MemoryStream and set to Image property of the control using Image.FromStream().

回答2:

I hope something like this would help:

public Bitmap loadImage(int imgID)
        {

            MySqlDataReader myData;
            MySqlCommand cmd = new MySqlCommand();

            string SQL;
            byte[] rawData;
            MemoryStream ms;
            UInt32 FileSize;
            Bitmap outImage;

            SQL = "SELECT ImageName, ImageSize, Image FROM Images WHERE ImageID =";
            SQL += imgID.ToString();

            try
            {
                cmd.Connection = connection;
                cmd.CommandText = SQL;

                myData = cmd.ExecuteReader();

                if (!myData.HasRows)
                    throw new Exception("There are no blobs to save");

                myData.Read();

                FileSize = myData.GetUInt32(myData.GetOrdinal("ImageSize"));
                rawData = new byte[FileSize];

                myData.GetBytes(myData.GetOrdinal("Image"), 0, rawData, 0, (Int32)FileSize);


                ms = new MemoryStream(rawData);
                outImage = new Bitmap(ms);
                ms.Close();
                ms.Dispose();

                myData.Close();
                myData.Dispose();

                cmd.Dispose();

                return outImage;


            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

        }

回答3:

while (Reader.Read()) 
{ 
  pictureBox1.Image = new Bitmap(new MemoryStream((byte[])Reader.GetValue(0)));
} 

this should do it. reader.GetValue() returns byte array on MySQL blobs, this does the trick.