I have this table called "Events" with where there are columns named 'EquipID' and 'EmailSent' respectively.
The default value for 'EmailSent' is "no" when a data is inserted. Now I have to run a query to iterate through every single row whether an e-mail has been sent or not based on the value. If the query reads 'no', then I have to perform a SMTP function to send a mail according to the corresponding 'EquipID' to it, where I have to fetch the cell value of it. A row can be skipped if the query reads 'yes' instead.
Now I have no idea on how to call a table and query the iteration to get the value of the cells only if the value of 'EmailSent' is 'no'.
I have attached the table design and data together.
I did something like this so far.
con.Open();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MVCConnectionString"].ConnectionString))
{
//replace this with your query
using (var command = new SqlCommand("SELECT EventID, EquipID, EmailSent FROM Events", con))
{
con.Open();
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (reader["EmailSent"].ToString() == "no")
{
string IDIDID = reader["EquipID"].ToString();
//add your function to send email
string sqlView = "SELECT * FROM [NewEquipment] INNER JOIN [User] ON [NewEquipment].[UserID] = [User].[UserID] WHERE EquipID = '" + IDIDID + "'";
using (SqlCommand yes = new SqlCommand(sqlView, con))
{
SqlDataReader read = yes.ExecuteReader();
if (read.Read())
{
string UserEmail = read["UserEmailAdd"].ToString();
string UserFullName = read["UserFullName"].ToString();
string EquipIDID = read["EquipID"].ToString();
string ModelNo = read["ModelNo"].ToString();
string ModelDesc = read["ModelDesc"].ToString();
string CalType = read["CalType"].ToString();
string CalDate = read["EquipCalDueDate"].ToString();
DateTime caldate = DateTime.Parse(CalDate);
string DateDate = caldate.ToString("MM-dd-yyyy");
MailMessage mail = new MailMessage();
mail.To.Add(UserEmail);
mail.From = new MailAddress("keysight@keysight.com");
mail.Subject = "Reminder on Equipment's Cal Due Date";
mail.IsBodyHtml = true;
string Body = "Greetings " + UserFullName + "<br/><br/>This email is to remind you that you have " + "<b>10 days </b>" + "left before you can send the equipment for calibration. Below are the details of the respective equipment: " +
"<br/><br/>Equipment ID: " + EquipIDID + "<br/>Model No.: " + ModelNo + "<br/>Model Description: " + ModelDesc + "<br/>Cal Type: " + CalType + "<br/>Equipment Status: " + "<b>CRITICAL</b>" +
"<br/>Equipment Cal Due Date: " + DateDate + "<br/><br/>Thank you." + "<br/><br/>Regards," + "<br/>Keysight Technologies";
mail.Body = Body;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.cos.is.keysight.com";
smtp.Port = 25;
smtp.Send(mail);
}
read.Close();
con.Close();
}
}
}
}
}
}
}
con.Close();
Thank you guys for your help. I found the answer for it. I've attached it below.
using (var con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\MyDatabase.mdf;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework"))
{
using (var command = con.CreateCommand())
{
command.CommandText = "SELECT EventID, EquipID, EmailSent, EquipCalDueDate, ThemeColor FROM Events";
con.Open();
using (var reader = command.ExecuteReader())
{
var indexOfColumn1 = reader.GetOrdinal("EventID");
var indexOfColumn2 = reader.GetOrdinal("EquipID");
var indexOfColumn3 = reader.GetOrdinal("EmailSent");
var indexOfColumn4 = reader.GetOrdinal("EquipCalDueDate");
var indexOfColumn5 = reader.GetOrdinal("ThemeColor");
while (reader.Read())
{
var value1 = reader.GetValue(indexOfColumn1);
var value2 = reader.GetValue(indexOfColumn2);
var value3 = reader.GetValue(indexOfColumn3);
var value4 = reader.GetValue(indexOfColumn4);
var value5 = reader.GetValue(indexOfColumn5);
if (value5.ToString() == "red" && value3.ToString() == "no") {
string sqlView = "SELECT * FROM [NewEquipment] INNER JOIN [User] ON [NewEquipment].[UserID] = [User].[UserID] WHERE EquipID = '" + value2.ToString() + "'";
using (SqlCommand yes = new SqlCommand(sqlView, con))
{
SqlDataReader read = yes.ExecuteReader();
if (read.Read())
{
string UserEmail = read["UserEmailAdd"].ToString();
string UserFullName = read["UserFullName"].ToString(); string EquipIDID = read["EquipID"].ToString();
string ModelNo = read["ModelNo"].ToString();
string ModelDesc = read["ModelDesc"].ToString();
string CalType = read["CalType"].ToString(); string CalDate = read["EquipCalDueDate"].ToString();
DateTime Edate = DateTime.Parse(CalDate);
double remainingDays = (Edate - DateTime.Now).TotalDays;
int rDays = (int)Math.Round(remainingDays, MidpointRounding.AwayFromZero);
MailMessage mail = new MailMessage();
mail.To.Add(UserEmail);
mail.From = new MailAddress("keysight@keysight.com");
mail.Subject = "Reminder on Equipment's Cal Due Date";
mail.IsBodyHtml = true;
string Body = "Greetings " + UserFullName + "<br/><br/>This email is to remind you that you have " + "<b>"+ rDays + " days </b>" + "left before you can send the equipment for calibration. Below are the details of the respective equipment: " +
"<br/><br/>Equipment ID: " + EquipIDID + "<br/>Model No.: " + ModelNo + "<br/>Model Description: " + ModelDesc + "<br/>Cal Type: " + CalType + "<br/>Equipment Status: " + "<b>CRITICAL</b>" +
"<br/>Equipment Cal Due Date: " + CalDate + "<br/><br/>Thank you." + "<br/><br/>Regards," + "<br/>Keysight Technologies";
mail.Body = Body;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.cos.is.keysight.com";
smtp.Port = 25;
smtp.Send(mail);
}
read.Close();
string yyy = "SELECT * FROM [Events] WHERE EquipID='" + value2.ToString() + "'";
using (SqlCommand cmdcmd = new SqlCommand(yyy, con))
{
SqlDataReader readread = cmdcmd.ExecuteReader();
if (readread.Read())
{
string sql = "UPDATE Events SET EmailSent='yes' WHERE EquipID = '" + value2.ToString() + "'";
SqlCommand cmd1 = new SqlCommand(sql, con);
cmd1.ExecuteNonQuery();
}
readread.Close();
}
}
}
}
}
con.Close();
}
}