Hi indradeo,
I have modified your code. please check it.
Code
C#
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("login.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, " + FirstName + " " + LastName + ".";
}
if (!Page.IsPostBack)
{
BindEmpGrid();
}
}
protected void BindEmpGrid()
{
SqlCommand cmd = new SqlCommand("select * from email", con);
DataTable dt = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
grEmp.DataSource = dt;
grEmp.DataBind();
}
protected void Upload(object sender, EventArgs e)
{
//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 btnSendMail_Click(object sender, EventArgs e)
{
string Id = string.Empty;
DataTable dt = new DataTable();
try
{
foreach (GridViewRow row in grEmp.Rows)
{
//get Current EMAIL_ID from the DataKey
Id = Convert.ToString(grEmp.DataKeys[row.RowIndex].Value);
CheckBox cb = (CheckBox)row.FindControl("chkSelect");
string flag = cb.Checked ? "Y" : "N";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
SqlCommand cmd = new SqlCommand("UPDATE email SET flag = @flag where Id=@Id");
cmd.Connection = con;
cmd.Parameters.AddWithValue("@flag", flag);
cmd.Parameters.AddWithValue("@Id", Id);
con.Open();
string isUpdated = Convert.ToString(cmd.ExecuteNonQuery());
con.Close();
}
if (cb.Checked)
{
SqlCommand cmd = new SqlCommand("select email, txn_desc, amt, phone from email 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]["email"].ToString();
string txn_desc = dt.Rows[0]["txn_desc"].ToString();
string amt = dt.Rows[0]["amt"].ToString();
string phoneNo = dt.Rows[0]["phone"].ToString();
//write code to send mail
SendEmailUsingGmail(emailId, txn_desc, amt);
sendSMS(phoneNo.Trim(), "Amount of Rs " + amt + " for " + txn_desc + "is sent to your Bank account");
dt.Clear();
dt.Dispose();
}
}
ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), Guid.NewGuid().ToString(), "alert('Payment Details Sent Successfully');", true);
}
catch (Exception ex)
{
Response.Write("Error occured: " + ex.Message.ToString());
}
finally
{
Id = string.Empty;
}
}
private void SendEmailUsingGmail(string toEmailAddress, string txndesc, string amount)
{
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 " + amount + " for " + txndesc + "is sent to your Bank account";
smtp.Send(message);
}
catch (Exception ex)
{
Response.Write("Error occured: " + ex.Message.ToString());
}
}
protected void chkSelectAll_CheckedChanged(object sender, EventArgs e)
{
CheckBox chkAll = (CheckBox)grEmp.HeaderRow.FindControl("chkSelectAll");
foreach (GridViewRow gvRow in grEmp.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 = "gateway";
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");
}
