Hi indradeo,
Refer modified code.
HTML
<div class="auto-style13">
<font color="black" size="1">
<div class="auto-style3">
<b><font face="Arial" size="4"><font color="black" size="6">
PAYMENT INFORMATION </font>
<br />
<br />
<font color="black">
Upload Payment information File in Excel format</font><br></br>
</font></b>
<br />
<br />
</div>
<table class="auto-style1">
<tr>
<td class="auto-style2">
<font color="black" size="1"><strong><font size="3">Select File</font></strong>
</td>
<td class="auto-style11">
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
<br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" ErrorMessage="Please choose a file."
Style="color: red; visibility: visible;" ControlToValidate="FileUpload1" runat="server"
Display="Dynamic" ForeColor="red" Font-Size="20px" />
</td>
</tr>
<tr>
<td class="auto-style4">
</td>
<td class="auto-style3">
</td>
</tr>
<tr>
<td class="auto-style5">
</td>
<td class="auto-style6">
<asp:Button Text="Save" OnClick="Upload" runat="server" ID="Button1" Height="20px"
Style="text-align: center" Width="116px" />
<br />
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" ValidationExpression="([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$"
ControlToValidate="FileUpload1" runat="server" ForeColor="red" Font-Size="20px"
ErrorMessage="Please select Either .xls or .xlsx File with correct format."
Display="Dynamic" />
<br />
<asp:Label ID="lblMessage" runat="server" Text=""></asp:Label>
</td>
</tr>
</table>
<p>
</p>
<table class="auto-style1">
<tr>
<td style="font-weight: 700;" class="auto-style3">
<div class="auto-style3">
<br />
<font size="3"><font color="black" size="1" align="center">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
ForeColor="#333333" GridLines="None" CssClass="auto-style14" Height="120px" Width="887px">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField DataField="vn_emp_cd" HeaderText="VN_EMP_CD" />
<asp:BoundField DataField="ven_emp_name" HeaderText="VEN_EMP_NAME" />
<asp:BoundField DataField="txn_desc" HeaderText="TXN_DESC" />
<asp:BoundField DataField="amt" HeaderText="AMOUNT" />
<asp:BoundField DataField="phone" HeaderText="PHONE_NO" />
<asp:BoundField DataField="email" HeaderText="EMAIL_ID" />
<asp:TemplateField HeaderText="Select All">
<HeaderTemplate>
Select All
<asp:CheckBox ID="CheckBox1" AutoPostBack="true" OnCheckedChanged="chckchanged" runat="server" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="CheckBox2" runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<SortedAscendingCellStyle BackColor="#FDF5AC" />
<SortedAscendingHeaderStyle BackColor="#4D0000" />
<SortedDescendingCellStyle BackColor="#FCF6C0" />
<SortedDescendingHeaderStyle BackColor="#820000" />
</asp:GridView>
</font></font>
<br />
<br />
</div>
</td>
</tr>
<tr>
<td style="font-weight: 700;" class="auto-style3">
<asp:Button ID="btnSendEmail" runat="server" Text="Email Send" CausesValidation="false"
OnClick="SendBulkEmail" />
<asp:Button ID="Button3" runat="server" Text="SMS Send" CausesValidation="false" />
</td>
</tr>
</table>
</font>
</div>
Code
SqlConnection con = new SqlConnection(@"Data Source=(localdb)\Projects;Initial Catalog=Employee;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False");
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
refreshdata();
}
}
public void refreshdata()
{
SqlCommand cmd = new SqlCommand("select * from email", con);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void chckchanged(object sender, EventArgs e)
{
CheckBox chckheader = (CheckBox)GridView1.HeaderRow.FindControl("CheckBox1");
foreach (GridViewRow row in GridView1.Rows)
{
CheckBox chckrw = (CheckBox)row.FindControl("CheckBox2");
if (chckheader.Checked == true)
{
chckrw.Checked = true;
}
else
{
chckrw.Checked = false;
}
}
}
protected void Upload(object sender, EventArgs e)
{
//Create a temporary DataTable
DataTable dtCustomers = new DataTable();
dtCustomers.Columns.AddRange(new DataColumn[2] { new DataColumn("Name", typeof(string)),
new DataColumn("Email",typeof(string)) });
//Copy the Checked Rows to DataTable
foreach (GridViewRow row in GridView1.Rows)
{
if ((row.FindControl("chkSelect") as CheckBox).Checked)
{
dtCustomers.Rows.Add(row.Cells[2].Text, (row.FindControl("lnkEmail") as HyperLink).Text);
}
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[7] { new DataColumn("Id", typeof(int)),
new DataColumn("vn_emp_cd", typeof(string)),
new DataColumn("ven_emp_name", typeof(string)),
new DataColumn("txn_desc", typeof (string)),
new DataColumn("amt", typeof(string)),
new DataColumn("phone", typeof(string)),
new DataColumn("email", typeof(string)) });
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.email";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("vn_emp_cd", "vn_emp_cd");
sqlBulkCopy.ColumnMappings.Add("ven_emp_name", "ven_emp_name");
sqlBulkCopy.ColumnMappings.Add("txn_desc", "txn_desc");
sqlBulkCopy.ColumnMappings.Add("amt", "amt");
sqlBulkCopy.ColumnMappings.Add("phone", "phone");
sqlBulkCopy.ColumnMappings.Add("email", "email");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
lblMessage.Font.Size = FontUnit.Large;
lblMessage.ForeColor = System.Drawing.Color.Green;
lblMessage.Text = Path.GetFileName(FileUpload1.FileName) + " has been Saved successfully.";
}
}
}
}
protected void Button3_Click(object sender, EventArgs e)
{
}
protected void SendBulkEmail(object sender, EventArgs e)
{
//Create a temporary DataTable
DataTable dtCustomers = new DataTable();
dtCustomers.Columns.AddRange(new DataColumn[2] { new DataColumn("Name", typeof(string)),
new DataColumn("Email",typeof(string)) });
//Copy the Checked Rows to DataTable
foreach (GridViewRow row in GridView1.Rows)
{
if ((row.FindControl("CheckBox2") as CheckBox).Checked)
{
dtCustomers.Rows.Add(row.Cells[1].Text, row.Cells[5].Text);
}
}
string subject = "Welcome Email";
string body = "Hello {0},<br /><br />Welcome to MTTP<br /><br />Thanks.";
//Using Parallel Multi-Threading send multiple bulk email.
Parallel.ForEach(dtCustomers.AsEnumerable(), row =>
{
SendEmail(row["Email"].ToString(), subject, string.Format(body, row["Name"]));
});
}
private bool SendEmail(string recipient, string subject, string body)
{
MailMessage mm = new MailMessage("indradeo7306@gmail.com", recipient);
mm.Subject = subject;
mm.Body = body;
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.EnableSsl = true;
NetworkCredential NetworkCred = new NetworkCredential();
NetworkCred.UserName = "indradeo7306@gmail.com";
NetworkCred.Password = "7503431478";
smtp.UseDefaultCredentials = true;
smtp.Credentials = NetworkCred;
smtp.Port = 587;
smtp.Send(mm);
return true;
}
