Tuesday, March 23, 2010
Tuesday, March 9, 2010
Embedded Image in Email
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
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
Wednesday, March 3, 2010
InfoPath 2007, saving forms without submitting
Well, I had that need! Where in my context submitting would trigger a workflow, a chain of emails saying what has been done and what needs to be done and so on. . .
Background: I have a forms library that house all the template forms and when any of the forms is filled and submitted, it will be submitted to its corresponding forms library. I thought it will keep the house clean. That was cool. . . but setting up that way was not very easy for me.
I had custom workflow for approval. Why? because approver was selected in the form. (See pervious posting)
After few test run, user wanted to save form without "submit"-ting it. I googled for "saving without submitting," thanks to all helper in infopathdev.com. The problem is, in SharePoint, container where the doc is opened is the default place for saving and my template form did not have "save" option.
I came with this solution.
Add a "Draft" checkbox, when checked, user meant, they are still playing with it and not yet ready for processing by workflow, and if not checked, otherwise. So, I created a Step 1 in my workflow to check the "Draft": if checked do not do anything stop the workflow else do what you are supposed to do.
and That worked!
Background: I have a forms library that house all the template forms and when any of the forms is filled and submitted, it will be submitted to its corresponding forms library. I thought it will keep the house clean. That was cool. . . but setting up that way was not very easy for me.
I had custom workflow for approval. Why? because approver was selected in the form. (See pervious posting)
After few test run, user wanted to save form without "submit"-ting it. I googled for "saving without submitting," thanks to all helper in infopathdev.com. The problem is, in SharePoint, container where the doc is opened is the default place for saving and my template form did not have "save" option.
I came with this solution.
Add a "Draft" checkbox, when checked, user meant, they are still playing with it and not yet ready for processing by workflow, and if not checked, otherwise. So, I created a Step 1 in my workflow to check the "Draft": if checked do not do anything stop the workflow else do what you are supposed to do.
and That worked!
Subscribe to:
Posts (Atom)