Get Image from MySQL Database - C#

Question

I have to store and restore image into/from MySQL database. For this I used the instructions given on this page: MySQL Forum , and I can successfully store Image via following code:

SaveIamge:

    void SaveImage()
    {
        MemoryStream ms = new MemoryStream();
        pictureBox1.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
        cmd = new MySqlCommand("INSERT INTO " + tableName + " ( Product, Manufacturer, Description, Price, Image) Values ('New_Product', 'New_Manufacturer', 'New_Description', '0', @Image)", conn);
        cmd.Parameters.Add(new MySqlParameter("@Image", Convert.ToBase64String(ms.ToArray())));
        cmd.ExecuteNonQuery();
    }

But I cannot Get the Image from database: I am using following code, please check what is wrong here:

GetThumbnail:

     void GetThumbnail()
     {
        string TN = tableNames[comboBox1.SelectedIndex];
        cmd = new MySqlCommand("SELECT Image FROM " + tableName + " WHERE Product = " + ProductTitle(), conn);
        object imgObj = cmd.ExecuteScalar();
        byte[] b = Convert.FromBase64String(Convert.ToString(imgObj));
        MemoryStream ms = new MemoryStream(b);
        pictureBox2.Image = Image.FromStream(ms);
    }

can you debug and see if imgObj is getting anything?

Trey Copeland
– Trey Copeland

2012-04-13 16:48:03 +00:00
Commented Apr 13, 2012 at 16:48 — Trey Copeland
– Trey Copeland, Commented Apr 13, 2012 at 16:48

Darin Dimitrov · Accepted Answer · 2012-04-13 16:57:35Z

5

How about storing the image into a binary field? It would make more sense than Base64 encoding it:

void SaveImage(byte[] image)
{
    using (var conn = new MySqlConnection(ConnectionString))
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "INSERT INTO pictures (Product, Manufacturer, Description, Price, Image) VALUES ('New_Product', 'New_Manufacturer', 'New_Description', '0', ?Image)";
        cmd.Parameters.Add("?Image", image);
        cmd.ExecuteNonQuery();    
    }
}

byte[] GetImage(string product)
{
    using (var conn = new MySqlConnection(ConnectionString))
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT Image FROM pictures WHERE Product = ?product";
        cmd.Parameters.Add("?product", product);
        using (var reader = cmd.ExecuteReader())
        {
            if (!reader.Read())
            {
                return null;
            }

            const int CHUNK_SIZE = 2 * 1024;
            byte[] buffer = new byte[CHUNK_SIZE];
            long bytesRead;
            long fieldOffset = 0;
            using (var stream = new MemoryStream())
            {
                while ((bytesRead = reader.GetBytes(reader.GetOrdinal("Image"), fieldOffset, buffer, 0, buffer.Length)) > 0)
                {
                    stream.Write(buffer, 0, (int)bytesRead);
                    fieldOffset += bytesRead;
                }
                return stream.ToArray();
            }
        }
    }
}

and then:

using (var ms = new MemoryStream())
{
    pictureBox1.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
    SaveImage(ms.ToArray());
}

and when you want to retrieve:

byte[] image = GetImage("New_Product");
MemoryStream stream = new MemoryStream(image);
pictureBox2.Image = Image.FromStream(ms);

answered Apr 13, 2012 at 16:57

Darin Dimitrov

1.0m275 gold badges3.3k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Bonetti Over a year ago

It only worked if I replace this line while ((bytesRead = reader.GetBytes(reader.GetOrdinal("Image"), fieldOffset, buffer, 0, buffer.Length)) > 0) with this

while ((bytesRead = reader.GetBytes(reader.GetOrdinal("Image"), fieldOffset, buffer, 0, buffer.Length)) == buffer.Length)

Collectives™ on Stack Overflow

Get Image from MySQL Database - C#

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related