Structuring a table in an email

Question

I Have some data listed in a console application which was fetched from an email. After I send an email with all this listed data. The problem is the data is not coming structured in a nice way and the email isn't understandable. I tried using body html but failed. I was wondering if anyone could help me figure out how to structure the email. below is my code in c#

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Net.Mail;
using System.Net;

namespace sql_connection
{
    class Program
    {
        static void Main(string[] args) 
        {
            string conn = null;
            SqlConnection connection;
            conn = ("Data Source=Database\\SQL2012;Initial Catalog=jobs;User ID=user;Password=passs");

            connection = new SqlConnection(conn);
            try
            {            
                connection.Open();
                Console.WriteLine("Connection Open!");
                SqlCommand cmd = new SqlCommand("SELECT jobs.[dbo].[tb_work].whd_Date,jobs.[dbo].[tb_work].whd_FromTime,jobs.[dbo].[tb_work].whd_ToTime, jobs.[dbo].[tb_work].whd_User,jobs.[dbo].[tb_UserLogin].login_Email FROM jobs.[dbo].[tb_work]INNER JOIN jobs.[dbo].[tb_UserLogin] ON jobs.[dbo].[tb_work].whd_User = jobs.[dbo].[tb_UserLogin].login_LoginId WHERE  DATEDIFF(DAY,[whd_FromTime],GETDATE())<=7 AND   (whd_ToTime = '' OR whd_ToTime IS NULL) AND(whd_User=login_LoginId)");
                cmd.Connection = connection;
                SqlDataReader reader = cmd.ExecuteReader();
                var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();

                var list = new List<string>();
                var col = new List<string>();

                while(reader.Read())
                {
                    var s = string.Format(" {1}     {0}         {2}       {3} ",
                                reader["whd_ToTime"] == DBNull.Value 
                                    ? "NULL" : reader["whd_ToTime"].ToString(), 
                                reader["whd_FromTime"] == DBNull.Value
                                    ? "NULL" : reader["whd_FromTime"].ToString(),                     
                                reader["whd_User"].ToString(),
                                reader["login_Email"].ToString());

                    Console.WriteLine(string.Join("   ", columns.ToArray()));

                    Console.WriteLine(s);
                    list.Add(s);
                }

                var sb = new StringBuilder();
                foreach (var s in list)
                {
                    sb.AppendLine(s);
                }

                connection.Close();

                MailMessage message;                    
                message=new MailMessage();

                MailAddress to = new MailAddress("xxxx@gmail.com");

                MailAddress from = new MailAddress("xxxx@gmail.com");

                MailMessage mail = new MailMessage(from, to);

                mail.Subject = ("missed punch clock");

                message.IsBodyHtml = true;

                StringBuilder html = new StringBuilder();

                html.AppendFormat("<!DOCTYPE html>");
                html.AppendFormat("<html><body><table>");
                html.AppendFormat("<tr><td>");

                html.Append("<table width=600px border=1 cellspacing=2 cellpadding=2 align=center bgcolor=White dir=ltr rules=all style=border-width: thin; border-style: solid; line-height: normal; vertical-align: baseline; text-align: center; font-family: Calibri; font-size: medium; font-weight: normal; font-style: normal; font-variant: normal; color: #000000; list-style-type: none;>");
                for (int rowind = 0; rowind < 1; rowind++)
                {
                    html.Append("<tr>");
                    html.Append("<td>");
                    html.Append("<table width=600px border=1 cellspacing=2 cellpadding=2 align=center bgcolor=White dir=ltr rules=all style=border-width: thin; border-style: solid; line-height: normal; vertical-align: baseline; text-align: center; font-family: Calibri; font-size: medium; font-weight: normal; font-style: normal; font-variant: normal; color: #000000; list-style-type: none;>");

                    for (int newrowind = 0; newrowind < 1; newrowind++)
                    {
                        html.AppendFormat("<tr>");
                        html.Append("<td colspan=1  style=font-weight:bold>");
                        html.Append("whd_ToTime");
                        html.Append("</td>");
                        html.Append("<td colspan=2 style=font-weight:bold>");
                        html.Append("whd_FromTime");
                        html.Append("</td>");
                        html.Append("<td colspan=3 style=font-weight:bold>");
                        html.Append("whd_User");
                        html.Append("</td>");
                        html.Append("</tr>");

                        foreach (var s in list)
                        {
                            html.AppendFormat("<tr>");
                            html.Append("<td colspan=1  style=font-weight:bold>");
                            html.Append(sb.ToString());
                            html.Append("</td>");
                            html.Append("</tr>");
                        }
                    }

                    html.Append("</tr>");
                    html.Append("</td>");
                    html.Append("</table>");
                }

                html.Append("</table>");
                html.AppendFormat("</td></tr>");
                html.AppendFormat("</table></html></body>");

                mail.Body= html.ToString();
                mail.IsBodyHtml = true;

                SmtpClient smtp = new SmtpClient();
                smtp.Host = "smtp.gmail.com";
                smtp.Port = 587;

                smtp.Credentials = new NetworkCredential("xxxx"gmail.com", xxxxxxxx");
                smtp.EnableSsl = true;
                Console.WriteLine("Sending email..");
                smtp.Send(mail);
            } 
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

I suggest you actually look at the HTML text your code produces. You will notice that you don't create a valid HTML table, much less an HTML page. The only relevant line is `string html = ...` , you can delete the rest of the code from the post — Panagiotis Kanavos, May 29 '15 at 08:55
See following webpage : http://stackoverflow.com/questions/19682996/datatable-to-html-table — jdweng, May 29 '15 at 09:08
I tried changing the code to this but still didn't work instead all these words come — lol, May 29 '15 at 09:12
I'll repeat the same comment - look at the HTML. Instead of sending the email, save the text to a file and check it. You *still* don't create a proper table - you don't close the table tags and you put the contents of the style attribute in the tags themselves. Perhaps, you should first try to create a proper HTML file with a table, then use that HTML as a guid on how to build the email message — Panagiotis Kanavos, May 29 '15 at 09:19
I agree with @PanagiotisKanavos . Output your HTML. (eg, via console.WriteLine(mail.Body)) , analyze the content and post it inside your question. — Ole Albers, May 29 '15 at 09:26
so I updated it and the table is forming but now I have a problem. I need the 4 columns from the database to be in the columns they are meant to be under not all the data in one column. basically I don't know how to input the data in a column under each other — lol, May 29 '15 at 10:19
Look at data with text editor. Rows should have and cells in row should have — jdweng, May 29 '15 at 12:32
Please stop nesting tables, it hurts my eyes and makes puppies cry. — Michael McGriff, May 29 '15 at 12:44
@ Michael McGriff: wrong. Nesting tables is the way to go for emails, if you want to make them reponsive and compatible with devices and mail clients. See Zurb Ink framework. — L-Four, May 29 '15 at 13:17

score 0 · Answer 1 · answered May 29 '15 at 09:40

First, in order to pinpoint your issue, just look at the generated HTML and like that you can detect issues in your code that generated it. What you can do is first design the mail in HTML, and when it looks good write your code to implement it.

It's easier to read an HTML template that you load in your code (as an embedded resource) and use placeholders for your custom data instead of building HTML in code - because like this you separate the view (HTML) from the logic.

Note that formatting mails is a difficult task as you have to take into account various devices and mail clients. If you want them to display nicely on all of them, then you better use a framework like Zurb Ink instead of doing everything yourself - this gives you the best chance to end up with responsive emails that work on most devices and clients.

so I updated it and the table is forming but now I have a problem. I need the 4 columns from the database to be in the columns they are meant to be under not all the data in one column. basically I don't know how to input the data in a column under each other — lol, May 29 '15 at 10:19

Structuring a table in an email

1 Answers1

Linked