//Insert statement
public void Insert(Models.Proposal proposal)
{
string query = "INSERT INTO proposals (" +
"jobID, " +
"freelancerID, " +
"propDescription, " +
"propPrice, " +
"clientAcceptance) VALUES(" +
"\"" + proposal.jobID + "\"" + ", " +
"\"" + proposal.freelancerID + "\"" + ", " +
"\"" + proposal.propDescription + "\"" + ", " +
"\"" + proposal.propPrice + "\"" + ", " +
"\"" + proposal.clientAcceptance + "\"" +
")";
//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();
}
}
//Update statement
public void Update(Models.Proposal proposal)
{
string query = "UPDATE proposals SET " +
"jobID=" + "\"" + proposal.jobID + "\"" + ", " +
"freelancerID=" + "\"" + proposal.freelancerID + "\"" + ", " +
"propDescription=" + "\"" + proposal.propDescription + "\"" + ", " +
"propPrice=" + "\"" + proposal.propPrice + "\"" + ", " +
"clientAcceptance=" + "\"" + proposal.clientAcceptance + "\"" +
"WHERE propID=" + proposal.propID;
//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();
}
}
//Select statement
public List <Models.Proposal> SelectAll()
{
string query = "SELECT * FROM proposals";
//Create a list to store the result
List <Models.Proposal> list = new List <Models.Proposal>();
//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.Proposal proposal = new Models.Proposal();
proposal.propID = Int32.Parse(dataReader["propID"].ToString());
proposal.jobID = Int32.Parse(dataReader["jobID"].ToString());
proposal.freelancerID = Int32.Parse(dataReader["freelancerID"].ToString());
proposal.propDescription = dataReader["propDescription"].ToString();
proposal.propPrice = Int32.Parse(dataReader["propPrice"].ToString());
proposal.clientAcceptance = dataReader["clientAcceptance"].ToString();
list.Add(proposal);
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
//return list to be displayed
return(list);
}
else
{
return(list);
}
}
//unvote a proposal
// DELETE api/votehistory/5
public HttpResponseMessage Delete(ViewUnvote unvote)
{
if (ModelState.IsValid)
{
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK);
VoteHistory history = _db.VoteHistory.Find(unvote.Id);
if (history != null)
{
_db.VoteHistory.Remove(history); //remove vote history
Models.User user = _db.User.FirstOrDefault(c => c.UserId == WebSecurity.CurrentUserId);
user.RemainingVotes++;
Models.Proposal proposal = _db.Proposal.FirstOrDefault(c => c.Id == unvote.ProposalId);
proposal.Votes--;
_db.SaveChanges();
return(response);
}
return(response = Request.CreateResponse(HttpStatusCode.NotModified));
}
else
{
return(Request.CreateResponse(HttpStatusCode.BadRequest));
}
}
