To do:
1. Collect data from a database
2. Group records by employee
3. Create a personalized email with images
Solution I persued:
I have a report in SSRS with all content and when printed it is ready to be "snail" mailed but I need to email. I did not know how to email part of report pertaining to each employee. So, I used C# to do so. Here too, I did not use the report viewer to email as email address would be in report data and I don't know how to extract the data from report viewer.
Instead I invisioned a large data set, a table, containing everything. This is result from the query, as in SSRS, I would then do grouping etc as required. I would then Separate/group dataset per emp; create an email; then send it. I would repeat it for all the employees.
1. Collect data from database: Well there might be number of ways but I used SQLDataAdapter to collect my data.
using (SqlConnection conn = new SqlConnection (ConfigurationSettings.AppSettings["ConnectionString"])){ conn.Open(); using (SqlDataAdapter da = new SqlDataAdapter (sqlQuery, conn)){ DataTable dt = new DataTable(); da.Fill(dt);Now I have my big dataset, I now need to separate them for each employee. My data had EmpId in the set so collected distinct empid from the set using:
DataTable empId = dt.DefaultView.ToTable(true, "EMPLOYID");2. I have now set of employee in empId data table, pick one employee at a time, filter dt for that employee: I used,
DataRow[] dRow = dt.Select("EMPLOYID=" + empId.Rows[loopIndex][0].ToString());This is the way to filter data table and it will return the array or data rows that matches the selection criteria.
Now I have the group, I created another data table to store information. This may have been extra steps but this is how I did:
foreach (DataRow dr in dRow){DataRow newRow = EmpTable.NewRow();newRow.ItemArray = dr.ItemArray;EmpTable.Rows.Add(newRow);}3. Now email needs to be created. I choose html body for, email would contain text and images. I used StringBuilder for this purpose. In fact I created whole HTML document with tables to format the content and again tables to add grid for records for the employee. Important parts were:
StringBuilder emailBody = new StringBuilder();emailBody.AppendLine("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 Transitional//EN\"> <html> <head> <meta http-equiv=Content-Type content='text/html; charset=iso-8859-1'></head><body> <table border='0' cellpadding='0' cellspacing='0'><tr><td><img src='cid:companyLogo' align='right' /></td></tr>");for (
int i = 0; i <>
{//create table using <table> tag and put my data}emailBody.AppendLine ("</table></body></html>");Email email = new Email("sender", "receiver", "subject", emailBody);email.Send(); Email Class:
class Email{private string from, to, subject;private StringBuilder body;SmtpClient smtp;LinkedResource lrCompanyLogo;public Email(string from, string to, string subject, StringBuilder body){this.from = from;this.to = to;this.subject = subject;this.body = body;lrCompanyLogo = new LinkedResource("path to file system", new ContentType("image/jpeg"));lrCompanyLogo.ContentId = "companyLogo"; //same as used in 'cid' in img tag}public void Send(){mail = new MailMessage();mail.To.Add(to);mail.From = new MailAddress(from);mail.Subject = subject.ToString();mail.BodyEncoding = Encoding.UTF8;mail.IsBodyHtml = true;ContentType mimeType = new ContentType ("text/html");AlternateView htmlBody = AlternateView.CreateAlternateViewFromString(this.body.ToString(), mimeType);htmlBody.LinkedResources.Add(rlCompanyLogo);mail.AlternateViews.Add(htmlBody);smtp = new SmtpClient();smtp.Host = ConfigurationSettings.AppSettings["EmailHost"];smtp.Send(mail);}Important concept:
DataRow[] dRow = dt.Select(boolExpression);DataTable empId = dt.DefaultView.ToTable (true, "EMPLOYID")equiv: "SELECT DISTINCT EMPLOYID FROM dt"<img src='cid:uniquId' />LinkedResources lr = new LinkedResources ("path to file", "image/jpeg");rl.ContentId = "uniqueId";AlternateView htmlBody = AlternateView.CreateAlternateViewFromString(body.ToString(), new ContentType("text/html"));htmlBody.LinkedResources.Add (rl);mail.AlternateViews.Add(htmlBody);Thanks to all of the followings:
http://dotnetperls.com/sqldataadapter-adonethttp://msdn.microsoft.com/en-us/library/det4aw50.aspxhttp://www.dotnetspider.com/forum/ViewForum.aspx?ForumId=39http://www.codeproject.com/KB/grid/Send_DataGridView_Mail.aspxhttp://aspalliance.com/1354_Sending_HTML_Mail_with_Embedded_Image_in_NET.allhttp://msdn.microsoft.com/en-us/library/system.data.datatable.select(VS.71).aspxhttp://msdn.microsoft.com/en-us/library/system.net.mail.mailmessage.alternateviews.aspxhttp://www.fuzzydev.com/blogs/dotnet/archive/2006/07/23/System_Net_Mail_AlternateView_LinkedResource.aspx
