I have created a method that pulls data from a database to a datagridview and from that datagridview, a method that sends email. I coded it like so:
// TODO: Prepare the email addresses
private void SendEmail()
{
// Get the email addresses according to bio_id
var rdr = new EmployeeDataAccess().GetBioIdEmail();
while (rdr.HasRows && rdr.Read())
{
dgvBioIdList.Rows.Add(rdr["bio_id"].ToString(), rdr["email_add"].ToString());
}
// Get the time logs for each of the bio_id in the datagridview
Cursor.Current = Cursors.WaitCursor;
for (var i = 0; i < dgvBioIdList.Rows.Count; i++)
{
using (var cn = new DatabaseConnection().ConnectToMySql())
{
const string query = @"SELECT MIN(scan_time) as 'TimeIn', MAX(scan_time) as 'TimeOut',
TIMEDIFF(MAX(scan_time),MIN(scan_time)) as difference
FROM `filtered_dates`
WHERE date BETWEEN @fromDate AND @toDate AND bio_id = @bioId
GROUP BY date
ORDER BY date asc";
var cmd = new MySqlCommand(query, cn);
cn.Open();
cmd.Parameters.AddWithValue("@fromDate", dtpStart.Text);
cmd.Parameters.AddWithValue("@toDate", dtpStop.Text);
cmd.Parameters.AddWithValue("@bioId", dgvBioIdList.Rows[i].Cells[0].Value);
rdr = cmd.ExecuteReader();
// Create the message to send to the email
if (!rdr.HasRows) continue;
var message = "Time Logs for Bio ID: " + dgvBioIdList.Rows[i].Cells[0].Value;
message += Environment.NewLine;
message += @"Included dates: " + dtpStart.Text + @" to " + dtpStop.Text;
message += Environment.NewLine;
message += @"Email Address: " + dgvBioIdList.Rows[i].Cells[1].Value;
message += Environment.NewLine;
while (rdr.Read())
{
message += rdr["TimeIn"] + @" - " + rdr["TimeOut"] +@" = " +rdr["Difference"];
message += Environment.NewLine;
}
// Actual sending of the email
SendingEmail(dgvBioIdList.Rows[i].Cells[0].Value.ToString(),dgvBioIdList.Rows[i].Cells[1].Value.ToString(), message, txtUserName.Text, txtPassword.Text);
}
}
Cursor.Current = Cursors.Default;
}
// Sending of the E-mail and to list the status of the sending in a listview
private void SendingEmail(string bioId, string recipient, string body, string userName, string password)
{
var mail = new MailMessage();
var smtpServer = new SmtpClient("smtp.gmail.com");
mail.From = new MailAddress(userName);
mail.To.Add(recipient);
mail.Subject = "Time Logs";
mail.Body = body;
smtpServer.Port = 587;
smtpServer.Credentials = new System.Net.NetworkCredential(userName, password);
smtpServer.EnableSsl = true;
try
{
smtpServer.Send(mail);
string[] row = {bioId, recipient, "PASSED"};
var listViewItem = new ListViewItem(row);
lvEmailStatus.Items.Add(listViewItem);
//Console.WriteLine(@"Successfully sent mail to " + recipient);
}
catch (Exception ex)
{
string[] row = { bioId, recipient, "FAILED" };
var listViewItem = new ListViewItem(row);
lvEmailStatus.Items.Add(listViewItem);
Console.WriteLine(@"Failed sending mail " + recipient);
return;
}
}
This code works fine except that, the listview should be filled through each iteration of rows in the datagridview. What happens is; it sends email first to everyone in the datagridview and while it is doing so, the program somewhat freezes, but you can still minimize and close the window though. As soon as the sending is done, that will be the time that the listview will be filled in with data together with the status of the sending if it passed or failed. But if I do this in the Console.Writeline, I can see the status of the sending before going to the next row of the datagridview! It should be like this - (choose the row) - (send email) - (see the listview populated) - (go to the next row) - (send email) - (see the listview populated) .... loop until last row in the datagridview.
Your help is very much appreciated.
