예제 #1
0
 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);
     }
 }
예제 #2
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);
             }
         }
     }
 }
예제 #3
0
 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);
         }
     }
 }
예제 #4
0
 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);
         }
     }
 }
예제 #5
0
 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);
         }
     }
 }
예제 #6
0
 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);
             }
         }
     }
 }
예제 #7
0
 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);
     }
 }
예제 #8
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);
                 }
             }
         }
     }
 }
예제 #9
0
 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);
     }
 }
예제 #10
0
 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);
                 }
             }
         }
     }
 }