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
2. Give the name "Contact Us"
4. Go to Settings->Advance Setting and disable attachment
5. Go to SPD and create workflow that triggers automatically on create. 5.1 Build email
5.2 Send email
5.3 Delete the current Item
7. Few tweaks
4. Go to Settings->Advance Setting and disable attachment
5. Go to SPD and create workflow that triggers automatically on create. 5.1 Build email
5.2 Send email
5.3 Delete the current Item
7. Few tweaks
