How to iterate through the table and get the value of a cell? (MVC)

Question

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();

Learning how to query a database is a good goal. Unfortunately for you, StackOverflow is not a tutorial site. The good news is there are "like a bazillion" sites out there that can provide the instruction you need. — Sam Axe, May 02 '18 at 06:29
You have not included any information about your technology for retrieving data... for example are you using Entity Framework? — Hooman Bahreini, May 02 '18 at 06:30
yes i'm using entity framework model to call the database in other pages Hooman. — Maheswarha Rajagopal, May 02 '18 at 06:33
`var results = db.Events.Where(x => x.EmailSent == "no").Select(x => x.EquipID);` will give you a collection of all the `EquipID` where `EmailSent == "no"` — , May 02 '18 at 07:02

score 0 · Answer 1 · answered May 02 '18 at 07:23

0

    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MVCConnectionString"].ConnectionString))
            {
                //replace this with your query
                using (var command = new SqlCommand("SELECT eventid,eqipid,emailsent FROM TableName", connection))
                {
                connection.Open();
                using (var reader = command.ExecuteReader()){
                       if(reader.HasRows){
                             while(reader.Read()){
                                  if (reader["emailsent"].ToString()=="NO"){
                                   //add your function to send email

                             }
                       }
                }
                 }
            }

answered May 02 '18 at 07:23

Ajay Venkata Raju

1,098
12
22

Thanks @Ajay, I've tried your code but it looks like there is some sort of error at [while (reader.Read())]. It shows "Invalid attempt to call Read when reader is closed." I have updated my codes above for your view. Please take a look. Your help is much needed. Thanks. – Maheswarha Rajagopal May 02 '18 at 09:26
May i know which database you are using? is that SQL Server? – Ajay Venkata Raju May 02 '18 at 09:40
i'm using local database @Ajay. It's .mdf – Maheswarha Rajagopal May 03 '18 at 00:48
Ok my suggestion at this point is, make sure you connected to the database first. check if SqlConnection con != null. If true, try to select a sample table from the db which could be done by SqlDataReader reader = command.ExecuteReader() if it is fetching some data from db successfully then we can think about iterating the results – Ajay Venkata Raju May 03 '18 at 04:02
yes the connection works @Ajay , I removed the while(reader) and retained everything else. after this code, string IDIDID = reader["EquipID"].ToString(); I entered this to make sure it works, ViewBag.show = IDIDID; – Maheswarha Rajagopal May 03 '18 at 08:57

score 0 · Accepted Answer · answered May 04 '18 at 03:38

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();
            }
        }

How to iterate through the table and get the value of a cell? (MVC)

2 Answers2