How to loop thru a data table in C# that is populated from a SQL strored proc

I have a consol applicaition that I get data via a SQL query in a stroed proc that returns if a person has attended any of 4 classes. If they have not attended any of the required courses then an auto email is sent to the persons boss telling them that they have not attended the class. The issue that I am running into is when some one has attended one or two or three of the courses but not all four of the courses multiple emails are sent stating that they have taken course 1 and course 2 and course 4 then another stating that they need to take course 3. I want to put into the body of  just one email that they have attended course 1, course 2 and course 4 and still need to attended course 4 (as an example).  I have been trying to work out the logic for this on the SQL side w/no success and so I was looking on fixing this issue on the code side. I am using C#.
My code is listed below:
static void Main(string[] args)
        {

		//SQL Connections

            try
            {
                command.CommandType = CommandType.StoredProcedure;

                //add to the datatable
                var dtItems = new DataTable();
                edConnection.Open();

                using (var sda = new SqlDataAdapter(command))
                    sda.Fill(dtItems);
                {
                    //Gather the data
                    //Loop thru all the properties
                    foreach (var row in dtItems.Select())
                    {
                        var _edStudentID = new DataColumn("Student ID", typeof(string));
                        var _edStudentType = Convert.ToString(row["Student Type Dept"]);
                        var _edBossLName = Convert.ToString(row["Boss Last Name"]);
                        var _edBossFName = Convert.ToString(row["Boss First Name"]);
                        var _edEmpLName = Convert.ToString(row["Emp Last Name"]);
                        var _edEmpFName = Convert.ToString(row["Emp First Name"]);
                        var _edEmail = Convert.ToString(row["Email"]);
                        var _edCourseDate = Convert.ToString(row["EndDateTimeView"]);
                        var _edCourseRegDate = Convert.ToString(row["dateTimeRegistered"]);
                        var _edCourseName = Convert.ToString(row["Course Name"]);
                        var _edAttended = Convert.ToString(row["Attended"]);
                        var _edWorkShop = Convert.ToString(row["Workshops"]);
                        var _edWorkShopAssc = Convert.ToString(row["Need Workshops AFU"]);
                        var _edWorkShopSS = Convert.ToString(row["Need SS Workshop"]);
                        var _edWorkShopPU = Convert.ToString(row["Need PU Workshop"]);
                        var _edLSPSD = Convert.ToString(row["LSP Start Date"]);

                       //create columns for the Data Table
                        //DataColumn id = new DataColumn("Student ID", typeof(string));
                       // dtItems.Columns.Add(_edStudentID);

                        _edStudentID.Unique = true;<-trying to make ID unique

                       // DataRow r = null;
                        //for (int i = 0; i < dtItems.Rows.Count ; i++)
                        for (int i = 0; i < _edStudentID.ColumnName.Count; i[b][b]++)<-Trying to loop thru the emp student id's where just one Email is sent. Not working
[/b][/b]                        {
                            if (_edLSPLName != null && _edLSPFName != null)
                            {
                                if (_edAgentLName != null)
                                {
                                    try
                                    {
                                        //LSP's six months or less
                                        DateTime dateConvert = DateTime.Parse(_edLSPSD);
                                        DateTime sixMonths = DateTime.Now.AddDays(-182.621099);

                                        //LSP's more then 24 months
                                        DateTime dateConvertTwoYears = DateTime.Parse(_edCourseDate);
                                        DateTime twoYears = DateTime.Now.AddDays(-730.484398);

                                        if (_edLSPSD != "2001-01-01 00:00:00.0000000" && dateConvert >= sixMonths && _edStudentType == "Employee")
                                        {
                                            var _mailAgent = new MailMessage { From = new MailAddress("WestCentralEducation@allstate.com") };
                                            _mail.To.Add(_edEmail);
                                            _mail.Subject = " **** Attention **** Your newly appointed Employee has not taken one or all of the workshop(s) **** Attention ****";
                                            var email = string.Format("mailto:Education@company.com");
                                            _mail.IsBodyHtml = true;
                                            _mail.BodyEncoding = System.Text.Encoding.UTF32;

                                            StringBuilder sb = new StringBuilder();

                                            StringWriter stringWriter = new StringWriter(sb);

                                            HtmlTextWriter writer = new HtmlTextWriter(stringWriter);

                                            string html = sb.ToString();
                                            writer.Write(html);

                                            //AddAttributesToRender(writer);
                                            writer.AddAttribute(HtmlTextWriterAttribute.Cellpadding, "0");
                                            writer.AddAttribute(HtmlTextWriterAttribute.Cellspacing, "0");

                                            // Create a manually rendered tag.
                                            writer.RenderBeginTag(HtmlTextWriterTag.Table);
                                            writer.RenderBeginTag(HtmlTextWriterTag.Tr);

                                            writer.AddAttribute(HtmlTextWriterAttribute.Height, "0");
                                            writer.AddAttribute(HtmlTextWriterAttribute.Width, "0");
                                            writer.AddAttribute(HtmlTextWriterAttribute.Align, "left");
                                            writer.AddAttribute(HtmlTextWriterAttribute.Valign, "top");
                                            writer.AddAttribute(HtmlTextWriterAttribute.Target, "_blank");
                                            writer.RenderBeginTag(HtmlTextWriterTag.Td);
                                            writer.RenderBeginTag(HtmlTextWriterTag.Img);
                                            writer.RenderEndTag();  // for Img tag      
                                            writer.RenderEndTag();
                                            writer.RenderEndTag();
                                            writer.RenderEndTag();


                                            writer.WriteBeginTag("p");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.AddStyleAttribute("font-size", "24pt");
                                            writer.AddStyleAttribute(HtmlTextWriterStyle.Color, "Silver");
                                            writer.Write("<b><i>Education</i></b><hr/><br/>");
                                            writer.WriteEndTag("p");

                                            writer.WriteBeginTag("table");
                                            writer.Write(HtmlTextWriter.TagRightChar);

                                            writer.WriteBeginTag("tr");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.WriteBeginTag("td");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.Write("Hello " + _edBossFName + "  " + _edBossLName + "<br/>");
                                            writer.WriteEndTag("td");
                                            writer.WriteEndTag("tr");

                                            writer.WriteBeginTag("tr");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.WriteBeginTag("td");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.Write("It is required that your newly appointed LSP  <b>" + _edEmpFName + "  " + _edEmpLName + " </b> attend the following workshops within six months.<br/>");
                                            writer.WriteEndTag("td");
                                            writer.WriteEndTag("tr");

                                            writer.WriteBeginTag("tr");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.WriteBeginTag("td");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.Write("At this time your student has attended the following workshops:");
                                            writer.WriteEndTag("td");
                                            writer.WriteEndTag("tr");

                                            writer.WriteBeginTag("tr");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.WriteBeginTag("td");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.Write("<br/><b> " + _edWorkShop + "</b> in the past six months.<br/>");
                                            writer.WriteEndTag("td");
                                            writer.WriteEndTag("tr");

                                            writer.WriteBeginTag("tr");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.WriteBeginTag("td");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.Write("<br/>They need to attend:" + "<br/><br/><b>" + _edWorkShopAssc + "</b><br/><br/><b>" + _edWorkShopSS + "</b><br/><br/><b>" + _edWorkShopPU + "</b><br/><br/>");
                                            writer.WriteEndTag("td");
                                            writer.WriteEndTag("tr");



                                            writer.WriteBeginTag("tr");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.WriteBeginTag("td");
                                            writer.Write(HtmlTextWriter.TagRightChar);
                                            writer.Write("<br/>You may contact your Educator or email <a href=" + email + "<b>Education@company.com</b></a> with any questions or concerns.");
                                            writer.WriteEndTag("td");
                                            writer.WriteEndTag("tr");

                                         
                                            writer.WriteEndTag("table");

                                            _mail.Body = sb.ToString();

                                            new SmtpClient();
                                            new MailMessage();
                                            var smtp = new SmtpClient { Host = "mail.company.com", Port = # };
                                            smtp.Send(_mail);
                                        }

Open in new window

newjeep19Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Order your dataset on the sql side by so that it's ordered by studentID

Then you can do the following:
string EmailBody = "";
int studentId = -1;
bool Class1 = false;
bool Class2 = false;

DataTable dt = SqlCommand.ExecuteDataReader();

foreach (DataRow row in dt.Rows)
{
  // new student Logic
   if (studentID != (int) row["studentId"])
   {  
    //if you're using other variables run other logic to set EmailBody based on      
      if (!Class1)
          EmailBody += "You're missing class 1;"


      if (!Class2)
                                   //if emailbody is empty                          //email body is this     //else        // email body is this
          Emailbody += String.IsnullOrEmpty(EmailBody) ? "You're missing class 2"        :          "You're also missing class 2";

     //send email for the student if there is a Message Body
      if (!string.IsnullOrEmpty(EmailBody))
              SendEmail(studentID, EmailBody );  

        //set student ID of new student, clear message body, reset other variables if needed
      StudentID= (int) row["studentId"];
      EmailBody = "";
 
    }
   
   //process rows of the current student.  If they're missing a class, add the message to MessageBody.  
   //you may want a few variables set to flags to see which courses they have or are missing.
      if (dr["ClassName"].ToString == "Class1")
          Class1 = true;
      else if (dr["ClassName"].ToString == "Class2")
          Class2 = true;
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
newjeep19Author Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.