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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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
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
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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
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

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:
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
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.