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