public Task <List <ObjectItem.WorkItem> > GetWorkItemByRandomSelectionAsync(int num) { List <ObjectItem.WorkItem> paraWorks = new List <ObjectItem.WorkItem>(); OracleConnection conn = new OracleConnection(ConString.conString); OracleCommand cmd = conn.CreateCommand(); conn.Open(); cmd.BindByName = true; cmd.CommandText = "select * from work order by upload_time desc"; //OracleParameter para = new OracleParameter("category", category); //cmd.Parameters.Add(para); OracleDataReader reader = cmd.ExecuteReader(); int count = 0; DateTime dt = DateTime.Now; dt.AddYears(-1); while (reader.Read() && count < num) { //Random rand = new Random(); //if (rand.Next(1, 100) < 50) //{ if (DateTime.Compare(dt, reader.GetDateTime(7)) > 0) { ObjectItem.WorkItem workitem = new ObjectItem.WorkItem { work_ID = reader.GetString(0), user_ID = reader.GetString(1), headline = reader.GetString(2), introduction = reader.GetString(3), picture = reader.GetString(4), likes_num = reader.GetInt32(5), collect_num = reader.GetInt32(6), upload_time = reader.GetDateTime(7), points_need = reader.GetInt32(8) }; count++; paraWorks.Add(workitem); } //} } reader.Close(); conn.Close(); return(Task.FromResult(paraWorks)); }
public Task <List <ObjectItem.WorkItem> > GetWorkItemByIDAsync(string name) { List <ObjectItem.WorkItem> paraWorks = new List <ObjectItem.WorkItem>(); if (name is null) { return(Task.FromResult(paraWorks)); } OracleConnection conn = new OracleConnection(ConString.conString); OracleCommand cmd = conn.CreateCommand(); conn.Open(); cmd.BindByName = true; cmd.CommandText = "select * from work where headline like '%' || :para_headline || '%'"; OracleParameter para = new OracleParameter("para_headline", name); cmd.Parameters.Add(para); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ObjectItem.WorkItem workitem = new ObjectItem.WorkItem { work_ID = reader.GetString(0), user_ID = reader.GetString(1), headline = reader.GetString(2), introduction = reader.GetString(3), picture = reader.GetString(4), likes_num = reader.GetInt32(5), collect_num = reader.GetInt32(6), upload_time = reader.GetDateTime(7), points_need = reader.GetInt32(8) }; paraWorks.Add(workitem); } reader.Close(); conn.Close(); return(Task.FromResult(paraWorks)); }
public Task <List <ObjectItem.WorkItem> > GetWorkItemByCategoryAsync(String category, String order) { List <ObjectItem.WorkItem> paraWorks = new List <ObjectItem.WorkItem>(); OracleConnection conn = new OracleConnection(ConString.conString); OracleCommand cmd = conn.CreateCommand(); conn.Open(); cmd.BindByName = true; cmd.CommandText = "select work_ID ,user_ID ,headline ,introduction,picture ,likes_num ,collect_num ,upload_time ,points_need " + "from work natural join work_type where typename= :category order by " + order + " desc"; OracleParameter para_category = new OracleParameter("category", category); cmd.Parameters.Add(para_category); //OracleParameter para_order = new OracleParameter("morder", order); //cmd.Parameters.Add(para_order); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ObjectItem.WorkItem workitem = new ObjectItem.WorkItem { work_ID = reader.GetString(0), user_ID = reader.GetString(1), headline = reader.GetString(2), introduction = reader.GetString(3), picture = reader.GetString(4), likes_num = reader.GetInt32(5), collect_num = reader.GetInt32(6), upload_time = reader.GetDateTime(7), points_need = reader.GetInt32(8) }; paraWorks.Add(workitem); } reader.Close(); conn.Close(); return(Task.FromResult(paraWorks)); }
public Task <List <ObjectItem.WorkItem> > GetWorkItemInUserLikesAsync(string user_id) { List <ObjectItem.WorkItem> paraWorks = new List <ObjectItem.WorkItem>(); OracleConnection conn = new OracleConnection(ConString.conString); OracleCommand cmd = conn.CreateCommand(); conn.Open(); cmd.BindByName = true; cmd.CommandText = "select work_ID ,likes.user_ID ,headline ,introduction,picture ,likes_num ," + "collect_num ,upload_time ,points_need" + " from likes join work using(work_ID) where likes.user_ID = :userID "; OracleParameter para = new OracleParameter("userID", user_id); cmd.Parameters.Add(para); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ObjectItem.WorkItem workitem = new ObjectItem.WorkItem { work_ID = reader.GetString(0), user_ID = reader.GetString(1), headline = reader.GetString(2), introduction = reader.GetString(3), picture = reader.GetString(4), likes_num = reader.GetInt32(5), collect_num = reader.GetInt32(6), upload_time = reader.GetDateTime(7), points_need = reader.GetInt32(8) }; paraWorks.Add(workitem); } reader.Close(); conn.Close(); return(Task.FromResult(paraWorks)); }