public static bool SaveQuestion(Question question)
{
try
{
SqlConnection conn = new SqlConnection(DatabaseConstants.GetServer() + DatabaseConstants.MSSQLConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoredProcedures.QuestionSave;
cmd.Parameters.AddWithValue("ID", question.ID);
cmd.Parameters.AddWithValue("Question", question.QuestionText);
cmd.Parameters.AddWithValue("OptionA", question.OptionA);
cmd.Parameters.AddWithValue("OptionB", question.OptionB);
cmd.Parameters.AddWithValue("OptionC", question.OptionC);
cmd.Parameters.AddWithValue("OptionD", question.OptionD);
cmd.Parameters.AddWithValue("Answer", question.Answer);
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception)
{
return(false);
}
return(true);
}
public static List <Student> GetUsers()
{
Student student;
List <Student> students = new List <Student>();
SqlConnection conn = new SqlConnection(DatabaseConstants.GetServer() + DatabaseConstants.MSSQLConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoredProcedures.GetUsers;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
student = new Student();
student.ID = Convert.ToInt32(reader["Id"]);
student.Name = reader["Name"].ToString();
student.RegNumber = reader["RegNumber"].ToString();
student.Trade = reader["Trade"].ToString();
student.Shift = reader["Shift"].ToString();
students.Add(student);
}
conn.Close();
return(students);
}
public static Student Login(string userName, string password)
{
Student student = new Student();
try
{
SqlConnection conn = new SqlConnection(DatabaseConstants.GetServer() + DatabaseConstants.MSSQLConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoredProcedures.Login;
cmd.Parameters.AddWithValue("RegNumber", userName);
cmd.Parameters.AddWithValue("Password", password);
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
student.ID = Convert.ToInt32(reader["Id"]);
student.Name = reader["Name"].ToString();
student.Password = reader["Password"].ToString();
student.RegNumber = reader["RegNumber"].ToString();
student.Trade = reader["Trade"].ToString();
student.Shift = reader["Shift"].ToString();
conn.Close();
}
catch (Exception)
{
student = null;
}
return(student);
}
/**
* Return a reader to the DAO Class to read Database Records
*
* @query : the query for the reader
*
* return SQLiteDataReader with it's query
**/
public SQLiteDataReader getReader(String query)
{
try {
setupDatabaseConnection();
command.CommandText = query;
reader = command.ExecuteReader();
return(reader);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
throw new DatabaseException(DatabaseConstants.INVALID("404"));
}
/**
* Reading the document from the database reader
*
* @dataReader : SQLite Reader that read from the database
*
* return the read document from the database
**/
public override Document find(SQLiteDataReader dataReader)
{
if (dataReader.Read())
{
Document document = new Document();
document.setId(dataReader[idColumn].ToString());
document.setOwner(dataReader[DatabaseConstants.COLUMN_OWENER].ToString());
document.setDocument((byte[])dataReader[DatabaseConstants.COLUMN_DOCUMENT]);
return(document);
}
throw new DatabaseException(DatabaseConstants.NOT_FOUND("404"));
}
/**
* Getting the share from the SQLiteDataReader
*
* @reader : the SQLiteDataReader
*
* return a share from the reader
**/
public override Share find(SQLiteDataReader reader)
{
if (reader.Read())
{
Share share = new Share();
share.userId = reader[idColumn].ToString();
share.documentsIds = CSVParser.CSV2List(reader[documentsIds].ToString()).ToHashSet();
return(share);
}
throw new DatabaseException(DatabaseConstants.NOT_FOUND("404"));
}
/**
* Getting the user from the SQLiteReader
*
* @reader : the SQLiteDataReader for access the database
*
* return user object
**/
public override User find(SQLiteDataReader reader)
{
if (reader.Read())
{
User user = new User();
user.setFullName(reader[DatabaseConstants.COLUMN_FULLNAME].ToString());
user.setIsAuthenticated(int.Parse(reader[DatabaseConstants.COLUMN_AUTH].ToString()));
user.setNotebookId(reader[DatabaseConstants.COLUMN_NOTEBOOKID].ToString());
user.setUsername(reader[DatabaseConstants.COLUMN_USERNAME].ToString());
user.setId(reader[idColumn].ToString());
return(user);
}
throw new DatabaseException(DatabaseConstants.INVALID("No Row to Read"));
}
/**
* Getting all note
*
* @lastNoteId : the last note that was read from the last call
*
* return a list of notes id if it was found and throw an exception otherwise
**/
public List <String> findAll(String lastNoteId = "1")
{
//Logging
Logging.paramenterLogging(nameof(findAll), false, new Pair(nameof(lastNoteId), lastNoteId));
try {
return(findAll(parser, tableName, "-1", lastNoteId));
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(findByTitle), true, new Pair(nameof(lastNoteId), lastNoteId));
//Note was not found
throw new DatabaseException(DatabaseConstants.INVALID(lastNoteId));
}
/**
* Deleting the authentication for the user
*
* @username : the username of the user to delete the authentication
*
* return ture if and only if the delete was done successfully
**/
public bool delete(String username)
{
//Logging
Logging.paramenterLogging(nameof(delete), false, new Pair(nameof(username), username));
//deleting from the database
try {
return(driver.executeQuery(parser.getDelete(DatabaseConstants.TABLE_AUTHENTICATE, DatabaseConstants.COLUMN_USERNAME, username)) != -11);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(register), true, new Pair(nameof(username), username));
//Username not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND(username));
}
/**
* Updating the share in the database
*
* @share : the share to update
* @columns : the columns to update
*
* return true if and only if the update was successfull and false otherwise
**/
public override bool update(Share share, params String [] columns)
{
//Logging
Logging.paramenterLogging(nameof(update), false, new Pair(nameof(columns), columns.ToString()), new Pair(nameof(share), share.ToString()));
//Updating the notebook
try {
return(driver.executeQuery(parser.getUpdate(tableName, idColumn, share.userId, share, columns)) != -1);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(update), true, new Pair(nameof(columns), columns.ToString()), new Pair(nameof(share), share.ToString()));
//Notebook was not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND(share.ToString()));
}
/**
* This method is a generic SQL Update Query statment
*
* @tableName : The Table Name in the Database
* @filter : the filter for the Where Statment
* @condition : the condition for the Where statment
* @column : the column name in the database
* @t : the object that will be updated
*
* It Throws and Exception when one of the parameters are invalid
*
* return an SQL Update Statment
**/
public String getUpdate(String tableName, String filter, String condition, T t, params String[] columns)
{
//Validation
if (columns.Count() == 0)
{
throw new ArgumentException(DatabaseConstants.INVALID(DatabaseConstants.EMPTY_UPDATE) + Logging.paramenterLogging(nameof(getUpdate), true
, new Pair(nameof(columns), columns.ToString())));
}
if (!DataValidator.isValidParameters(tableName, filter, condition) || t == null)
{
throw new ArgumentException(Logging.paramenterLogging(nameof(getUpdate), true
, new Pair(nameof(tableName), tableName)
, new Pair(nameof(filter), filter), new Pair(nameof(t), t.ToString())
, new Pair(nameof(condition), condition)));
}
//Logging
Logging.paramenterLogging(nameof(getUpdate), false
, new Pair(nameof(tableName), tableName)
, new Pair(nameof(filter), filter), new Pair(nameof(t), t.ToString())
, new Pair(nameof(condition), condition));
//Building SQL Statment
StringBuilder query = new StringBuilder();
query.Append("UPDATE ");
query.Append(tableName);
query.Append(" SET ");
String val = "", prefix = "";
foreach (String columnName in columns)
{
query.Append(prefix);
prefix = ",";
query.Append(columnName);
query.Append(" = '");
try {
val = getFieldFromColumn(columnName, t);
} catch (DatabaseException e) {
Logging.logInfo(true, e.Message);
return(null);
}
query.Append(val);
query.Append("'");
}
query.Append(getWhere(filter, condition));
query.Append(";");
return(query.ToString());
}
/**
* Deleting the notebook base on the id
*
* @id : the id of the notebook
*
* return true if and only if the delete operation was successfull
**/
public override bool delete(String id)
{
//Logging
Logging.paramenterLogging(nameof(delete), false, new Pair(nameof(id), id));
//Deleting the Notebook from database
try {
driver.executeQuery(parser.getDelete(tableName, idColumn, id));
return(true);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(delete), true, new Pair(nameof(id), id));
//Notebook was not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND(id));
}
/**
* Register for the user
*
* @auth : the usernamae and password for the user (the authentication object)
*
* return true if and only if the register was done successfully
**/
public bool register(Authentication auth)
{
//Logging
Logging.paramenterLogging(nameof(register), false, new Pair(nameof(auth), auth.ToString()));
//Register
try {
auth.setPassword(encrypt(auth.getPassword()));
return(driver.executeQuery(parser.getInsert(auth)) != -11);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(register), true, new Pair(nameof(auth), auth.ToString()));
//Something went wrong
throw new DatabaseException(DatabaseConstants.INVALID(auth.ToString()));
}
/**
* updatting the task in the SQL Database
*
* @task : the task that will get updated
* @columns : the column in the database that will be updated
*
* return true if and only if the updating operation was successfull
**/
public override bool update(TaskNote task, params String[] columns)
{
//Logging
Logging.paramenterLogging(nameof(update), false, new Pair(nameof(task), task.ToString()));
//Updating
try {
return(driver.executeQuery(parser.getUpdate(tableName,
DatabaseConstants.COLUMN_ID, task.id, task, columns)) != -1);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(update), true, new Pair(nameof(task), task.ToString()));
//TaskNote was not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND(task.ToString()));
}
private void btnBrowesFile_Click(object sender, EventArgs e)
{
openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
openFileDialog.Filter = TypesConstants.FILE_TYPES;
if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
if (openFileDialog.FileName.Length > 4)
{
txtNote.Text = File.ReadAllText(openFileDialog.FileName);
}
else
{
MessageBox.Show(DatabaseConstants.INVALID("File"));
}
}
}
/**
* Getting the Note from the dataReader
*
* @reader : the sql reader
*
* return a note from the reader if it was found and throw an exception otherwise
**/
public override Note find(SQLiteDataReader reader)
{
if (reader.Read())
{
Note note = new Note();
note.setAuthor(reader[DatabaseConstants.COLUMN_AUTHOR].ToString());
note.setDocumentId(reader[DatabaseConstants.COLUMN_DOCUMENTID].ToString());
note.setId(reader[idColumn].ToString());
note.setLastModified(DateTime.Parse(reader[DatabaseConstants.COLUMN_LASTMODIFIED].ToString()));
note.setDateCreated(DateTime.Parse(reader[DatabaseConstants.COLUMN_DATECREATED].ToString()));
note.setTitle(reader[DatabaseConstants.COLUMN_TITLE].ToString());
return(note);
}
//Note was not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND("404"));
}
/**
* Getting the notebook from the SQLiteDataReader
*
* @reader : the SQLiteDataReader
*
* return a notebook from the reader
**/
public override Notebook find(SQLiteDataReader reader)
{
if (reader.Read())
{
Notebook notebook = new Notebook();
notebook.setAuthor(reader[DatabaseConstants.COLUMN_AUTHOR].ToString());
notebook.setId(reader[idColumn].ToString());
notebook.setDateCreated(DateTime.Parse(reader[DatabaseConstants.COLUMN_DATECREATED].ToString()));
notebook.setLastModified(DateTime.Parse(reader[DatabaseConstants.COLUMN_LASTMODIFIED].ToString()));
notebook.setNotes(CSVParser.CSV2List(reader[DatabaseConstants.COLUMN_NOTESID].ToString()).ToHashSet());
notebook.setTitle(reader[DatabaseConstants.COLUMN_TITLE].ToString());
return(notebook);
}
throw new DatabaseException(DatabaseConstants.NOT_FOUND("404"));
}
/**
* Updatting the document in the SQL Database
*
* @document : the document that will get updated
* @columns : the column in the database that will be updated
*
* return true if and only if the updating operation was successfull
**/
public override bool update(Document document, params String[] columns)
{
//Logging
Logging.paramenterLogging(nameof(update), false, new Pair(nameof(document), document.ToString()));
//Updating
try {
bool flag = delete(document.getId()); //This is done because of the blob
return(flag && save(document));
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(update), true, new Pair(nameof(document), document.ToString()));
//Documnet was not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND(document.ToString()));
}
/**
* Open a docuemt
*
* @openFileDialog: the dialog to open and open the file locally
**/
public void open(OpenFileDialog openFileDialog)
{
openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
openFileDialog.Filter = TypesConstants.FILE_TYPES;
if (openFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
if (openFileDialog.FileName.Length > 4)
{
getCurrentDocument.LoadFile(openFileDialog.FileName, RichTextBoxStreamType.PlainText);
}
else
{
MessageBox.Show(DatabaseConstants.INVALID("File"));
}
}
}
public static List <Question> GetAllQuestions()
{
Question question;//= new Question();
List <Question> questions = new List <Question>();
SqlConnection conn = new SqlConnection(DatabaseConstants.GetServer() + DatabaseConstants.MSSQLConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoredProcedures.GetQuestions;
//cmd.Parameters.AddWithValue("RegNumber", userName);
//cmd.Parameters.AddWithValue("Password", password);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
question = new Question();
question.ID = Convert.ToInt32(reader["Id"]);
question.QuestionText = reader["Question"].ToString();
question.OptionA = reader["OptionA"].ToString();
question.OptionB = reader["OptionB"].ToString();
question.OptionC = reader["OptionC"].ToString();
question.OptionD = reader["OptionD"].ToString();
question.Answer = Convert.ToInt32(reader["Answer"]);
questions.Add(question);
}
List <Question> questionsShfile = new List <Question>();
while (questions.Count > 0)
{
Random random = new Random(DateTime.Now.Millisecond);
int randomNumber = random.Next(0, questions.Count);
Thread.Sleep(10);
questionsShfile.Add(questions[randomNumber]);
questions.RemoveAt(randomNumber);
}
conn.Close();
return(questionsShfile);
}
/**
* Getting all Documents Id
*
* @userId : the user to get the documents from
*
* return a list of ids
**/
public List <String> findAllDocumentsIds(String userId)
{
//Logging
Logging.paramenterLogging(nameof(findAllDocumentsIds), false, new Pair(nameof(userId), userId));
//Getting all ids
try {
SQLiteDataReader reader = driver.getReader(parser.getSelect(tableName, idColumn, documentsIds, userId));
List <String> documnetsIds = CSVParser.CSV2List(reader[documentsIds].ToString());
reader.Close();
return(documnetsIds);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(findAllDocumentsIds), true, new Pair(nameof(userId), userId));
//Not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND(userId));
}
/**
* Finding the share based on it's id
*
* @userId : the user Id to search for
*
* return share if it was found and throw an Exception otherwise
**/
public override Share findById(String userId)
{
//Logging
Logging.paramenterLogging(nameof(findById), false, new Pair(nameof(userId), userId));
//Getting all ids
try {
SQLiteDataReader reader = driver.getReader(parser.getSelect(tableName, idColumn, documentsIds, userId));
Share share = find(reader);
reader.Close();
return(share);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(findById), true, new Pair(nameof(userId), userId));
//Not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND(userId));
}
/**
* Finding he notebook based on it's id
*
* @id : the notebook id
*
* return notebook if it was found and throw an Exception otherwise
**/
public override Notebook findById(String id)
{
//Logging
Logging.paramenterLogging(nameof(findById), false, new Pair(nameof(id), id));
//Finding the notebook
try {
SQLiteDataReader reader = driver.getReader(parser.getSelect(tableName, idColumn, DatabaseConstants.ALL, id));
Notebook notebook = find(reader);
reader.Close();
return(notebook);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(findById), true, new Pair(nameof(id), id));
//Notebook was not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND(id));
}
/**
* Getting the Note id from the task id
*
* @taskId : the task id to search for
*
* return a note id if it was found and throw an exception otherwise
**/
public String findNote(String taskId)
{
//Logging
Logging.paramenterLogging(nameof(findNote), false, new Pair(nameof(taskId), taskId));
//Finding note id
try {
SQLiteDataReader reader = driver.getReader(parser.getSelect(tableName, idColumn, DatabaseConstants.COLUMN_NOTEID, taskId));
if (reader.Read())
{
return(reader[DatabaseConstants.COLUMN_NOTEID].ToString());
}
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(findNote), false, new Pair(nameof(taskId), taskId));
//Task id was not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND(taskId));
}
/**
* Get the last id for the inseted value
*
* @tableName : the tableName for the id that was inserted in
*
* return an id
**/
public static String getLastId(String tableName)
{
//Logging
Logging.paramenterLogging(nameof(getLastId), false, new Pair(nameof(tableName), tableName));
try {
SQLiteDataReader reader = DatabaseDriverImplementation.getInstance()
.getReader(DatabaseParserImplementation <T> .getLastAddedRecored(tableName));
if (reader.Read())
{
String id = reader[DatabaseConstants.COLUMN_ID].ToString();
reader.Close();
return(id);
}
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
Logging.paramenterLogging(nameof(getLastId), true, new Pair(nameof(tableName), tableName));
throw new DatabaseException(DatabaseConstants.NOT_FOUND("EMPTY"));
}
/**
* Getting the task from the database based on the id
*
* @id : the task id to search for
*
* return task if found and throw an Exception otherwise
**/
public override TaskNote findById(String id)
{
//Logging
Logging.paramenterLogging(nameof(findById), false, new Pair(nameof(id), id));
//Finding the task
try {
SQLiteDataReader reader = driver.getReader(parser.getSelect(tableName, idColumn, "*", id));
//Reading the the Record from the database
TaskNote task = find(reader);
Logging.logInfo(false, nameof(findById), DatabaseConstants.FOUND(id), task.ToString());
reader.Close();
return(task);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(findById), true, new Pair(nameof(id), id));
//TaskNote not found in the database
throw new DatabaseException(DatabaseConstants.NOT_FOUND(id));
}
/**
* Login for the user
*
* @auth : the usernamae and password for the user (the authentication object)
*
* return true if and only if the login was done successfully
**/
public bool login(Authentication auth)
{
//Logging
Logging.paramenterLogging(nameof(login), false, new Pair(nameof(auth), auth.ToString()));
//Login
try {
SQLiteDataReader reader = driver.getReader(parser.getSelect(DatabaseConstants.TABLE_AUTHENTICATE,
DatabaseConstants.COLUMN_USERNAME, DatabaseConstants.COLUMN_PASSWORD, auth.getUsername()));
if (reader.Read())
{
return(decrypt(reader[DatabaseConstants.COLUMN_PASSWORD].ToString()).Equals(auth.getPassword()));
}
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(login), true, new Pair(nameof(auth), auth.ToString()));
//Something went wrong
throw new DatabaseException(DatabaseConstants.NOT_FOUND(auth.ToString()));
}
public static bool DeleteQuestion(int id)
{
try
{
SqlConnection conn = new SqlConnection(DatabaseConstants.GetServer() + DatabaseConstants.MSSQLConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoredProcedures.DeleteQuestion;
cmd.Parameters.AddWithValue("ID", id);
SqlDataReader reader = cmd.ExecuteReader();
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
catch (Exception)
{
return(false);
}
}
/**
* Getting user based on the username
*
* @username : the username of the user to find
*
* return a user if it was found and throw and exception otherwise
**/
public User findByUsername(String username)
{
//Logging
Logging.paramenterLogging(nameof(findByUsername), false, new Pair(nameof(username), username));
//Finding user
try {
SQLiteDataReader reader = driver.getReader(parser.getSelect(tableName
, DatabaseConstants.COLUMN_USERNAME, DatabaseConstants.ALL, username));
User user = find(reader);
Logging.logInfo(false, user.ToString());
reader.Close();
return(user);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(findByUsername), true, new Pair(nameof(username), username));
//User was not found
throw new DatabaseException(DatabaseConstants.NOT_FOUND(username));
}
/**
* Getting the document from the database based on the id
*
* @id : the document id to search for
*
* return document if found and throw an Exception otherwise
**/
public override Document findById(String id)
{
//Logging
Logging.paramenterLogging(nameof(findById), false, new Pair(nameof(id), id));
try {
//Finding the document
SQLiteDataReader reader = driver.getReader(parser.getSelect(tableName,
DatabaseConstants.COLUMN_ID, DatabaseConstants.ALL, id));
//Reading the the Record from the database
Document document = find(reader);
reader.Close();
return(document);
} catch (Exception e) {
Logging.logInfo(true, e.Message);
}
//Logging
Logging.paramenterLogging(nameof(findById), true, new Pair(nameof(id), id));
//Document not found in the database
throw new DatabaseException(DatabaseConstants.NOT_FOUND(id));
}