public HttpResponseMessage AddTodo([FromBody] todos todo) { //check exist or not int count = 0; count = checkexistbyDescription(todo.Description); if (count.ToString() == "0") { MySqlConnection myConnection = new MySqlConnection(); myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["apidb"].ConnectionString; MySqlCommand sqlCmd = new MySqlCommand(); sqlCmd.CommandType = CommandType.Text; sqlCmd.CommandText = "INSERT INTO TodoItem (Description,DueDate,isDone) Values (@Description,@DueDate,@isDone)"; sqlCmd.Connection = myConnection; sqlCmd.Parameters.AddWithValue("@Description", todo.Description); sqlCmd.Parameters.AddWithValue("@DueDate", todo.DueDate); sqlCmd.Parameters.AddWithValue("@isDone", todo.isDone); try { myConnection.Open(); int rowInserted = sqlCmd.ExecuteNonQuery(); return(Request.CreateErrorResponse(HttpStatusCode.Created, "Task for " + todo.Description + " has been created!")); } catch (Exception) { return(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, "Error occured while executing AddTodo.")); } finally { myConnection.Close(); } } else { return(Request.CreateErrorResponse(HttpStatusCode.NotFound, " Task " + todo.Description.ToString() + " has exited and cannot be created again.")); } }
public HttpResponseMessage UpdateTodo([FromUri] int id, [FromBody] todos todo) { //check exist or not int count = 0; count = checkexistbyID(id); if (count >= 1) { MySqlConnection myConnection = new MySqlConnection(); myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["apidb"].ConnectionString; MySqlCommand sqlCmd = new MySqlCommand(); sqlCmd.CommandType = CommandType.Text; sqlCmd.CommandText = "update TodoItem set Description=@Description,DueDate=@DueDate,isDone=@isDone where id=" + id + ""; sqlCmd.Connection = myConnection; sqlCmd.Parameters.AddWithValue("@Description", todo.Description); sqlCmd.Parameters.AddWithValue("@DueDate", todo.DueDate); sqlCmd.Parameters.AddWithValue("@isDone", todo.isDone); myConnection.Open(); try { int rowInserted = sqlCmd.ExecuteNonQuery(); return(Request.CreateErrorResponse(HttpStatusCode.OK, "Task " + id.ToString() + "'s information has been udpated!")); } catch (Exception) { return(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, "Error occured while executing UpdateTodo.")); } finally { myConnection.Close(); } } else { return(Request.CreateErrorResponse(HttpStatusCode.NotFound, " Task " + id.ToString() + " Not Found and Update didn't execute.")); } }
public HttpResponseMessage QueryByID(int id) { try { MySqlDataReader reader = null; MySqlConnection myConnection = new MySqlConnection(); myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["apidb"].ConnectionString; MySqlCommand sqlCmd = new MySqlCommand(); sqlCmd.CommandType = CommandType.Text; sqlCmd.CommandText = " select * from TodoItem where id=" + id + " "; sqlCmd.Connection = myConnection; myConnection.Open(); reader = sqlCmd.ExecuteReader(); todos todo = null; while (reader.Read()) { todo = new todos(); todo.id = (int)reader.GetValue(0); todo.Description = reader.GetValue(1).ToString(); todo.DueDate = reader.GetValue(2).ToString(); todo.isDone = (bool)reader.GetValue(3); } if (todo == null) { return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Todo for " + id.ToString() + " Not Found!")); } else { return(Request.CreateResponse <todos>(HttpStatusCode.OK, todo)); } } catch (Exception) { return(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, "Error occured while executing QueryByID")); } }