internal bool userExists(string firstName, string lastName)
{
bool exists = false;
cryptography encrypt = new cryptography();
OleDbConnection myDbConnection = new OleDbConnection(connectionString);
OleDbCommand command = new OleDbCommand();
command.CommandType = CommandType.Text;
command.Connection = myDbConnection;
command.CommandText = "SELECT COUNT(*) from myTable where firstName = @p1 AND lastName = @p2";
command.Parameters.AddWithValue("@p1", encrypt.encryptString(firstName));
command.Parameters.AddWithValue("@p2", encrypt.encryptString(lastName));
try
{
myDbConnection.Open();
int result = (int)command.ExecuteScalar();
if (result > 0)
{
exists = true;
}
return(exists);
}
catch (OleDbException ex)
{
DisplayOleDbErrorCollection(ex);
return(exists);
}
finally
{
myDbConnection.Close();
}
}
public bool adminExists()
{
bool result = true;
try
{
cryptography decrypt = new cryptography();
OleDbConnection myDbConnection = new OleDbConnection(connectionString);
OleDbCommand command = new OleDbCommand();
command.CommandType = CommandType.Text;
command.Connection = myDbConnection;
command.CommandText = "SELECT * FROM myTable WHERE permission = p1";
command.Parameters.AddWithValue("p1", decrypt.encryptString("Admin"));
myDbConnection.Open();
OleDbDataReader reader = command.ExecuteReader();
if (!reader.HasRows)
{
result = false; // Admin does not exist
}
reader.Close();
myDbConnection.Close();
return(result);
}
catch (OleDbException ex)
{
DisplayOleDbErrorCollection(ex);
return(result); // So the user is not enrolled as an Admin by default
}
}
internal void setNewFileLoc(int primeKey, string newLocation)
{
OleDbConnection myDbConnection = new OleDbConnection(connectionString);
using (myDbConnection)
{
cryptography encrypt = new cryptography();
string cryptVidLoc = encrypt.encryptString(newLocation);
OleDbCommand command = new OleDbCommand();
command.CommandType = CommandType.Text;
command.Connection = myDbConnection;
command.CommandText = "UPDATE myTable SET videoLink = p1 WHERE PrimaryKey = p2 ";
command.Parameters.AddWithValue("p1", cryptVidLoc);
command.Parameters.AddWithValue("p2", primeKey);
try
{
myDbConnection.Close();
myDbConnection.Open();
command.ExecuteNonQuery();
}
catch (OleDbException ex)
{
DisplayOleDbErrorCollection(ex);
MessageBox.Show("No records were recorded");
}
finally
{
myDbConnection.Close();
}
}
}
public void insertEntry(string fName, string lName, string permissionsLevel, string id, Bitmap imageBmp, NLTemplate template, string vidLoc)
{
OleDbConnection myDbConnection = new OleDbConnection(connectionString);
using (myDbConnection)
{
cryptography encrypt = new cryptography();
string crypFName = encrypt.encryptString(fName);
string cryptLName = encrypt.encryptString(lName);
string cryptAccessLevel = encrypt.encryptString(permissionsLevel);
string cryptUserId = encrypt.encryptString(id);
string cryptVidLoc = encrypt.encryptString(vidLoc);
byte[] cryptImage = encrypt.encryptImage(imageBmp);
byte[] cryptTemplate = encrypt.encryptTemplate(template);
OleDbCommand command = new OleDbCommand();
command.CommandType = CommandType.Text;
command.Connection = myDbConnection;
command.CommandText = "INSERT INTO myTable (firstName, lastName, userId, permission, picture, template, videoLink) Values (@p1,@p2,@p3,@p4,@p5,@p6, @p7)";
command.Parameters.AddWithValue("@P1", crypFName);
command.Parameters.AddWithValue("@p2", cryptLName);
command.Parameters.AddWithValue("@p3", cryptUserId);
command.Parameters.AddWithValue("@p4", cryptAccessLevel);
command.Parameters.AddWithValue("@p5", cryptImage);
command.Parameters.AddWithValue("@p6", cryptTemplate);
command.Parameters.AddWithValue("p7", cryptVidLoc);
try
{
myDbConnection.Open();
command.ExecuteNonQuery();
}
catch (OleDbException ex)
{
DisplayOleDbErrorCollection(ex);
MessageBox.Show("No records were recorded");
}
finally
{
myDbConnection.Close();
}
}
}
internal void updateEntry(int primeKey, string fName, string lName, string accessLevel, string enrolleeId, string videoFileLoc)
{
OleDbConnection myDbConnection = new OleDbConnection(connectionString);
using (myDbConnection)
{
cryptography encrypt = new cryptography();
string crypFName = encrypt.encryptString(fName);
string cryptLName = encrypt.encryptString(lName);
string cryptAccessLevel = encrypt.encryptString(accessLevel);
string cryptUserId = encrypt.encryptString(enrolleeId);
string cryptVidLoc = encrypt.encryptString(videoFileLoc);
OleDbCommand command = new OleDbCommand();
command.CommandType = CommandType.Text;
command.Connection = myDbConnection;
command.CommandText = "UPDATE myTable SET firstName = @p1, lastName = @p2, userId = @p3, permission = @p4, videoLink = @p5 WHERE PrimaryKey = @p6";
command.Parameters.AddWithValue("@P1", crypFName);
command.Parameters.AddWithValue("@p2", cryptLName);
command.Parameters.AddWithValue("@p3", cryptUserId);
command.Parameters.AddWithValue("@p4", cryptAccessLevel);
command.Parameters.AddWithValue("@p5", cryptVidLoc);
command.Parameters.AddWithValue("@p6", primeKey);
try
{
myDbConnection.Open();
command.ExecuteNonQuery();
}
catch (OleDbException ex)
{
DisplayOleDbErrorCollection(ex);
MessageBox.Show("No records were recorded");
}
finally
{
myDbConnection.Close();
}
}
}
internal int getKeyFromName(string userFName, string userLName)
{
int primeKey = 0;
cryptography decrypt = new cryptography();
OleDbConnection myDbConnection = new OleDbConnection(connectionString);
OleDbCommand command = new OleDbCommand();
command.CommandType = CommandType.Text;
command.Connection = myDbConnection;
command.CommandText = "SELECT PrimaryKey from myTable where firstName = p1 AND lastName = p2";
command.Parameters.AddWithValue("p1", decrypt.encryptString(userFName));
command.Parameters.AddWithValue("p2", decrypt.encryptString(userLName));
try
{
myDbConnection.Close();
myDbConnection.Open();
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
primeKey = (int)reader.GetValue(0);
}
reader.Close();
myDbConnection.Close();
return(primeKey);
}
catch (OleDbException ex)
{
DisplayOleDbErrorCollection(ex);
return(primeKey);
}
finally
{
myDbConnection.Close();
}
}
internal Bitmap getImageFromId(string userId)
{
Bitmap dbaseImage = null;
OleDbConnection myDbConnection = new OleDbConnection(connectionString);
try
{
string queryString;
byte[] encryptedImageArray = null;
byte[] decryptedImageArray = null;
cryptography decrypt = new cryptography();
queryString = String.Format("SELECT picture from myTable where userId = '{0}'", decrypt.encryptString(userId));
OleDbCommand command = new OleDbCommand(queryString, myDbConnection);
myDbConnection.Open();
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
encryptedImageArray = (byte[])reader.GetValue(0);
decryptedImageArray = decrypt.decryptBytes(encryptedImageArray);
MemoryStream stream = new MemoryStream(decryptedImageArray);
dbaseImage = (Bitmap)Image.FromStream(stream);
}
reader.Close();
return(dbaseImage);
}
catch (OleDbException ex)
{
DisplayOleDbErrorCollection(ex);
return(dbaseImage);
}
finally
{
myDbConnection.Close();
}
}
internal byte[] getTemplateFromId(string userId)
{
cryptography deCrypt = new cryptography();
OleDbConnection myDbConnection = new OleDbConnection(connectionString);
byte[] encryptedTemplateArray = null;
byte[] templateArray = null;
string queryString = String.Format("SELECT template from myTable where userId = '{0}'", deCrypt.encryptString(userId));
OleDbCommand command = new OleDbCommand(queryString, myDbConnection);
try
{
myDbConnection.Open();
OleDbDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
encryptedTemplateArray = (byte[])reader.GetValue(0);
templateArray = deCrypt.decryptBytes(encryptedTemplateArray);
}
}
reader.Close();
return(templateArray);
}
catch (OleDbException ex)
{
DisplayOleDbErrorCollection(ex);
return(templateArray);
}
finally
{
myDbConnection.Close();
}
}
