/// <summary>
/// Establishes a database connection to the Dovetail server.
/// Developer is responsible to close the connection before function exit.
/// </summary>
/// <returns>SqlConnection to 'dovetail-db'</returns>
public static Boolean TestConnection()
{
try
{
SqlConnection connection = DovetailDbConnection.GetConnection();
return(true);
} catch
{
return(false);
}
}
/// <summary>
/// API procedure for removing the specified user into the Dovetail software.
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns>True, if remove successful; false otherwise</returns>
public static bool RemoveUser(DovetailUser user)
{
// Prepare connection to the database
SqlConnection connection = DovetailDbConnection.GetConnection();
bool canRemove = false;
// Attempt to connect to database and verify user credentials
try
{
// Prepare sign-in query
StringBuilder sb = new StringBuilder();
sb.Append("DELETE COUNT(1) ");
sb.Append("FROM Users ");
sb.Append("WHERE Username = @Username AND Password = @Password AND Access = 1;");
string sql = sb.ToString();
SqlCommand command = new SqlCommand(sql, connection)
{
CommandType = CommandType.Text
};
if (string.IsNullOrEmpty(user.Username) || string.IsNullOrEmpty(user.Password))
{
connection.Close();
return(false);
}
command.Parameters.AddWithValue("@Username", user.Username);
command.Parameters.AddWithValue("@Password", user.Password);
// Execute query; only one valid user should be found/returned
int queryResult = Convert.ToInt32(command.ExecuteScalar());
if (queryResult != 1)
{
connection.Close();
return(false);
}
// Sign-in is successful
canRemove = true;
}
catch (SqlException sqle)
{
connection.Close();
MessageBox.Show(sqle.Message);
}
connection.Close();
return(canRemove);
}
/// <summary>
/// API procedure for updating the specified user's privileges to the new user type.
/// </summary>
/// <param name="username"></param>
/// <param name="newUserType"></param>
/// <returns>True, if user type updated successfully; false otherwise</returns>
public static bool UserUpdateUserType(string username, string newUserType)
{
SqlConnection connection = DovetailDbConnection.GetConnection();
bool UserTypeUpdatedSuccessfully = false;
try
{
// Prepare register new user query
StringBuilder sb = new StringBuilder();
sb.Append("UPDATE Users ");
sb.Append("SET UserType = @NewUserType ");
sb.Append("WHERE Username = @Username;");
string sql = sb.ToString();
SqlCommand command = new SqlCommand(sql, connection)
{
CommandType = CommandType.Text
};
if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(newUserType))
{
connection.Close();
return(false);
}
command.Parameters.AddWithValue("@Username", username);
command.Parameters.AddWithValue("@NewUserType", newUserType);
// Execute query; only one valid user should be updated/returned
int result = command.ExecuteNonQuery();
if (result != 1)
{
connection.Close();
return(false);
}
// User's email has been successfully updated
UserTypeUpdatedSuccessfully = true;
}
catch (SqlException sqle)
{
connection.Close();
MessageBox.Show(sqle.Message);
}
connection.Close();
return(UserTypeUpdatedSuccessfully);
}
/// <summary>
/// Check if the specified user already exists in the database.
/// </summary>
/// <param name="username"></param>
/// <returns>True, if user already exists; false otherwise</returns>
private static bool UserAlreadyExists(string username)
{
SqlConnection connection = DovetailDbConnection.GetConnection();
bool userAlreadyExists = false;
try
{
// Prepare register new user query
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM Users ");
sb.Append("WHERE Username = @Username;");
string sql = sb.ToString();
SqlCommand command = new SqlCommand(sql, connection)
{
CommandType = CommandType.Text
};
command.Parameters.AddWithValue("@Username", username);
// Execute query; only one valid user should be found/returned
int queryResult = Convert.ToInt32(command.ExecuteScalar());
if (queryResult > 0)
{
userAlreadyExists = true;
}
}
catch (SqlException sqle)
{
connection.Close();
MessageBox.Show(sqle.Message);
}
connection.Close();
return(userAlreadyExists);
}
/// <summary>
/// API procedure for signing the specified user into the Dovetail software.
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns>True, if sign-in successful; false otherwise</returns>
public static bool UserSignIn(DovetailUser user)
{
// Prepare connection to the database
SqlConnection connection = DovetailDbConnection.GetConnection();
bool canSignIn = false;
//unhashing hashed password
//string savedPasswordHash = user.Password;
//byte[] hashBytes = Convert.FromBase64String(savedPasswordHash);
//byte[] salt = new byte[16];
//Array.Copy(hashBytes,0,salt,0,16);
//var pbkdf2 = new Rfc2898DeriveBytes(user.Password,salt,10000);
//byte[] hash = pbkdft.GetBytes(20);
//bool same = true;
//for(int i = 0; i < 20; i++) {
// if(hashBytes[i+16]!=hash[i])
// same = false;
// }
// Attempt to connect to database and verify user credentials
try
{
// Prepare sign-in query
StringBuilder sb = new StringBuilder();
sb.Append("SELECT COUNT(1) ");
sb.Append("FROM Users ");
sb.Append("WHERE Username = @Username AND Password = @Password AND Access = 1;");
string sql = sb.ToString();
SqlCommand command = new SqlCommand(sql, connection)
{
CommandType = CommandType.Text
};
if (string.IsNullOrEmpty(user.Username) || string.IsNullOrEmpty(user.Password))
{
connection.Close();
return(false);
}
command.Parameters.AddWithValue("@Username", user.Username);
command.Parameters.AddWithValue("@Password", user.Password);
// Execute query; only one valid user should be found/returned
int queryResult = Convert.ToInt32(command.ExecuteScalar());
if (queryResult != 1)
{
connection.Close();
return(false);
}
// Sign-in is successful
canSignIn = true;
}
catch (SqlException sqle)
{
connection.Close();
MessageBox.Show(sqle.Message);
}
connection.Close();
return(canSignIn);
}
/// <summary>
/// API procedure for creating/registering a new user for the Dovetail software.
/// </summary>
/// <param name="user"></param>
/// <returns>True, if success; false otherwise</returns>
public static bool RegisterNewUser(DovetailUser user)
{
// Prepare connection to the database
SqlConnection connection = DovetailDbConnection.GetConnection();
bool canRegisterUser = false;
//byte[] salt;
//new RNGCryptoServiceProvider().GetBytes(salt = new byte[16]);
//var pbkdf2 = new Rfc2898DeriveBytes(user.Password, salt, 10000);
//byte[] hash = pbkdf2.GetBytes(20);
//byte[] hashBytes = new byte[36];
//Array.Copy(salt,0,hashBytes,0,16);
//Array.Copy(hash,0,hashBytes,16,20);
//string savedPasswordHash = Convert.ToBase64String(hashBytes);
//todo store with savedPasswordHash, but everything is g2g
// Attempt to connect to database and verify user credentials
try
{
// Check if user already exists
if (UserAlreadyExists(user.Username))
{
connection.Close();
return(false);
}
// Prepare register new user query
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO Users ");
sb.Append("VALUES (@Username, @Password, @FirstName, @LastName, @Email, @UserType, @Access);");
string sql = sb.ToString();
SqlCommand command = new SqlCommand(sql, connection)
{
CommandType = CommandType.Text
};
if (string.IsNullOrEmpty(user.Username) || string.IsNullOrEmpty(user.Password) ||
string.IsNullOrEmpty(user.FirstName) || string.IsNullOrEmpty(user.LastName) ||
string.IsNullOrEmpty(user.Email))
{
connection.Close();
return(false);
}
if (!IsValidEmail(user.Email))
{
connection.Close();
return(false);
}
command.Parameters.AddWithValue("@Username", user.Username);
command.Parameters.AddWithValue("@Password", user.Password);
command.Parameters.AddWithValue("@FirstName", user.FirstName);
command.Parameters.AddWithValue("@LastName", user.LastName);
command.Parameters.AddWithValue("@Email", user.Email);
command.Parameters.AddWithValue("@UserType", user.UserType);
command.Parameters.AddWithValue("@Access", user.HasAccess);
// Execute query; only one valid user should be created/returned
int result = command.ExecuteNonQuery();
if (result != 1)
{
connection.Close();
return(false);
}
// New user successfully registered
canRegisterUser = true;
}
catch (SqlException sqle)
{
connection.Close();
MessageBox.Show(sqle.Message);
}
connection.Close();
return(canRegisterUser);
}
