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