How to retrieve images from MySQL database and display as a slide show in Windows Forms application using C# and VB.NET

By | November 29, 2012

In this article I will explain how to retrieve images stored in BLOB field of MySQL database and display as a slide show in Windows Forms application using C#

Download source code

Introduction

In previous article I explained how to save images in Windows Forms application using C#. In this article I will explain how to retrieve images from 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

Write following in Load event of the Form

C# code:

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

VB.NET code:

[sourcecode language=”vb”]
ConString = System.Configuration.ConfigurationManager.ConnectionStrings("ConString").ConnectionString
GetImages()
[/sourcecode]

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

C# code:

[sourcecode language=”csharp”]
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();
}
[/sourcecode]

VB.NET code:

[sourcecode language=”vb”]
Private Sub GetImages()
Dim con As MySqlConnection = New MySqlConnection(ConString)
Dim cmd As MySqlCommand
Dim reader As MySqlDataReader

Dim StudentId As Integer
Dim CmdString As String = "SELECT StudentID, Image FROM Students"

Dim fs As FileStream
Dim bw As BinaryWriter
Dim BufferSize As Integer = 1024

‘Array in VB.NET counts elements from 0(lower bound) to its given size(upper bound.
‘So to declare array of 1024 elements its size has to be 1023
Dim ImageData(BufferSize – 1) As Byte
Dim nBytesReturned, StartIndex As Long

cmd = New MySqlCommand(CmdString, con)
con.Open()
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

If (Directory.Exists("Images") = False) Then
Directory.CreateDirectory("Images")
End If

While (reader.Read())
StudentId = reader.GetInt32("StudentID")
fs = New FileStream("Images/Image_" + StudentId.ToString() + ".jpg", FileMode.OpenOrCreate, FileAccess.Write)
bw = New BinaryWriter(fs)

StartIndex = 0
nBytesReturned = reader.GetBytes(1, StartIndex, ImageData, 0, BufferSize)

While (nBytesReturned = BufferSize)
bw.Write(ImageData)
bw.Flush()
StartIndex += BufferSize
nBytesReturned = reader.GetBytes(1, StartIndex, ImageData, 0, BufferSize)
End While

bw.Close()
fs.Close()
End While

reader.Close()
con.Close()
backgroundWorker1.RunWorkerAsync()
End Sub
[/sourcecode]

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

C# code:

[sourcecode language=”csharp”]
private void btnAddImage_Click(object sender, EventArgs e)
{
Form1 frm1 = new Form1();
frm1.Show();
}
[/sourcecode]

VB.NET code:

[sourcecode language=”vb”]
Dim frm1 As Form1 = New Form1()
frm1.Show()
[/sourcecode]

Write following in DoWork and RunWorkerCompleted event of the BackgroundWorker control

C# code:

[sourcecode language=”csharp”]
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);
}
}
[/sourcecode]

VB.NET code:

[sourcecode language=”vb”]
Private Sub backgroundWorker1_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles backgroundWorker1.DoWork
For Each img As String In Directory.GetFiles("Images")
pictureBox1.BackgroundImage = Image.FromFile(img)
pictureBox1.BackgroundImageLayout = ImageLayout.Zoom
Thread.Sleep(1000)
Next
End Sub
[/sourcecode]

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

C# code:

[sourcecode language=”csharp”]
private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
backgroundWorker1.RunWorkerAsync();
}
[/sourcecode]

VB.NET code:

[sourcecode language=”vb”]
Private Sub backgroundWorker1_RunWorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles backgroundWorker1.RunWorkerCompleted
backgroundWorker1.RunWorkerAsync()
End Sub
[/sourcecode]

0 thoughts on “How to retrieve images from MySQL database and display as a slide show in Windows Forms application using C# and VB.NET

  1. reyno

    your the man dude!! ^_^ thank you so much!

    i have a question 🙁 when i load twice the picture. there’s no picture in the pictureBox. 🙁 can you tell me what to do sir??

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *