public List <AdminClass> SelectAllAdmin() { 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("SelectAllAdmin", connOne)) { connOne.Open(); connTwo.Open(); cmdOne.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet newDataSet = new DataSet(); cmdOne.Fill(newDataSet); List <AdminClass> adminList = new List <AdminClass>(); int rec_cnt = newDataSet.Tables[0].Rows.Count; for (int i = 0; i < rec_cnt; i++) { DataRow row = newDataSet.Tables[0].Rows[i]; AdminClass tmpClass = new AdminClass(); tmpClass.ID = (Guid)row["ID"]; tmpClass.UserName = (String)row["userName"]; tmpClass.CreatedAt = (DateTime)row["createdAt"]; using (SqlCommand cmdTwo = new SqlCommand("SelectOneEncryption", connTwo)) { cmdTwo.CommandType = CommandType.StoredProcedure; cmdTwo.Parameters.AddWithValue("@Identity", tmpClass.UserName); 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.AdminName = tmpClass.generateDecryptor((String)row["adminName"]); tmpClass.Role = tmpClass.generateDecryptor((String)row["role"]); tmpClass.salt = ""; tmpClass.iv = new byte[0]; tmpClass.key = new byte[0]; adminList.Add(tmpClass); } connOne.Close(); connTwo.Close(); return(adminList); } } } }
public Int16 UpdateAdminRole(String Username, String Role) { using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString.ToString())) { Int16 result = 0; AdminClass tmpClass = new AdminClass(); tmpClass = tmpClass.SelectOneAdmin(Username); using (SqlCommand cmd = new SqlCommand("UpdateAdminRole", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserName", Username); cmd.Parameters.AddWithValue("@Role", tmpClass.generateEncryptor(Role)); 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 AdminClass SelectOneAdmin(String userName) { 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("SelectOneAdmin", connOne)) { using (SqlCommand cmdTwo = new SqlCommand("SelectOneEncryption", connTwo)) { AdminClass tmpClass = new AdminClass(); cmdOne.CommandType = CommandType.StoredProcedure; cmdTwo.CommandType = CommandType.StoredProcedure; cmdOne.Parameters.AddWithValue("@UserName", userName); cmdTwo.Parameters.AddWithValue("@Identity", userName); try { connOne.Open(); using (SqlDataReader reader = cmdOne.ExecuteReader()) { if (reader.Read()) { tmpClass.ID = (Guid)reader["ID"]; tmpClass.AdminName = (String)reader["adminName"]; tmpClass.UserName = (String)reader["userName"]; tmpClass.Password = (String)reader["password"]; tmpClass.Role = (String)reader["role"]; } } 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"]); String One = tmpClass.generateEncryptor(tmpClass.AdminName); String Two = tmpClass.generateEncryptor(tmpClass.Role); tmpClass.AdminName = tmpClass.generateDecryptor(tmpClass.AdminName); tmpClass.Role = tmpClass.generateDecryptor(tmpClass.Role); tmpClass.created = true; } } } catch (SqlException err) { return(null); } catch (Exception err) { Console.WriteLine(err); return(null); } finally { connOne.Close(); connTwo.Close(); } return(tmpClass); } } } } }