////////////////////////////////////////////////// // IGNORE BEYOND THIS POINT BUT DO NOT DELETE // ////////////////////////////////////////////////// /* //DO NOT DELETE * //Method which returns all project records which have a status code of 0, ie need to be confirmed by a researcher. * public DataTable getResearcherUnconfirmedProjects() * { * //Connects to database * ConnectionClass.OpenConnection(); * * //Declare new mysql command using connection to returns all projects which need * * //MySqlCommand cmd = ConnectionClass.con.CreateCommand(); * //cmd.CommandType = CommandType.Text; * //cmd.CommandText = "SELECT * FROM projects WHERE StatusCode = '0'"; * //cmd.ExecuteNonQuery(); * * String query = "SELECT * FROM projects WHERE StatusCode = '0'"; * * //Create datatable for results to be read into * DataTable dt = new DataTable(); * MySqlDataAdapter da = new MySqlDataAdapter(query, ConnectionClass.con); * //Fill the datatable with the results from the MYSQL command using data adapter * da.Fill(dt); * ConnectionClass.CloseConnection(); * //If the datatable is empty, ie there are no Projects which require a researcher to confirm * if (dt == null) * { * return null; * } * //else if there are projects to be confirmed * else * { * return dt; * } * } */ /* * 1. Using ProjectID researcher wants to sign, check the database to see if it has been confirmed or not already. * 2. If it has not been signed yet, sign it * */ //DO NOT DELETE //Function which takes in a ProjectID for the project to be confirmed, changing its status code to 1 and its Researcher signed value to userID public void researcherConfirmation1(int projectID, int userID) { //Connects to database ConnectionClass.OpenConnection(); DataTable dt = viewProjectInfo(projectID); int researcherSigned = 0; int statusCode = 0; foreach (DataRow dr in dt.Rows) { researcherSigned = Convert.ToInt32(dr["ResearcherSigned"]); statusCode = Convert.ToInt32(dr["StatusCode"]); Console.WriteLine(researcherSigned + " " + statusCode); } if (researcherSigned == 0 && statusCode == 0) { //Declare new mysql command using connection which sets user specified project's ResearcherSigned and StatusCode values to userID ConnectionClass.OpenConnection(); MySqlCommand cmd = ConnectionClass.con.CreateCommand(); cmd.CommandText = "UPDATE projects SET ResearcherSigned = '" + userID + "', StatusCode = '1' WHERE ProjectID = '" + projectID + "'"; cmd.ExecuteNonQuery(); ConnectionClass.CloseConnection(); } ConnectionClass.CloseConnection(); }
public string ValidateLoginDetails(string StaffID, string pwd) { //assign stored procedure string storedProc = "checkLogin;"; DataTable dt = new DataTable(); //this is creating a virtual table ConnectionClass.OpenConnection(); //open connection MySqlConnection connection = new MySqlConnection(ConnectionClass.ConnectionString); MySqlDataAdapter sda = new MySqlDataAdapter(storedProc, connection); //assign parameters sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.AddWithValue("?sID", StaffID); sda.SelectCommand.Parameters.AddWithValue("?pwd", pwd); // in above line the program is selecting the whole data from table and the matching it with the user name and password provided by user. sda.Fill(dt); try //when data table has something in it { Console.WriteLine("found!"); ConnectionClass.CloseConnection(); string uid = dt.Rows[0][0].ToString(); //store the user id as a string return(uid); //return string } catch (Exception) //when data table is empty { return(null); } }
/// <summary> /// Deletes any existing files in the project with the same name then reads the bytes from the pasted stream and inputs them into the database. /// </summary> /// <param name="id">Project ID that the file belongs to</param> /// <param name="stream">Stream of the file you are uploading</param> /// <param name="fileName">File name to display to the user</param> /// <returns>Number of rows effected</returns> public int UploadFile(int id, Stream stream, string fileName) { //Convert file to bytes byte[] file; using (var reader = new BinaryReader(stream)) { file = reader.ReadBytes((int)stream.Length); } ConnectionClass.OpenConnection(); MySqlCommand comm = new MySqlCommand("deleteFileWhereProjectIDAndFileName", ConnectionClass.con); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.Add(new MySqlParameter("?id", id)); comm.Parameters.Add(new MySqlParameter("?fileName", fileName)); comm.ExecuteNonQuery(); //Insert bytes into the storedfiles table comm = new MySqlCommand("createNewFile", ConnectionClass.con); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.AddWithValue("?id", id); comm.Parameters.AddWithValue("?fileName", fileName); comm.Parameters.Add("?fileData", MySqlDbType.LongBlob, file.Length).Value = file; int i = comm.ExecuteNonQuery(); ConnectionClass.CloseConnection(); return(i); }
//Method which returns a datatable containing all the related files returned based on the projectID passed to it. public DataTable viewProjectFiles(int input) { //Connects to database ConnectionClass.OpenConnection(); //Declare new mysql command using stored procedure. MySqlCommand command = new MySqlCommand("viewProjectFiles", ConnectionClass.con); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add(new MySqlParameter("@id", input)); //Create datatable for results to be read into DataTable dt = new DataTable(); //Adaptor to read results into the datatable MySqlDataAdapter sda = new MySqlDataAdapter(command); //Fill the datatable with the results from the MYSQL command using data adapter sda.Fill(dt); //Close Connection ConnectionClass.CloseConnection(); return(dt); }
/* * Below is potential sign/reject methods refactored. Reject doesn't require switch statement as it * doesn't matter who rejects it, all signed fields are reset to 0/default. * All reject stored procedures should simply use 'deanRejectProject'. * * * /// <summary> * /// Sign project switch statement to run correct signing stored procedure. * /// </summary> * /// <param name="projectID"></param> * /// <param name="staffID"></param> * /// <param name="position"></param> * public void SignChoice(int projectID, string staffID, int position) * { * switch (position) * { * case 0: * Sign(projectID, staffID, "researcherSignProject;"); * break; * case 1: * Sign(projectID, staffID, "RISSign;"); * break; * case 2: * Sign(projectID, staffID, "AssocDeanSign;"); * break; * case 3: * Sign(projectID, staffID, "DeanSign;"); * break; * } * } * * /// <summary> * /// Sign selected project with staffID based on user's job position. * /// </summary> * /// <param name="projectID"></param> * /// <param name="staffID"></param> * /// <param name="proc"></param> * /// <returns>Number of records affected</returns> * private int Sign(int projectID, string staffID, string proc) * { * //assign stored procedure * string storedProc = proc; * //open connection * MySqlConnection connection = new MySqlConnection(ConnectionClass.ConnectionString); * connection.Open(); * //define stored procedure * MySqlCommand cmd = new MySqlCommand(storedProc, connection); * cmd.CommandType = System.Data.CommandType.StoredProcedure; * //assign parameters * cmd.Parameters.Add(new MySqlParameter("?pID", projectID)); * cmd.Parameters.Add(new MySqlParameter("?sID", staffID)); * //execute procedure * int i = cmd.ExecuteNonQuery(); * //close connection and return number of rows affected (should be 1) * connection.Close(); * return i; * } * * /// <summary> * /// Rejects selected project. * /// </summary> * /// <param name="projectID"></param> * /// <param name="proc"></param> * /// <returns>Number of records affected</returns> * private int Reject(int projectID) * { * //assign stored procedure * string storedProc = "rejectProject;"; * //open connection * MySqlConnection connection = new MySqlConnection(ConnectionClass.ConnectionString); * connection.Open(); * //define stored procedure * MySqlCommand cmd = new MySqlCommand(storedProc, connection); * cmd.CommandType = System.Data.CommandType.StoredProcedure; * //assign parameters * cmd.Parameters.Add(new MySqlParameter("?pID", projectID)); * //execute procedure * int i = cmd.ExecuteNonQuery(); * //close connection and return number of rows affected (should be 1) * connection.Close(); * return i; * }*/ public bool DeleteProject(int projectID) { try { //assign stored procedure string storedProc = "DeletePairingAndProject;"; //open connection MySqlConnection connection = new MySqlConnection(ConnectionClass.ConnectionString); connection.Open(); //define stored procedure MySqlCommand cmd = new MySqlCommand(storedProc, connection); cmd.CommandType = System.Data.CommandType.StoredProcedure; //assign parameters cmd.Parameters.Add(new MySqlParameter("?pID", projectID)); //execute procedure cmd.ExecuteNonQuery(); ConnectionClass.CloseConnection(); return(true);//file deleted } catch (Exception) { ConnectionClass.CloseConnection(); return(false); throw; } }
//DB INFO: //Hostname: silva.computing.dundee.ac.uk //Port: 3306 //Username: 17agileteam5 //Password: 7485.at5.5847 //DONE //Method which returns a datatable containing all the information returned for a project based on the projectID passed to it. public DataTable viewProjectInfo(int input) { //Connects to database ConnectionClass.OpenConnection(); //Declare new mysql command using stored procedure. MySqlCommand command = new MySqlCommand("viewProjectInfo", ConnectionClass.con); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add(new MySqlParameter("@id", input)); //Create datatable for results to be read into DataTable dt = new DataTable(); //Adaptor to read results into the datatable MySqlDataAdapter adapter = new MySqlDataAdapter(command); //Fill the datatable with the results from the MYSQL command using data adapter adapter.Fill(dt); //Close Connection ConnectionClass.CloseConnection(); //If the datatable is empty, ie the project row does not exist in the database, then return null. if (dt == null) { return(null); } //else if the project record does exist, return this datatable. else { return(dt); } }
/// <summary> /// Takes a file primary key (fileID) and deletes all rows from storedfiles with that key. /// </summary> /// <param name="fileID">Database primary key that you want to delete</param> /// <returns>Number of rows effected</returns> public int DeleteFile(int fileID) { //Deletes all rows with primary key = fileID ConnectionClass.OpenConnection(); MySqlCommand comm = new MySqlCommand("deleteFile", ConnectionClass.con); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.Add(new MySqlParameter("?id", fileID)); int i = comm.ExecuteNonQuery(); ConnectionClass.CloseConnection(); return(i); }
public void updateBot(int id, string p) { ConnectionClass.OpenConnection(); MySqlCommand command = new MySqlCommand("updateProfileBot", ConnectionClass.con); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add(new MySqlParameter("@u", id)); command.Parameters.Add(new MySqlParameter("@p", p)); command.ExecuteNonQuery(); ConnectionClass.CloseConnection(); }
/// <summary> /// /// </summary> /// <param name="title"></param> /// <param name="userID"></param> /// <returns></returns> public String CreateProject(string title, int userID) { MySqlCommand cmd; ConnectionClass.OpenConnection(); cmd = ConnectionClass.con.CreateCommand(); //New Connection object try { //FIRST INSERT THE NEW PROJECT INTO THE PROJECTS TABLE //SQL Query cmd.CommandText = "INSERT INTO projects(Title)VALUES(@title);SELECT LAST_INSERT_ID();"; // Populate SQl query values cmd.Parameters.AddWithValue("@title", title); // Execute Query MySqlDataReader reader = cmd.ExecuteReader(); String pID = ""; while (reader.Read()) { pID = reader.GetString("LAST_INSERT_ID()"); } reader.Close(); //FOLLOW BY INSERTING THE PROJECT AND ID INTO LINK //SQL Query cmd.CommandText = "INSERT INTO userprojectpairing(LoginDetails_UserID,Projects_ProjectID)VALUES(@userID,@projID)"; // Populate SQl query values cmd.Parameters.AddWithValue("@userID", userID); cmd.Parameters.AddWithValue("@projID", pID); // Execute Query cmd.ExecuteNonQuery(); // Close Connection ConnectionClass.CloseConnection(); return(pID); } catch (Exception) { ConnectionClass.CloseConnection(); return(null); throw; } }
public DataTable ViewSignedProjects(string id) { ConnectionClass.OpenConnection(); MySqlConnection connection = new MySqlConnection(ConnectionClass.ConnectionString); MySqlDataAdapter sda = new MySqlDataAdapter("viewSignedProjects", connection); sda.SelectCommand.CommandType = CommandType.StoredProcedure; sda.SelectCommand.Parameters.AddWithValue("?sID", id); DataTable dt = new DataTable(); sda.Fill(dt); ConnectionClass.CloseConnection(); return(dt); }
public void updateTop(int id, string f, string l, string e) { ConnectionClass.OpenConnection(); MySqlCommand command = new MySqlCommand("updateProfileTop", ConnectionClass.con); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add(new MySqlParameter("@u", id)); command.Parameters.Add(new MySqlParameter("@f", f)); command.Parameters.Add(new MySqlParameter("@l", l)); command.Parameters.Add(new MySqlParameter("@e", e)); command.ExecuteNonQuery(); ConnectionClass.CloseConnection(); }
public DataTable getUserInfo(int inputID) { //Connects to database ConnectionClass.OpenConnection(); //Declare new mysql command using stored procedure. MySqlCommand command = new MySqlCommand("returnProfile", ConnectionClass.con); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add(new MySqlParameter("@id", inputID)); //Create datatable for results to be read into DataTable dt = new DataTable(); //Adaptor to read results into the datatable MySqlDataAdapter adapter = new MySqlDataAdapter(command); //Fill the datatable with the results from the MYSQL command using data adapter adapter.Fill(dt); //Close Connection ConnectionClass.CloseConnection(); /* * foreach (DataRow dataRow in dt.Rows) * { * foreach (var item in dataRow.ItemArray) * { * Console.WriteLine(item); * } * * } */ //If the datatable is empty, ie the project row does not exist in the database, then return null. if (dt == null) { return(null); } //else if the project record does exist, return this datatable. else { return(dt); } }
/// <summary> /// Downloads all files with the passed project ID into the path location passed to it /// </summary> /// <param name="id">Project ID to fetch files from</param> /// <param name="path">Path to download folder</param> /// <returns>List of paths to the files downloaded</returns> public List <String> DownloadAllFiles(int id, string path) { List <String> fileList = new List <string>(); ConnectionClass.OpenConnection(); MySqlCommand comm = new MySqlCommand("selectAllFilesWithProjectID", ConnectionClass.con); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.AddWithValue("@id", id); using (MySqlDataReader sqlQueryResult = comm.ExecuteReader()) { if (sqlQueryResult.HasRows) { //Loop for all files while (sqlQueryResult != null && sqlQueryResult.Read()) { byte[] blob = new Byte[(sqlQueryResult.GetBytes(sqlQueryResult.GetOrdinal("FileData"), 0, null, 0, int.MaxValue))]; sqlQueryResult.GetBytes(sqlQueryResult.GetOrdinal("FileData"), 0, blob, 0, blob.Length); //Manage file name duplication filename(count).filetype String fileName = sqlQueryResult["FileName"].ToString(); String fullPath = System.IO.Path.Combine(path, fileName); int count = 1; while (File.Exists(fullPath)) { string[] split = fileName.Split('.'); fullPath = System.IO.Path.Combine(path, split[0] + "(" + count + ")." + split[1]); count++; } using (FileStream fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write)) { fs.Write(blob, 0, blob.Length); fileList.Add(fullPath); } } } } ConnectionClass.CloseConnection(); return(fileList); }
/// <summary> /// Gets the email of a user with the passed id /// </summary> /// <param name="id">The database user primary key</param> /// <returns>The email that matches with the userID</returns> public String getUserEmail(int id) { String email = ""; ConnectionClass.OpenConnection(); MySqlCommand comm = new MySqlCommand("getEmailOfUser", ConnectionClass.con); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.AddWithValue("@id", id); using (MySqlDataReader sqlQueryResult = comm.ExecuteReader()) { if (sqlQueryResult.HasRows) { while (sqlQueryResult != null && sqlQueryResult.Read()) { email = sqlQueryResult["Email"].ToString(); } } } ConnectionClass.CloseConnection(); return(email); }
public int GetRISSignID(int projectID) { int RISID = 0; ConnectionClass.OpenConnection(); MySqlCommand comm = new MySqlCommand("GetRISSignID", ConnectionClass.con); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.AddWithValue("@pID", projectID); using (MySqlDataReader sqlQueryResult = comm.ExecuteReader()) { if (sqlQueryResult.HasRows) { while (sqlQueryResult != null && sqlQueryResult.Read()) { RISID = (int)sqlQueryResult["Title"]; } } } ConnectionClass.CloseConnection(); return(RISID); }
public string GetProjectName(int projectID) { string projectName = ""; ConnectionClass.OpenConnection(); MySqlCommand comm = new MySqlCommand("GetProjectName", ConnectionClass.con); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.AddWithValue("@pID", projectID); using (MySqlDataReader sqlQueryResult = comm.ExecuteReader()) { if (sqlQueryResult.HasRows) { while (sqlQueryResult != null && sqlQueryResult.Read()) { projectName = (string)sqlQueryResult["Title"]; } } } ConnectionClass.CloseConnection(); return(projectName); }
public int GetProjectOwner(int pID) { int owner = 0; ConnectionClass.OpenConnection(); MySqlCommand comm = new MySqlCommand("GetProjectOwner", ConnectionClass.con); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.AddWithValue("@id", pID); using (MySqlDataReader sqlQueryResult = comm.ExecuteReader()) { if (sqlQueryResult.HasRows) { while (sqlQueryResult != null && sqlQueryResult.Read()) { owner = (int)sqlQueryResult["LoginDetails_UserID"]; } } } ConnectionClass.CloseConnection(); return(owner); }
public byte[] GetFile(int id) { ConnectionClass.OpenConnection(); MySqlCommand comm = new MySqlCommand("selectFileWithFileID", ConnectionClass.con); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.Parameters.AddWithValue("@id", id); using (MySqlDataReader sqlQueryResult = comm.ExecuteReader()) { if (sqlQueryResult.HasRows) { //Loop for all files while (sqlQueryResult != null && sqlQueryResult.Read()) { byte[] blob = new Byte[(sqlQueryResult.GetBytes(sqlQueryResult.GetOrdinal("FileData"), 0, null, 0, int.MaxValue))]; sqlQueryResult.GetBytes(sqlQueryResult.GetOrdinal("FileData"), 0, blob, 0, blob.Length); ConnectionClass.CloseConnection(); return(blob); } } } return(null); }