CODEWITHSUNDEEP
c#mysql
Rain
Rain

Reputation: 23

Retrieving multiple images from database

So I'm working on my first project and right now there's something that's driving me a little bit crazy and I've been searching but I can't seem to find an answer.

I have two tables in my database, one is for the employee data (employeedata), and another only with pictures from their house (housepictures) with only three fields (PhotoID,EmployeeID,Photo), using a foreign key to the EmployeeID from the employeedata table.

I'm trying to retrieve the pictures from this table and put them in their respectives PictureBox (there are six in total, since I'm only storing 6 images for employee), but I've only managed to either retrieve the first picture, or the last, or the first picture and repeat it (this same photo) in every PictureBox. This is my current code:

    try
    {
        using (MySqlConnection conn = new MySqlConnection(connDB.connstring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select HousePicture from Employees.housepictures where EmployeeID='" + id + "'", conn))
            {
                conn.Open();

                using (MySqlDataReader dr = cmd.ExecuteReader())
                {
                    if (dr.Read())
                    {

                        PictureBox[] pb = { pictureBoxHouse1, pictureBoxHouse2, pictureBoxHouse3, pictureBoxHouse4, pictureBoxHouse5, pictureBoxHouse6 };

                        for (int i = 0; i < pb.Length; i++)
                        {
                            using (MemoryStream stream = new MemoryStream())
                            {

                                if (dr["HousePicture"] != DBNull.Value)
                                {
                                    byte[] image = (byte[])dr["HousePicture"];
                                    stream.Write(image, 0, image.Length);
                                    Bitmap bitmap = new Bitmap(stream);
                                    pb[i].Image = bitmap;
                                }
                            }
                        }

                    }
                }
            }
        }
    }

    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

Any suggestions would be very much appreciated!

Upvotes: 2

Views: 1460

Answers (1)

Grant Winney
Grant Winney

Reputation: 66449

Try getting your results first, then using while (instead of if) to loop through all those returned records. You can leave most of your code as-is, and increment a counter on each iteration of your loop, in order to set the correct PictureBox in your array.

PictureBox[] pb = { pictureBoxHouse1, pictureBoxHouse2, pictureBoxHouse3,
                    pictureBoxHouse4, pictureBoxHouse5, pictureBoxHouse6 };

using (MySqlDataReader dr = cmd.ExecuteReader())
{
    int i = 0;

    while (dr.Read())
    {
        using (MemoryStream stream = new MemoryStream())
        {
            if (dr["HousePicture"] != DBNull.Value)
            {
                byte[] image = (byte[])dr["HousePicture"];
                stream.Write(image, 0, image.Length);
                Bitmap bitmap = new Bitmap(stream);
                pb[i].Image = bitmap;
            }
        }

        i++;
    }
}

Upvotes: 2

Related Questions