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);
}
}
}
}
}
