public int AddTodo([FromBody] newtodos todo)
{
//check exist or not
int count = 0;
count = checkexistbyDescription(todo.username, todo.Description);
int userexit = 0;
userexit = checkusername(todo.username);
if (userexit > 0)
{
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 NewTodoItem (username,Description,DueDate,isDone) Values (@username,@Description,@DueDate,@isDone)";
sqlCmd.Connection = myConnection;
sqlCmd.Parameters.AddWithValue("@username", todo.username);
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(2);
}
catch (Exception)
{
return(3);
}
finally
{
myConnection.Close();
}
}
else
{
return(1);
}
}
else
{
//no existing username
return(4);
}
}
public int UpdateTodo([FromUri] int id, [FromBody] newtodos 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 NewTodoItem 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(7);
}
catch (Exception)
{
return(8);
}
finally
{
myConnection.Close();
}
}
else
{
return(9);
}
}
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 NewTodoItem where id=" + id + " ";
sqlCmd.Connection = myConnection;
myConnection.Open();
reader = sqlCmd.ExecuteReader();
newtodos todo = null;
while (reader.Read())
{
todo = new newtodos();
todo.id = (int)reader.GetValue(0);
todo.username = reader.GetValue(1).ToString();
todo.Description = reader.GetValue(2).ToString();
todo.DueDate = reader.GetValue(3).ToString();
todo.isDone = (bool)reader.GetValue(4);
}
if (todo == null)
{
return(Request.CreateErrorResponse(HttpStatusCode.NotFound, "Todo for " + id.ToString() + " Not Found!"));
}
else
{
return(Request.CreateResponse <newtodos>(HttpStatusCode.OK, todo));
}
}
catch (Exception)
{
return(Request.CreateErrorResponse(HttpStatusCode.InternalServerError, "Error occured while executing QueryByID"));
}
}