//Insert statement public void Insert(Models.SavedJob savedJobs) { string query = "INSERT INTO savedjobs (" + "freelancerID, " + "jobID) VALUES(" + "\"" + savedJobs.freelancerID + "\"" + ", " + "\"" + savedJobs.jobID + "\"" + ")"; //open connection if (this.OpenConnection() == true) { //create command and assign the query and connection from the constructor MySqlCommand cmd = new MySqlCommand(query, connection); //Execute command cmd.ExecuteNonQuery(); //close connection this.CloseConnection(); } }
//Select statement public List <Models.SavedJob> SelectAll() { string query = "SELECT * FROM savedjobs"; //Create a list to store the result List <Models.SavedJob> list = new List <Models.SavedJob>(); //Open connection if (this.OpenConnection() == true) { //Create Command MySqlCommand cmd = new MySqlCommand(query, connection); //Create a data reader and Execute the command MySqlDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list while (dataReader.Read()) { Models.SavedJob savedJobs = new Models.SavedJob(); savedJobs.savedID = Int32.Parse(dataReader["savedID"].ToString()); savedJobs.freelancerID = Int32.Parse(dataReader["freelancerID"].ToString()); savedJobs.jobID = Int32.Parse(dataReader["jobID"].ToString()); list.Add(savedJobs); } //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); //return list to be displayed return(list); } else { return(list); } }
//Select statement with UserID public Models.SavedJob SelectwithId(string savedID) { string query = "SELECT * FROM savedjobs where savedID = " + "\"" + savedID + "\""; //Create a Object to store the result Models.SavedJob savedJobs = new Models.SavedJob(); //Open connection if (this.OpenConnection() == true) { //Create Command MySqlCommand cmd = new MySqlCommand(query, connection); //Create a data reader and Execute the command MySqlDataReader dataReader = cmd.ExecuteReader(); //Read the data and store them in the list while (dataReader.Read()) { savedJobs.savedID = Int32.Parse(dataReader["savedID"].ToString()); savedJobs.freelancerID = Int32.Parse(dataReader["freelancerID"].ToString()); savedJobs.jobID = Int32.Parse(dataReader["jobID"].ToString()); } //close Data Reader dataReader.Close(); //close Connection this.CloseConnection(); //return list to be displayed return(savedJobs); } else { return(savedJobs); } }
//Update statement public void Update(Models.SavedJob savedJobs) { string query = "UPDATE savedjobs SET " + "freelancerID=" + "\"" + savedJobs.freelancerID + "\"" + ", " + "jobID=" + "\"" + savedJobs.jobID + "\"" + ", " + "WHERE savedID=" + "\"" + savedJobs.savedID; //Open connection if (this.OpenConnection() == true) { //create mysql command MySqlCommand cmd = new MySqlCommand(); //Assign the query using CommandText cmd.CommandText = query; //Assign the connection using Connection cmd.Connection = connection; //Execute query cmd.ExecuteNonQuery(); //close connection this.CloseConnection(); } }