Slide Show in C# Windows Forms Application Using MySQL Database

Introduction

In my previous article I explained how to save images in a Windows Forms application using C#. In this article I will explain how to retrieve images from a MySQL database using SequentialAccess and show as a slide show in a Windows Forms application

Description

Insert some images in the database as discussed in the previous article. Add a new Form to the Windows Application and add a PictureBox, a Button and a BackgroundWorker control to the Form.

get-image-from-mysql.jpg

Write the following in the Load event of the form:

private void Form2_Load(object sender, EventArgs e)
{
    ConString = System.Configuration.ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
    GetImages();
}

The GetImages() method gets bytes from the BLOB column of the database and creates image files in the Images folder using ExecuteReader() of the MySqlDataReader method. An overloaded version of the ExecuteReader() method is used with a parameter of CommandBehavior.SequentialAccess because a BLOB field may contain data up to 4GB and it cannot be contained in a single row, it has to be retrieved as a stream. After images are created, the RunWorkerAsync() method of the BackgroundWorker control is called. It causes the DoWork event of the BackgroundWorker control to fire which binds images one by one in the PictureBox at an interval of 1000 milliseconds to simulate a slide show

private void GetImages()
{
    MySqlConnection con = new MySqlConnection(ConString);
    MySqlCommand cmd;
    MySqlDataReader reader; 
    int StudentID; 
    string CmdString="SELECT StudentID, Image FROM Students"
    FileStream fs;
    BinaryWriter bw;
    int bufferSize = 1024; // Number of bytes to read at a time
    byte[] ImageData = new byte[bufferSize];
    long nBytesReturned, startIndex = 0;
    cmd = new MySqlCommand(CmdString, con);
    con.Open();
    reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
    if (!Directory.Exists("Images"))
    {
        Directory.CreateDirectory("Images");
    }
    while (reader.Read())
    {
        StudentID = reader.GetInt32("StudentID");
        // Create image files in Images folder using bytes retrieved from the database
        fs = new FileStream("Images/Image_" + StudentID.ToString() + ".jpg", FileMode.OpenOrCreate, FileAccess.Write);
        bw = new BinaryWriter(fs);
        startIndex = 0;
        nBytesReturned = reader.GetBytes(
            1, // Column index of BLOB column
            startIndex, // Start position of the byte to read
            ImageData, // Byte array to recieve BLOB data
            0, // Start index of the array
            bufferSize // Size of buffer
            ); 
        // Continue if total bytes are more than the buffer size
        while (nBytesReturned == bufferSize)
        {
            bw.Write(ImageData);
            bw.Flush();
            startIndex += bufferSize;
            nBytesReturned = reader.GetBytes(1, startIndex, ImageData, 0, bufferSize); // Number of bytes returned is assigned to nBytesReturned
        }
        bw.Write(ImageData, 0, (int)nBytesReturned - 1);
        bw.Close();
        fs.Close();
    }
    reader.Close();
    con.Close();
    backgroundWorker1.RunWorkerAsync();
}

Write the following in the Button click event to open a form to add images to the database:

private void btnAddImage_Click(object sender, EventArgs e)
{
    Form1 frm1 = new Form1();
    frm1.Show();
}

Write the following in DoWork and the RunWorkerCompleted event of the BackgroundWorker control:

private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
    foreach (string imgFile in Directory.GetFiles("Images"))
    {
        pictureBox1.BackgroundImage = Image.FromFile(imgFile);
        pictureBox1.BackgroundImageLayout = ImageLayout.Zoom;
        Thread.Sleep(1000);
    }
}

When the DoWork event completes it is restarted using the RunWorkerAsync() method in the RunWorkerCompleted event of the BackgroundWorker. It runs the slide show again from the start and it never ends.

private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    backgroundWorker1.RunWorkerAsync();
}