I am trying to insert and save into two tables and at the same time send to user email. I want to add new user by inserting user details into the database. I have two tables. Some data will go into one table and some will go into another table. For example, Employee ID, email and Name will go into handyTable, while MailTable will have Employee ID, email, password, Name and phone Number. The password is encrypted and then decrypted to send to recipient email. On button click, these data are inserted into the two tables and an email is sent to the recipient as well in the same button click.
I followed this thread
But there is a slight difference with mine, because in my case the user’s email and password does not exist in the database yet. So it has to insert the details and at the same time send them to new user's email
I want to please know if I did the correct coding on this.
Here is what I tried
HTML
<div>
<br />
<asp:Label ID="LblID" runat="server" Text="ID"></asp:Label>
<asp:TextBox ID="txtID" runat="server" CssClass="form-control" Font-Size="10pt"></asp:TextBox>
<br />
<asp:Label ID="Label2" runat="server" Text="Email"></asp:Label>
<asp:TextBox ID="mailtxt" runat="server" CssClass="form-control" Font-Size="10pt"></asp:TextBox>
<br />
<asp:Label ID="Label3" runat="server" Text="Password"></asp:Label>
<asp:TextBox ID="txtpass" runat="server" TextMode="Password" CssClass="form-control" Font-Size="10pt" placeholder="Login Password"></asp:TextBox>
<div class="input-group-append">
<span class="input-group-text" style="background-color: #fff; color: #2d6193; border-bottom: 1px solid #d9dcdd; border-top: 1px solid #d9dcdd;">
<span id="toggle_pw" class="fa fa-fw fa-eye field_icon" style="cursor: pointer;"></span>
</span>
</div>
<asp:RegularExpressionValidator ID="Regexpress" runat="server" Font-Size="8pt" ErrorMessage="Minimum of 8 characters [UpperCase, LowerCase, Number and Special Character]" ValidationExpression="(?=^.{8,}$)(?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%^&*()_+}{":;'?/>.<,])(?!.*\s).*$" ControlToValidate="txtpass" ForeColor="Red"></asp:RegularExpressionValidator>
<br />
<asp:Label ID="Label4" runat="server" Text="Name"></asp:Label>
<asp:TextBox ID="Named" runat="server" CssClass="form-control" Font-Size="10pt"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="Test Try Out" OnClick="Button1_Click" />
<br />
<div id="dvMessage" runat="server" visible="false" class="alert alert-danger" style="font-size: 10pt;">
<asp:Label ID="lblMessage" runat="server" />
</div>
<div id="Div1" runat="server" visible="false" class="alert alert-success">
<asp:Label ID="lblsuccess" runat="server" />
</div>
</div>
protected void Button1_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtID.Text) & !string.IsNullOrEmpty(mailtxt.Text) & !string.IsNullOrEmpty(Named.Text) & !string.IsNullOrEmpty(txtpass.Text))
{
using (SqlConnection con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename =| DataDirectory |\\Dataregister.mdf; Integrated Security = True"))
{
//Data inserted into first table.
using (SqlCommand cmd = new SqlCommand("INSERT INTO handyTable (id, email, Named) VALUES(@id, @email, @Named)"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@id", txtID.Text.Trim());
cmd.Parameters.AddWithValue("@email", mailtxt.Text.Trim());
cmd.Parameters.AddWithValue("@Named", Named.Text.Trim());
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
}
con.Close();
}
//Data inserted into second table here.
using (SqlCommand cmd = new SqlCommand("INSERT INTO MailTable (id, named, pass, email) VALUES(@id, @named, @pass, @email)"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@id", txtID.Text.Trim());
cmd.Parameters.AddWithValue("@named", Named.Text.Trim());
cmd.Parameters.AddWithValue("@pass", Encrypt(txtpass.Text.Trim()));
cmd.Parameters.AddWithValue("@email", mailtxt.Text.Trim());
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
}
con.Close();
}
//This is where I call the Send mail function to send the details to recipient
SendMailToEmp();
}
Response.Redirect(Request.Url.AbsoluteUri);
Div1.Visible = true;
dvMessage.Visible = false;
lblMessage.Visible = false;
lblsuccess.Visible = true;
lblsuccess.ForeColor = System.Drawing.Color.Red;
lblsuccess.Text = "Profile Data Uploaded Successfully";
}
else
{
dvMessage.Visible = true;
lblMessage.Visible = true;
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Text = "*All Fields Are Required*";
lblsuccess.Visible = false;
Div1.Visible = false;
}
}
private void SendMailToEmp()
{
//This function is where the email will be sent.
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename =| DataDirectory |\\Dataregister.mdf; Integrated Security = True"))
{
con.Open();
SqlCommand cmd = new SqlCommand("SELECT email, pass FROM MailTable Where email= '" + mailtxt.Text.Trim() + "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
//This is where I password is decrypted to be sent to recipient
string password = Decrypt(ds.Tables[0].Rows[0]["pass"].ToString());
MailMessage Msg = new MailMessage();
Msg.From = new MailAddress("richard@gmail.com");
Msg.To.Add(mailtxt.Text);
Msg.Subject = "Password Details";
Msg.Body = "Hi, User <br/>Here are your login details<br/><br/>Username: " + ds.Tables[0].Rows[0]["email"] + "<br/><br/>Password: " + password + "<br/><br/>";
Msg.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "mySMTPHost";
smtp.Port = 587;
smtp.Credentials = new System.Net.NetworkCredential("richard@gmail.com", "*************");
smtp.EnableSsl = true;
smtp.Send(Msg);
Msg = null;
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Login Details has been Sent to recipient')", true);
mailtxt.Text = "";
}
else
{
Response.Write("<script>alert('Email Id you entered does not exist');</script>");
}
}
}
private string Encrypt(string clearText)
{
string EncryptionKey = "MAKV2SPBNI99212";
byte[] clearBytes = Encoding.Unicode.GetBytes(clearText);
using (Aes encryptor = Aes.Create())
{
Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
using (MemoryStream ms = new MemoryStream())
{
using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateEncryptor(), CryptoStreamMode.Write))
{
cs.Write(clearBytes, 0, clearBytes.Length);
cs.Close();
}
clearText = Convert.ToBase64String(ms.ToArray());
}
}
return clearText;
}
private string Decrypt(string cipherText)
{
string EncryptionKey = "MAKV2SPBNI99212";
byte[] cipherBytes = Convert.FromBase64String(cipherText);
using (Aes encryptor = Aes.Create())
{
Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(EncryptionKey, new byte[] { 0x49, 0x76, 0x61, 0x6e, 0x20, 0x4d, 0x65, 0x64, 0x76, 0x65, 0x64, 0x65, 0x76 });
encryptor.Key = pdb.GetBytes(32);
encryptor.IV = pdb.GetBytes(16);
using (MemoryStream ms = new MemoryStream())
{
using (CryptoStream cs = new CryptoStream(ms, encryptor.CreateDecryptor(), CryptoStreamMode.Write))
{
cs.Write(cipherBytes, 0, cipherBytes.Length);
cs.Close();
}
cipherText = Encoding.Unicode.GetString(ms.ToArray());
}
}
return cipherText;
}
