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