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-adonet
http://msdn.microsoft.com/en-us/library/det4aw50.aspx
http://www.dotnetspider.com/forum/ViewForum.aspx?ForumId=39
http://www.codeproject.com/KB/grid/Send_DataGridView_Mail.aspx
http://aspalliance.com/1354_Sending_HTML_Mail_with_Embedded_Image_in_NET.all
http://msdn.microsoft.com/en-us/library/system.data.datatable.select(VS.71).aspx
http://msdn.microsoft.com/en-us/library/system.net.mail.mailmessage.alternateviews.aspx
http://www.fuzzydev.com/blogs/dotnet/archive/2006/07/23/System_Net_Mail_AlternateView_LinkedResource.aspx
No comments:
Post a Comment