public Int16 InsertIntoDatabase() { if (this.created == true) { using (SqlConnection connOne = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString.ToString())) { using (SqlCommand cmdOne = new SqlCommand("InsertBlackList", connOne)) { Int16 result = 0; CustomerClass tmpClass = new CustomerClass(); tmpClass = tmpClass.SelectOneCustomer(this.CustomerID); tmpClass.generateEncryptor(this.CustomerName); cmdOne.CommandType = CommandType.StoredProcedure; cmdOne.Parameters.AddWithValue("@endedAt", this.EndedAt); cmdOne.Parameters.AddWithValue("@reason", this.Reason); cmdOne.Parameters.AddWithValue("@customerId", this.CustomerID); cmdOne.Parameters.AddWithValue("@customerName", tmpClass.generateEncryptor(this.CustomerName)); try { connOne.Open(); result = (Int16)cmdOne.ExecuteNonQuery(); if (result != 1) { throw new NullReferenceException(); } result = tmpClass.UpdateCustomerStatus(tmpClass.ID, tmpClass.Email, "blackListedStatus", true); } catch (SqlException err) { Console.WriteLine(err); result = -3; } catch (NullReferenceException) { result = -3; } catch (OverflowException) { result = -2; } catch { result = -1; } finally { connOne.Close(); } return(result); } } } else { return(0); } }
public List <CustomerClass> SelectAllCustomers() { using (SqlConnection connOne = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString.ToString())) { using (SqlConnection connTwo = new SqlConnection(ConfigurationManager.ConnectionStrings["MySecretDB"].ConnectionString.ToString())) { using (SqlDataAdapter cmdOne = new SqlDataAdapter("SelectAllCustomer", connOne)) { connOne.Open(); connTwo.Open(); cmdOne.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet newDataSet = new DataSet(); cmdOne.Fill(newDataSet); List <CustomerClass> custList = new List <CustomerClass>(); int rec_cnt = newDataSet.Tables[0].Rows.Count; for (int i = 0; i < rec_cnt; i++) { DataRow row = newDataSet.Tables[0].Rows[i]; CustomerClass tmpClass = new CustomerClass(); tmpClass.ID = (Guid)row["id"]; tmpClass.Email = (String)row["email"]; tmpClass.deleteDate = (DateTime)row["deleteDate"]; tmpClass.emailVerified = (Boolean)row["emailVerified"]; tmpClass.delete = (Boolean)row["delete"]; tmpClass.blackListed = (Boolean)row["blackListed"]; tmpClass.createdAt = (DateTime)row["createdAt"]; using (SqlCommand cmdTwo = new SqlCommand("SelectOneEncryption", connTwo)) { cmdTwo.CommandType = CommandType.StoredProcedure; cmdTwo.Parameters.AddWithValue("@Identity", tmpClass.Email); using (SqlDataReader reader = cmdTwo.ExecuteReader()) { if (reader.Read()) { tmpClass.salt = (String)reader["salt"]; tmpClass.key = Convert.FromBase64String((String)reader["key"]); tmpClass.iv = Convert.FromBase64String((String)reader["iv"]); tmpClass.created = true; } } } tmpClass.FirstName = tmpClass.generateDecryptor((String)row["firstName"]); tmpClass.LastName = tmpClass.generateDecryptor((String)row["lastName"]); tmpClass.PhoneNumber = tmpClass.generateDecryptor((String)row["phoneNumber"]); tmpClass.DateOfBirth = DateTime.Parse(tmpClass.generateDecryptor((String)row["dateOfBirth"])); tmpClass.salt = ""; tmpClass.iv = new byte[0]; tmpClass.key = new byte[0]; custList.Add(tmpClass); } connOne.Close(); connTwo.Close(); return(custList); } } } }
public Int16 UpdateCustomer(Guid ID, String PastEmail, String firstName, String lastName, String email, String PhoneNumber, DateTime dateOfBirth) { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString.ToString())) { Int16 result = 0; CustomerClass tmpClass = new CustomerClass(); tmpClass = tmpClass.SelectOneCustomer(PastEmail); using (SqlCommand cmd = new SqlCommand("UpdateCustomerParticulars", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", ID); cmd.Parameters.AddWithValue("@PastEmail", PastEmail); cmd.Parameters.AddWithValue("@FirstName", tmpClass.generateEncryptor(firstName)); cmd.Parameters.AddWithValue("@LastName", tmpClass.generateEncryptor(lastName)); cmd.Parameters.AddWithValue("@NewEmail", email); cmd.Parameters.AddWithValue("@PhoneNumber", tmpClass.generateEncryptor(PhoneNumber)); cmd.Parameters.AddWithValue("@BirthDate", tmpClass.generateEncryptor(dateOfBirth.ToString())); try { conn.Open(); BlackListClass tmpPower = new BlackListClass(); result = tmpPower.UpdateBlacklistEmails(PastEmail, email); if (result < 0) { throw new OverflowException(); } result = (Int16)cmd.ExecuteNonQuery(); if (result != 1) { throw new OverflowException(); } if (email != PastEmail) { result = UpdateCustomerStatus(ID, PastEmail, "emailStatus", false); } } catch (SqlException err) { Console.WriteLine(err); result = -3; } catch (OverflowException err) { result = -2; } catch { result = -1; } finally { conn.Close(); } return(result); } } }
public BlackListClass SelectOneBlacklist(Guid ID, String customerId) { using (SqlConnection connOne = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString.ToString())) { using (SqlCommand cmdOne = new SqlCommand("SelectOneBlackList", connOne)) { BlackListClass tmpClass = new BlackListClass(); cmdOne.CommandType = CommandType.StoredProcedure; cmdOne.Parameters.AddWithValue("@ID", ID); cmdOne.Parameters.AddWithValue("@customerId", customerId); CustomerClass powerClass = new CustomerClass(); powerClass = powerClass.SelectOneCustomer(customerId); try { connOne.Open(); using (SqlDataReader reader = cmdOne.ExecuteReader()) { if (reader.Read()) { tmpClass.CreatedAt = (DateTime)reader["createdAt"]; tmpClass.EndedAt = (DateTime)reader["endedAt"]; tmpClass.Reason = (String)reader["reason"]; tmpClass.CustomerID = (String)reader["customerId"]; tmpClass.CustomerName = powerClass.generateDecryptor((String)reader["customerName"]); tmpClass.Deleted = (Boolean)reader["deleted"]; } } } catch (Exception err) { Console.WriteLine(err); return(null); } finally { connOne.Close(); } return(tmpClass); } } }
public Int16 UpdateCustomerPassword(Guid ID, String PastEmail, String Password) { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString.ToString())) { Int16 result = 0; CustomerClass tmpClass = new CustomerClass(); tmpClass = tmpClass.SelectOneCustomer(PastEmail); using (SqlCommand cmd = new SqlCommand("UpdateCustomerPassword", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", ID); cmd.Parameters.AddWithValue("@PastEmail", PastEmail); cmd.Parameters.AddWithValue("@ValueOne", tmpClass.updateHashPassword(Password)); try { conn.Open(); result = (Int16)cmd.ExecuteNonQuery(); } catch (SqlException err) { Console.WriteLine(err); result = -3; } catch (OverflowException) { result = -2; } catch { result = -1; } finally { conn.Close(); } return(result); } } }
public List <BlackListClass> SelectAllBlacklist(String customerId) { using (SqlConnection connOne = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString.ToString())) { using (SqlConnection connTwo = new SqlConnection(ConfigurationManager.ConnectionStrings["MySecretDB"].ConnectionString.ToString())) { using (SqlDataAdapter cmdOne = new SqlDataAdapter("SelectAllBlackList", connOne)) { connOne.Open(); connTwo.Open(); cmdOne.SelectCommand.CommandType = CommandType.StoredProcedure; cmdOne.SelectCommand.Parameters.AddWithValue("@customerId", customerId); DataSet newDataSet = new DataSet(); cmdOne.Fill(newDataSet); List <BlackListClass> blacklistList = new List <BlackListClass>(); int rec_cnt = newDataSet.Tables[0].Rows.Count; CustomerClass powerClass = new CustomerClass(); powerClass = powerClass.SelectOneCustomer(customerId); for (int i = 0; i < rec_cnt; i++) { DataRow row = newDataSet.Tables[0].Rows[i]; BlackListClass tmpClass = new BlackListClass(); tmpClass.ID = (Guid)row["ID"]; tmpClass.CreatedAt = (DateTime)row["createdAt"]; tmpClass.EndedAt = (DateTime)row["endedAt"]; tmpClass.Reason = (String)row["reason"]; tmpClass.CustomerID = (String)row["customerId"]; tmpClass.CustomerName = powerClass.generateDecryptor((String)row["customerName"]); tmpClass.Deleted = (Boolean)row["deleted"]; blacklistList.Add(tmpClass); } connOne.Close(); connTwo.Close(); return(blacklistList); } } } }
public Int16 InsertIntoDatabase() { if (this.created == true) { using (SqlConnection connOne = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString)) { using (SqlConnection connTwo = new SqlConnection(ConfigurationManager.ConnectionStrings["MySecretDB"].ConnectionString.ToString())) { using (SqlCommand cmdOne = new SqlCommand("InsertCustomer", connOne)) { using (SqlCommand cmdTwo = new SqlCommand("InsertEncryption", connTwo)) { Int16 result = 0; cmdOne.CommandType = CommandType.StoredProcedure; cmdTwo.CommandType = CommandType.StoredProcedure; cmdOne.Parameters.AddWithValue("@FirstName", generateEncryptor(this.FirstName)); cmdOne.Parameters.AddWithValue("@LastName", generateEncryptor(this.LastName)); cmdOne.Parameters.AddWithValue("@PhoneNumber", generateEncryptor(this.PhoneNumber)); cmdOne.Parameters.AddWithValue("@DateOfBirth", generateEncryptor(this.DateOfBirth.ToString())); cmdOne.Parameters.AddWithValue("@Email", this.Email); cmdOne.Parameters.AddWithValue("@Password", this.Password); cmdTwo.Parameters.AddWithValue("@Salt", this.salt); cmdTwo.Parameters.AddWithValue("@Key", Convert.ToBase64String(this.key)); cmdTwo.Parameters.AddWithValue("@Iv", Convert.ToBase64String(this.iv)); cmdTwo.Parameters.AddWithValue("@Identity", this.Email); try { connOne.Open(); connTwo.Open(); result = (Int16)cmdOne.ExecuteNonQuery(); if (result < 0) { throw new OverflowException(); } result = (Int16)cmdTwo.ExecuteNonQuery(); if (result < 0) { CustomerClass tmpClass = SelectOneCustomer(this.Email); FullDeleteCustomer(tmpClass.ID, tmpClass.Email, DateTime.Now.AddDays(-30)); throw new OverflowException(); } } catch (SqlException err) { Console.WriteLine(err); result = -3; } catch (OverflowException) { result = -2; } catch { result = -1; } finally { connOne.Close(); connTwo.Close(); } return(result); } } } } } else { return(0); } }
public CustomerClass SelectOneCustomer(String Email) { using (SqlConnection connOne = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString.ToString())) { using (SqlConnection connTwo = new SqlConnection(ConfigurationManager.ConnectionStrings["MySecretDB"].ConnectionString.ToString())) { using (SqlCommand cmdOne = new SqlCommand("SelectOneCustomer", connOne)) { using (SqlCommand cmdTwo = new SqlCommand("SelectOneEncryption", connTwo)) { CustomerClass tmpClass = new CustomerClass(); cmdOne.CommandType = CommandType.StoredProcedure; cmdTwo.CommandType = CommandType.StoredProcedure; cmdOne.Parameters.AddWithValue("@Email", Email); cmdTwo.Parameters.AddWithValue("@Identity", Email); try { connOne.Open(); connTwo.Open(); String tmpFirstName = ""; String tmpLastName = ""; String tmpPhoneNumber = ""; String tmpDateOfBirth = ""; using (SqlDataReader reader = cmdOne.ExecuteReader()) { if (reader.Read()) { tmpClass.ID = (Guid)reader["id"]; tmpClass.Email = (String)reader["email"]; tmpClass.emailVerified = (Boolean)reader["emailVerified"]; tmpClass.deleteDate = (DateTime)reader["deleteDate"]; tmpClass.delete = (Boolean)reader["delete"]; tmpClass.blackListed = (Boolean)reader["blackListed"]; tmpClass.createdAt = (DateTime)reader["createdAt"]; tmpFirstName = (String)reader["firstName"]; tmpLastName = (String)reader["lastName"]; tmpPhoneNumber = (String)reader["phoneNumber"]; tmpDateOfBirth = (String)reader["dateOfBirth"]; } } using (SqlDataReader reader = cmdTwo.ExecuteReader()) { if (reader.Read()) { tmpClass.salt = (String)reader["salt"]; tmpClass.key = Convert.FromBase64String((String)reader["key"]); tmpClass.iv = Convert.FromBase64String((String)reader["iv"]); tmpClass.FirstName = tmpClass.generateDecryptor(tmpFirstName); tmpClass.LastName = tmpClass.generateDecryptor(tmpLastName); tmpClass.PhoneNumber = tmpClass.generateDecryptor(tmpPhoneNumber); tmpClass.DateOfBirth = DateTime.Parse(tmpClass.generateDecryptor(tmpDateOfBirth)); tmpClass.created = true; } } } catch (Exception err) { Console.WriteLine(err); return(null); } finally { connOne.Close(); connTwo.Close(); } return(tmpClass); } } } } }
public Int16 UpdateCustomerStatus(Guid ID, String PastEmail, String purpose, Boolean status) { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString.ToString())) { Int16 result = 0; CustomerClass tmpClass = new CustomerClass(); if (purpose == "deleteStatus") { using (SqlCommand cmd = new SqlCommand("UpdateCustomerDeleteStatus", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", ID); cmd.Parameters.AddWithValue("@PastEmail", PastEmail); cmd.Parameters.AddWithValue("@ValueOne", status); try { conn.Open(); result = (Int16)cmd.ExecuteNonQuery(); } catch (SqlException err) { Console.WriteLine(err); result = -3; } catch (OverflowException) { result = -2; } catch { result = -1; } finally { conn.Close(); } return(result); } } else if (purpose == "emailStatus") { using (SqlCommand cmd = new SqlCommand("UpdateCustomerEmailVerified", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", ID); cmd.Parameters.AddWithValue("@PastEmail", PastEmail); cmd.Parameters.AddWithValue("@ValueOne", status); try { conn.Open(); result = (Int16)cmd.ExecuteNonQuery(); } catch (SqlException err) { Console.WriteLine(err); result = -3; } catch (OverflowException) { result = -2; } catch { result = -1; } finally { conn.Close(); } return(result); } } else if (purpose == "blackListedStatus") { using (SqlCommand cmd = new SqlCommand("UpdateCustomerBlackListed", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", ID); cmd.Parameters.AddWithValue("@PastEmail", PastEmail); cmd.Parameters.AddWithValue("@ValueOne", status); try { conn.Open(); result = (Int16)cmd.ExecuteNonQuery(); } catch (SqlException err) { Console.WriteLine(err); result = -3; } catch (OverflowException) { result = -2; } catch { result = -1; } finally { conn.Close(); } return(result); } } return(result); } }
public CustomerClass VerifyUser(String emailVal) { using (SqlConnection connOne = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString.ToString())) { using (SqlConnection connTwo = new SqlConnection(ConfigurationManager.ConnectionStrings["MySecretDB"].ConnectionString.ToString())) { using (SqlCommand cmdOne = new SqlCommand("SelectOneCustomerVerification", connOne)) { using (SqlCommand cmdTwo = new SqlCommand("SelectOneEncryption", connTwo)) { CustomerClass tmpClass = new CustomerClass(); cmdOne.CommandType = CommandType.StoredProcedure; cmdTwo.CommandType = CommandType.StoredProcedure; cmdOne.Parameters.AddWithValue("@Email", emailVal); cmdTwo.Parameters.AddWithValue("@Identity", emailVal); try { connOne.Open(); using (SqlDataReader reader = cmdOne.ExecuteReader()) { if (reader.Read()) { tmpClass.ID = (Guid)reader["id"]; tmpClass.Email = (String)reader["email"]; tmpClass.emailVerified = (Boolean)reader["emailVerified"]; tmpClass.Password = (String)reader["password"]; tmpClass.delete = (Boolean)reader["delete"]; tmpClass.deleteDate = (DateTime)reader["deleteDate"]; tmpClass.blackListed = (Boolean)reader["blackListed"]; } } connTwo.Open(); using (SqlDataReader reader = cmdTwo.ExecuteReader()) { if (reader.Read()) { tmpClass.salt = (String)reader["salt"]; tmpClass.key = Convert.FromBase64String((String)reader["key"]); tmpClass.iv = Convert.FromBase64String((String)reader["iv"]); tmpClass.created = true; } } } catch (SqlException err) { return(null); } catch { return(null); } finally { connOne.Close(); connTwo.Close(); } return(tmpClass); } } } } }