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