public List <Wall> GetWall(WallComing page) { HttpStatusCode responseCode = HttpStatusCode.OK; CommonController cc = new CommonController(); List <Wall> wList = new List <Wall>(); MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["sq_traffiti"].ConnectionString); try { int pageSize = 20; int offset = (page.pageNum - 1) * pageSize; cn.Open(); MySqlCommand cmd = new MySqlCommand(@"select s.wall_id, s.content, s.photo_path, s.author_id, a.author_name, a.profile_pic, l.location_en as location, s.created_date, w.fav_count, w.like_count from wall_snap s, author a, location l, wall w where s.author_id = a.author_id and s.location_id = l.location_id and s.wall_id = w.wall_id order by s.created_date desc limit @pageSize offset @offset", cn); cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@pageSize", MySqlDbType.Int32).Value = pageSize; cmd.Parameters.Add("@offset", MySqlDbType.Int32).Value = offset; cmd.Parameters.Add("@lang_id", MySqlDbType.Int32).Value = page.lang_id; MySqlDataAdapter ad = new MySqlDataAdapter(cmd); DataSet ds = new DataSet(); ad.Fill(ds); foreach (DataRow dr in ds.Tables[0].Rows) { Wall w = new Wall(); w.wall_id = Convert.ToInt32(dr["wall_id"]); w.content = dr["content"].ToString(); w.photo_path = dr["photo_path"].ToString(); w.author_id = Convert.ToInt32(dr["author_id"]); w.author_name = dr["author_name"].ToString(); w.profile_pic = dr["profile_pic"].ToString(); w.location = dr["location"].ToString(); w.date_text = cc.CalculateDateTime(Convert.ToDateTime(dr["created_date"]), page.lang_id); w.fav_count = Convert.ToInt32(dr["fav_count"]); w.like_count = Convert.ToInt32(dr["like_count"]); wList.Add(w); } } catch (Exception ex) { responseCode = HttpStatusCode.InternalServerError; ErrorHandling(responseCode, 1, ex.Message); } finally { cn.Close(); } return(wList); }
public List <WallDetail> GetProfileWallList(WallComing wc) { CommonController cc = new CommonController(); List <WallDetail> wdList = new List <WallDetail>(); MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["sq_traffiti"].ConnectionString); try { cn.Open(); MySqlCommand cmd = new MySqlCommand(@"select w.wall_id, w.author_id, w.created_date, w.like_count, w.fav_count, w.location_id, a.author_name, a.profile_pic, case @lang when 1 then l.location_en when 2 then l.location_tc when 3 then l.location_sc end as location from wall w, author a, location l where w.author_id = a.author_id and w.location_id = l.location_id and w.author_id = @authorID order by w.created_date desc", cn); cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@wall_id", MySqlDbType.Int32).Value = wc.wall_id; cmd.Parameters.Add("@lang", MySqlDbType.Int32).Value = wc.lang_id; cmd.Parameters.Add("@authorID", MySqlDbType.Int32).Value = wc.authorID; MySqlDataReader detailDr = cmd.ExecuteReader(); while (detailDr.Read()) { WallDetail wd = new WallDetail(); wd.wall_id = Convert.ToInt32(detailDr["wall_id"]); wd.location = detailDr["location"].ToString(); wd.author_id = Convert.ToInt32(detailDr["author_id"]); wd.author_name = detailDr["author_name"].ToString(); wd.profile_pic = detailDr["profile_pic"].ToString(); wd.date_text = cc.CalculateDateTime(Convert.ToDateTime(detailDr["created_date"]), wc.lang_id); wd.fav_count = Convert.ToInt32(detailDr["fav_count"]); wd.like_count = Convert.ToInt32(detailDr["like_count"]); wdList.Add(wd); } detailDr.Close(); for (int i = 0; i < wdList.Count; i++) { cmd = new MySqlCommand("select photo_path from photo where wall_id = @wall_id order by is_default desc", cn); cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@wall_id", MySqlDbType.Int32).Value = wdList[i].wall_id; DataSet ds = new DataSet(); MySqlDataAdapter ad = new MySqlDataAdapter(cmd); ad.Fill(ds); wdList[i].photoList = new List <string>(); foreach (DataRow dr in ds.Tables[0].Rows) { if (dr["photo_path"] != DBNull.Value && !string.IsNullOrEmpty(dr["photo_path"].ToString())) { wdList[i].photoList.Add(dr["photo_path"].ToString()); } } } //MySqlCommand contentCmd = new MySqlCommand("select content from wall_section where wall_id = @wall_id order by display_order", cn); //contentCmd.CommandType = CommandType.Text; //contentCmd.Parameters.Add("@wall_id", MySqlDbType.Int32).Value = wc.wall_id; //ds = new DataSet(); //ad = new MySqlDataAdapter(contentCmd); //ad.Fill(ds); //wd.contentList = new List<string>(); //foreach (DataRow dr in ds.Tables[0].Rows) //{ // wd.contentList.Add(dr["content"].ToString()); //} } catch (Exception ex) { } finally { cn.Close(); } return(wdList); }