How to capture or write data that is returned from a SQL query using C# code

I need to know how I can get all the data in all the rows for four different columns returned from a SQL Stored Proc and then write that data in some type of string prehapps to be in the body of an HTML formated smtp email.

I have code already that gets the id which is used in fromating the email address to loop thru that column to build an data set to populate a dictionary and initialize for the email. The issue is w/the code below.  I need to be able to loop thru the columns returnded and then place that data in the body of the email.
 //foreach (var courseRow in emailList[ntid])
                    //{
                    //    _lastRowAFU = Convert.ToString(courseRow["AttendedAFU"]);
                    //    _lastRowTAU = Convert.ToString(courseRow["AttendedTAU"]);
                    //    _lastRowPU = Convert.ToString(courseRow["AttendedPU"]);
                    //    _lastRowSU = Convert.ToString(courseRow["AttendedSU"]);
                    //}
                    //_lastRowAFU1 = _lastRowAFU;
                    //_lastRowTAU1 = _lastRowTAU;
                    //_lastRowPU1 = _lastRowPU;
                    //_lastRowSU1 = _lastRowSU;
                    //DataRow _lastRowAFU1 = emailList[ntid][0];
                    //DataRow _lastRowTAU1 = emailList[ntid][0];
                    //DataRow _lastRowPU1 = emailList[ntid][0];
                    //DataRow _lastRowSU1 = emailList[ntid][0];
                    //"They need to attend:" + "<br/><br/><b>" + _edWorkShopAssc + "</b><br/><br/><b>" + _edWorkShopSS + "</b><br/><br/><b>" + _edWorkShopPU + "</b><br/><br/>"
                   // body.AppendFormat("{0} {1} {2} {3}", _lastRowAFU1, _lastRowTAU1, _lastRowPU1, _lastRowSU1);
                    //, reader["AttendedTAU"], reader["AttendedPU"]), reader["AttendedSU"]
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            body.AppendFormat(String.Format("{0}", reader["AttendedAFU"]));
                            body.AppendFormat(String.Format("{0}", reader["AttendedTAU"]));
                            body.AppendFormat(String.Format("{0}", reader["AttendedPU"]));
                            body.AppendFormat(String.Format("{0}", reader["AttendedSU"]));
                            body.AppendFormat(String.Format("{0}", reader["Not Attended"]));
                        }
                    }

Open in new window

newjeep19Asked:
Who is Participating?
 
Lokesh B RDeveloperCommented:
Hi,

The following code will get you 5 rows as per the screenshot.

 if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        if (reader["AttendedAFU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedAFU"]));

                        if (reader["AttendedTAU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedTAU"]));

                        if (reader["AttendedPU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedPU"]));

                        if (reader["AttendedSU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedSU"]));

                        if (reader["AttendedAFU"] == null && reader["AttendedTAU"] == null && reader["AttendedPU"] == null && reader["AttendedSU"] == null)
                            body.AppendLine(string.Format("{0}", reader["Not Attended"]));
                    }
                }

Open in new window






Output

Attended Sales University 
Attended Sales University 
Attended Trusted Advisor University
Advanced Auto Attended Property University
Need to attend AF University Associates

Open in new window

0
 
gplanaCommented:
I think what you need is this:

https://msdn.microsoft.com/en-us/library/fksx3b4f.aspx

Is related to a SQL SELECT instead of a SQL stored proc, but the way to do it is the same (just change the SELECT by the call of the stored proc).

Hope it helps. Regards.
0
 
Lokesh B RDeveloperCommented:
Hi,

try this code

  using (SqlDataReader reader = command.ExecuteReader())
            {
             
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        body.AppendLine(string.Format("{0}", reader["AttendedAFU"]));
                        body.AppendLine(string.Format("{0}", reader["AttendedTAU"]));
                        body.AppendLine(string.Format("{0}", reader["AttendedPU"]));
                        body.AppendLine(string.Format("{0}", reader["AttendedSU"]));
                        body.AppendLine(string.Format("{0}", reader["Not Attended"]));
                    }
                }
            }

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
newjeep19Author Commented:
Thank you for your reply. I am getting data returned. However, I may need to explain what I need a bit better.
The results that I need as an example is below:

body.AppendLine(string.Format("{0}", reader["AttendedAFU"])); - if has row then data returned else if null then nothing
body.AppendLine(string.Format("{0}", reader["AttendedTAU"])); - if has row then data returned else if null then nothing
body.AppendLine(string.Format("{0}", reader["AttendedPU"])); - if has row then data returned else if null then nothing
body.AppendLine(string.Format("{0}", reader["AttendedSU"])); - if has row then data returned else if null then nothing
body.AppendLine(string.Format("{0}", reader["Not Attended"])); if all of the 4 rows/columns are null then retruned the data in the this column

I hope that it makes more sense.
Thanks again for your help
0
 
Lokesh B RDeveloperCommented:
Hi,

Are you looking for something like this


   if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        if (reader["AttendedAFU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedAFU"]));

                        if (reader["AttendedTAU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedTAU"]));

                        if (reader["AttendedPU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedPU"]));

                        if (reader["AttendedSU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedSU"]));

                        if (reader["AttendedAFU"] == null && reader["AttendedTAU"] == null && reader["AttendedPU"] == null && reader["AttendedSU"] == null)
                            body.AppendLine(string.Format("{0}", reader["Not Attended"]));
                    }
                }
   if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        if (reader["AttendedAFU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedAFU"]));

                        if (reader["AttendedTAU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedTAU"]));

                        if (reader["AttendedPU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedPU"]));

                        if (reader["AttendedSU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedSU"]));

                        if (reader["AttendedAFU"] == null && reader["AttendedTAU"] == null && reader["AttendedPU"] == null && reader["AttendedSU"] == null)
                            body.AppendLine(string.Format("{0}", reader["Not Attended"]));
                    }
                }

Open in new window

0
 
Tushar ParekhIntegration Project of Major Payment Gateways into a Single Payment Gateway Switching SystemCommented:
Hello,

You can do the following :

STEP 1 : Define your HTML Email Template  with Tokens (Variables) like say
Example
<span> Enter Name : ##Name##  </span>

STEP 2 : open the HTML Template File and use StringBuilder with the ReadAllText() function.

Example:

                   StringBuilder sb = new StringBuilder();
                    sb.Append(File.ReadAllText(HttpContext.Current.Server.MapPath("~/EmailTemplates/x_html.html")));
 


Step 3 : call sb.Replace


Example :
sb.Replace("##PROFORMA##", "");

You can replace these Tokens with whatever string from DataSet , DatTable or any other Data Structure like List,row,etc.

STEP 4 : You can use the SMTP Mail functions in .NET to attache the HTML Template after you complete with Step 3 with
Helper Libraries available on the NET.

Step 5 : Complete the process by sending an Email.

That;s it ! Hope this helps ..

Note: Check out MSDN Documentation for these StringBuilder Libraries . There are some good examples in MSDN to use it.


That's it
0
 
newjeep19Author Commented:
Very close. Thank you agian.
Basically in the query the folowing rows are returned: for ID 1
NULL      <b>NULL      <b>Attended Trusted Advisor University</b>      NULL      NULL
NULL      NULL      NULL      <b>Attended Sales University</b>
NULL      NULL      NULL      <b>Attended Sales University</b>
<b>Did not attend AFU Associates<b> NULL NULL NULL

So, what I need is that ID 1 (email body) to read as follows:
Attended Trusted Advisor University
Attended Sales University -------------------------------------------------------- just needed this once
Attended Sales University --------------------------------------------------------- not needed
Advanced Auto Attended Property University
Did not attend AFU Associates

Make sense. Thanks again
0
 
Lokesh B RDeveloperCommented:
Hi,

there are 4 rows of data and 4 rows are in email body.

you have 2 times the same value, so it is written.

I didn't get from where this came.

Did not attend AFU Associates
0
 
newjeep19Author Commented:
That is what is returned in the SQL query
0
 
Lokesh B RDeveloperCommented:
Hi,

try this code


 if (reader.HasRows)
                {
                    if (reader.Read())
                    {
                        if (reader["AttendedAFU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedAFU"]));

                        if (reader["AttendedTAU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedTAU"]));

                        if (reader["AttendedPU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedPU"]));

                        if (reader["AttendedSU"] != null)
                            body.AppendLine(string.Format("{0}", reader["AttendedSU"]));

                        if (reader["AttendedAFU"] == null && reader["AttendedTAU"] == null && reader["AttendedPU"] == null && reader["AttendedSU"] == null)
                            body.AppendLine(string.Format("{0}", reader["Not Attended"]));
                    }
                }

Open in new window

0
 
newjeep19Author Commented:
I only returned one row when I need all the rows returned please see image.
Picture1.jpg
I need all rows returned in the body of the email:
0
 
newjeep19Author Commented:
Only one row is returned
0
 
newjeep19Author Commented:
Thank you for reply. I am know getting all the courses in the SQL query even if their is a new attendee. What I need is all the courses to come in for just the attendee. Not all the courses per all the attendees. Please see below:
Attendance:
Attended Trusted Advisor University Attended Advanced Auto Attended Property University Attended Advanced Auto Attended Property University Attended Advanced Auto Attended Property University Attended Advanced Auto Attended Property University Attended Advanced Auto Attended Property University Attended AF University Associates
Above is returned for all of the attendees.
0
 
Lokesh B RDeveloperCommented:
Hi,

I think your query is wrong or your code is wrong.
0
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.

All Courses

From novice to tech pro — start learning today.