I need help in achieveing a requirement, please. How do I achieve a situation where I can send pdf documents, as attachment, via email to multiple recipients displayed in a GridView, without mixing up the document?
What I mean is, let's say I have about 5 recipients that I want to send these documents attachment to. Each document bears the name of each recipient, and I want to send them to each recipient accordingly.
For example:
Recipients: Timothy, Sandra, Eric, Michael, Rita
Documents: 1, 2, 3, 4, 5.
Document 1 has Timothy's name on it, Document 2 has Sandra's name on it, Document 3 has Eric's name on it, and so on.... So I want to send these documents such that Document 1 will not go to Eric, Sandra, Michael or Rita because it's Timothy's document, and Document 2 will not go any other recipient but only to Sandra. I also do not want one particular document to be sent to all recipients.
The documents are saved in the Database in PDF format. My GridView has Pagination. I would want that even if I have hundred names in the Gridview, I can send to the names even if some names will be displayed in other GridView pages due to the Pagination. I hope my explanation can be easily understood?
Thank you
Here is my GridView where the recipients' names are displayed along with their email addresses
<div class="col-sm-11" style="width: 100%; margin: 0 auto; padding: 10px; margin-right: auto; margin-left: auto;">
<asp:Label ID="createby" runat="server" Text="1"></asp:Label>
<asp:UpdatePanel ID="panel" runat="server" ChildrenAsTriggers="true">
<ContentTemplate>
<div class="container-fluid p-3 mb5 bg-white rounded" id="card" style="margin: 0 auto; padding: 10px; border: 1.3px solid #e4e7e8;">
<asp:GridView ID="GridView1" runat="server" GridLines="None" DataKeyNames="Id" AllowPaging="true" HeaderStyle-BackColor="#fdfdfd" HeaderStyle-Font-Bold="false" HeaderStyle-ForeColor="#05214d" HeaderStyle-Font-Size="10pt" Font-Size="9pt"
AutoGenerateColumns="false" HeaderStyle-HorizontalAlign="left" RowStyle-HorizontalAlign="Left" OnPageIndexChanging="OnPageIndexChanging" class="table" Width="100%">
<EmptyDataTemplate>
<div style="text-align: center; font-weight: 500; margin-top: 2%;">
<i class="fal fa-file-times" style="margin: 0 auto; font-size: 30pt; color: #145c7c;"></i>
<p id="P1" runat="server" style="font-size: 11pt; font-weight: 400;">No Recipient</p>
</div>
</EmptyDataTemplate>
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="CheckSelected" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Id" HeaderText="ID" HeaderStyle-Font-Bold="false" />
<asp:BoundField DataField="Recipient" HeaderText="Recipients" HeaderStyle-Font-Bold="false" />
<asp:TemplateField HeaderText="Email" HeaderStyle-Font-Bold="false">
<ItemTemplate>
<asp:HyperLink ID="emailLink" runat="server" Text='<%# Eval("RecEmail") %>' NavigateUrl='<%# Eval("RecEmail", "mailto:{0}") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="AwardDate" HeaderText="Date" HeaderStyle-Font-Bold="false" />
</Columns>
</asp:GridView>
<div style="float: right; font-size: 10pt; margin-right: 1%;">
Showing Page
<asp:Label ID="lblPageIndex" runat="server" Text="Label" />
of
<asp:Label ID="lblTotalPage" runat="server" />
(<asp:Label ID="lblTotal" runat="server" />
Records)
<div class="dvPager">
<asp:Repeater ID="rptPager" runat="server">
<ItemTemplate>
<asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
OnClick="Page_Changed" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
</ItemTemplate>
</asp:Repeater>
</div>
</div>
<br />
<br />
</div>
</ContentTemplate>
</asp:UpdatePanel>
</div>
<div class="col-sm-10" style="width: 100%; margin: 0 auto; padding: 10px; padding-bottom: 10px;">
<div class="row">
<div class="col-sm-11">
<div class="form-group">
<asp:Label CssClass="element" ID="Label1" runat="server">Subject</asp:Label>
<div class="input-group">
<asp:TextBox ID="TextSubject" runat="server" AutoCompleteType="None" Height="32" CssClass="form-control" placeholder="e.g Document File" />
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-sm-11">
<div class="form-group">
<div class="input-group">
<asp:TextBox ID="txtBody" runat="server" CssClass="form-control" Width="100%" Font-Size="10pt" TextMode="MultiLine" placeholder="e.g. Please download your documents below" Style="overflow: hidden; resize: none;" oninput="Resize(this)" />
<script type="text/javascript">
function Resize(textbox) {
textbox.style.height = "";
textbox.style.height = Math.min(textbox.scrollHeight, 300) + "px";
}
</script>
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-sm-11">
<div class="form-group">
<div class="input-group">
<asp:Button ID="SendBtn" CssClass="btn btn-primary" Text="Send Documents" runat="server" OnClick="SendBulkDocument" />
</div>
</div>
</div>
</div>
</div>
Stored Procedure
CREATE PROCEDURE [dbo].[Recipients]
@CreatedBy VARCHAR(50)
,@PageIndex INT
,@PageSize INT
,@RecordCount INT OUT
AS
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) RowNumber
,Id
,Recipient
,RecEmail
,DocumentData
,AwardDate
,CreatedBy
INTO #Temp
FROM DpcumentTable
WHERE CreatedBy = @CreatedBy ORDER BY Id DESC
SELECT @RecordCount = COUNT(*) FROM #Temp
SELECT * FROM #Temp
WHERE (RowNumber BETWEEN ((@PageIndex-1) * @PageSize) + 1 AND (@PageIndex * @PageSize)) OR @PageIndex = - 1 ORDER BY Id DESC
DROP TABLE #Temp
END
C# Sending documents to each recipient
private void GetRecipient(int pageIndex)
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
try
{
using (SqlCommand cmd = new SqlCommand("Recipients", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CreatedBy", createby.Text.Trim());
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
con.Open();
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
using (SqlDataReader dr = cmd.ExecuteReader())
if (dr.HasRows)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
}
int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
this.PopulatePager(recordCount, pageIndex);
}
}
con.Close();
}
}
catch (SqlException ex)
{
string msg = "Error:";
msg += ex.Message;
throw new Exception(msg);
}
}
}
protected void Page_Changed(object sender, EventArgs e)
{
int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
this.GetRecipient(pageIndex);
}
private void PopulatePager(int recordCount, int currentPage)
{
double dblPageCount = (double)((decimal)recordCount / (decimal)PageSize);
int pageCount = (int)Math.Ceiling(dblPageCount);
List<ListItem> pages = new List<ListItem>();
if (pageCount > 0)
{
if (currentPage != 1)
{
pages.Add(new ListItem("Prev", (currentPage - 1).ToString()));
}
if (pageCount < 4)
{
for (int i = 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
}
else if (currentPage < 4)
{
for (int i = 1; i <= 4; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
pages.Add(new ListItem("...", (currentPage).ToString(), false));
}
else if (currentPage > pageCount - 4)
{
pages.Add(new ListItem("...", (currentPage).ToString(), false));
for (int i = currentPage - 1; i <= pageCount; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
}
else
{
pages.Add(new ListItem("...", (currentPage).ToString(), false));
for (int i = currentPage - 2; i <= currentPage + 2; i++)
{
pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
}
pages.Add(new ListItem("...", (currentPage).ToString(), false));
}
if (currentPage != pageCount)
{
pages.Add(new ListItem("Next", (currentPage + 1).ToString()));
}
}
rptPager.DataSource = pages;
rptPager.DataBind();
lblPageIndex.Text = currentPage.ToString();
lblTotalPage.Text = ((recordCount / PageSize) + ((recordCount % PageSize) > 0 ? 1 : 0)).ToString();
lblTotal.Text = recordCount.ToString();
}
protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
this.GetRecipient(1);
}
protected void SendBulkDocument(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
using (SqlCommand cmd = new SqlCommand("SELECT * FROM DocumentTable WHERE CreatedBy = @CreatedBy", con))
{
cmd.Parameters.AddWithValue("@CreatedBy", createby.Text);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
byte[] DataByte = (byte[])dr["DataCert"];
iTextSharp.text.Image bytes = iTextSharp.text.Image.GetInstance(DataByte);
}
//Create a temporary DataTable
DataTable dtCustomers = new DataTable();
dtCustomers.Columns.AddRange(new DataColumn[2] { new DataColumn("Recipient", typeof(string)),
new DataColumn("RecEmail",typeof(string)) });
//Copy the Checked Rows to DataTable
foreach (GridViewRow row in GridView1.Rows)
{
if ((row.FindControl("CheckSelected") as CheckBox).Checked)
{
dtCustomers.Rows.Add(row.Cells[2].Text, (row.FindControl("emailLink") as HyperLink).Text);
}
}
string subject = TextSubject.Text;
string body = "Dear {0},<br /><br />" + txtBody.Text + "<br /><br />";
//Using Parallel Multi-Threading send multiple bulk email.
Parallel.ForEach(dtCustomers.AsEnumerable(), row =>
{
SendEmail(row["RecEmail"].ToString(), subject, string.Format(body, row["Recipient"]));
});
}
}
}
private bool SendEmail(string recipient, string subject, string body, byte Attachment)
{
MailMessage mm = new MailMessage("sender@gmail.com", recipient);
mm.Subject = subject;
mm.Body = body;
mm.Attachments.Add(new Attachment(new MemoryStream(Attachment), "Document.pdf"));
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.EnableSsl = true;
NetworkCredential NetworkCred = new NetworkCredential();
NetworkCred.UserName = "sender@gmail.com";
NetworkCred.Password = "<password>";
smtp.UseDefaultCredentials = true;
smtp.Credentials = NetworkCred;
smtp.Port = 587;
smtp.Send(mm);
return true;
}
