Check with below code.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Net;
using System.Net.Mail;
namespace WebApplication13
{
public partial class WebForm2 : System.Web.UI.Page
{
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 (Session["FirstName"] == null)
Response.Redirect("login1.aspx");
else
{
String EmplooyeId = Convert.ToString((int)Session["empid"]);
String FirstName = Session["FirstName"].ToString();
String LastName = Session["LastName"].ToString();
String Department = Session["Department"].ToString();
lbluserInfo.Text = "Department:- " + Department + ".";
Label1.Text = "Welcome Mr." + FirstName + " " + LastName + " ";
}
if (!this.IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT Id, VEND_EMP, VEN_EMP_NAME, PARTICULARS, AMT, VEN_EMP_PHNO, VEN_EMP_EMAIL,EIC_PH_NO,EIC_MAIL_ID FROM MESSAGE_SYSTEM where flag <> 'Y'"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
}
protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
this.BindGrid();
}
protected void Upload(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + System.IO.Path.GetFileName(FileUpload.PostedFile.FileName);
FileUpload.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload.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[9] { new DataColumn("Id", typeof(int)),
new DataColumn("VEND_EMP", typeof(string)),
new DataColumn("VEN_EMP_NAME", typeof(string)),
new DataColumn("PARTICULARS", typeof (string)),
new DataColumn("AMT", typeof(string)),
new DataColumn("VEN_EMP_PHNO", typeof(string)),
new DataColumn("VEN_EMP_EMAIL", typeof(string)),
new DataColumn("EIC_PH_NO", typeof(string)) ,
new DataColumn("EIC_MAIL_ID", 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.MESSAGE_SYSTEM";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("VEND_EMP", "VEND_EMP");
sqlBulkCopy.ColumnMappings.Add("VEN_EMP_NAME", "VEN_EMP_NAME");
sqlBulkCopy.ColumnMappings.Add("PARTICULARS", "PARTICULARS");
sqlBulkCopy.ColumnMappings.Add("AMT", "AMT");
sqlBulkCopy.ColumnMappings.Add("VEN_EMP_PHNO", "VEN_EMP_PHNO");
sqlBulkCopy.ColumnMappings.Add("VEN_EMP_EMAIL", "VEN_EMP_EMAIL");
sqlBulkCopy.ColumnMappings.Add("EIC_PH_NO", "EIC_PH_NO");
sqlBulkCopy.ColumnMappings.Add("EIC_MAIL_ID", "EIC_MAIL_ID");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
Page.RegisterStartupScript("UserMsg", "<script>alert('Payment Details data uploaded Successfully! ...');if(alert){ window.location='WebForm2.aspx';}</script>");
}
}
}
protected void btnSendMail_Click(object sender, EventArgs e)
{
string Id = string.Empty;
DataTable dt = new DataTable();
try
{
foreach (GridViewRow row in GridView1.Rows)
{
CheckBox cb = (CheckBox)row.FindControl("chkSelect");
if (cb.Checked == true)
{
if (cb != null && cb.Checked)
{
//get Current EMAIL_ID from the DataKey
Id = Convert.ToString(GridView1.DataKeys[row.RowIndex].Value);
SqlCommand cmd = new SqlCommand("select VEN_EMP_EMAIL,EIC_MAIL_ID,AMT,PARTICULARS from MESSAGE_SYSTEM where Id=" + Id + "", con);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
//Fill datatable with EMAIL_ID corresponding to Current EMP_ID
adp.Fill(dt);
//Get EMAIL_ID into variable
string emailId = dt.Rows[0]["VEN_EMP_EMAIL"].ToString();
string Email_id1 = dt.Rows[0]["EIC_MAIL_ID"].ToString();
string AMT = dt.Rows[0]["AMT"].ToString();
string PARTICULARS = dt.Rows[0]["EIC_MAIL_ID"].ToString();
//write code to send mail
SendEmailUsingGmail(emailId, AMT, PARTICULARS);
SendEmailUsingGmail(Email_id1, AMT, PARTICULARS);
dt.Clear();
dt.Dispose();
}
}
}
// ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('Emails sent successfully');", true);
}
catch (Exception ex)
{
Response.Write("Error occured: " + ex.Message.ToString());
}
finally
{
Id = string.Empty;
}
}
//private void SendEmailUsingGmail(string toEmailAddress)
private void SendEmailUsingGmail(string toEmailAddress, string AMT, string PARTICULARS)
{
try
{
SmtpClient smtp = new SmtpClient();
smtp.Credentials = new NetworkCredential("indradeo7306@gmail.com", "7503431478");
smtp.Port = 587;
smtp.Host = "smtp.gmail.com";
smtp.EnableSsl = true;
MailMessage message = new MailMessage();
message.From = new MailAddress("indradeo7306@gmail.com");
message.To.Add(toEmailAddress);
message.Subject = " PAYMENT INFORMATION";
message.Body = "Amount of Rs " + AMT + " for " + PARTICULARS + " is sent to your Bank account";
smtp.Send(message);
}
catch (Exception ex)
{
Response.Write("Error occured: " + ex.Message.ToString());
}
try
{
SmtpClient smtp = new SmtpClient();
Page.RegisterStartupScript("UserMsg", "<script>alert('Payment Details Successfully Send...');if(alert){ window.location='WebForm2.aspx';}</script>");
}
catch (Exception ex)
{
Exception ex2 = ex;
string errorMessage = string.Empty;
while (ex2 != null)
{
errorMessage += ex2.ToString();
ex2 = ex2.InnerException;
}
Page.RegisterStartupScript("UserMsg", "<script>alert('Payment Details Sending Failed...');if(alert){ window.location='WebForm2.aspx';}</script>");
}
}
protected void chkSelectAll_CheckedChanged(object sender, EventArgs e)
{
CheckBox chkAll = (CheckBox)GridView1.HeaderRow.FindControl("chkSelectAll");
foreach (GridViewRow gvRow in GridView1.Rows)
{
CheckBox chkSel = (CheckBox)gvRow.FindControl("chkSelect");
if (
chkAll.Checked)
{
chkSel.Checked = true;
}
else
{
chkSel.Checked = false;
}
}
}
static string sendSMS(string mobileNo, string messageTxt)
{
string SMSurl = "https://82r89.api.infobip.com/sms/1/text/query?username=MUNPLA&password=Ashwini@085531&to=" + mobileNo + "&text=" + messageTxt + "&from=MUNPLA";
WebRequest request = HttpWebRequest.Create(SMSurl);
WebResponse response = request.GetResponse();
string retString = response.ToString();
response.Close();
return retString;
}
protected void LinkButton16_Click(object sender, EventArgs e)
{
Response.Redirect("login1.aspx");
}
}
}
