public List <Change_Request_Index> GetAllChangeRequest_Index() { //directly using sql conn.ConnectionString = mysqlconnection; if (conn.State != ConnectionState.Open) { conn.Open(); } //ACTIVE = 1 : ACTIVE ACTIVE = 0 : INACTIVE string sqlcmd = @"SELECT cr.*,r.regionName AS Region_Name,c.Category_Name AS Category_Name,u.Name AS UserName,p.Picture_Path AS Path FROM change_request cr LEFT JOIN region r ON (r.regionID = cr.Region_Id) LEFT JOIN category c ON (c.Category_Id = cr.Category_Id) LEFT JOIN user u ON(u.User_Id = cr.Update_User_Id) LEFT JOIN picture p ON(p.Picture_Id = cr.Picture_Id) WHERE cr.Active = 1 ORDER BY cr.Last_Update_Date_Time"; MySqlCommand cmd = new MySqlCommand(sqlcmd, conn); List <Change_Request_Index> c = new List <Change_Request_Index>(); using (MySqlDataReader rd = cmd.ExecuteReader()) { while (rd.Read()) { Change_Request_Index ci = new Change_Request_Index(); ci.Change_Request_Id = Convert.ToInt32(rd["Change_Request_Id"]); ci.Region_Id = Convert.ToInt32(rd["Region_Id"]); ci.regionName = rd["Region_Name"].ToString(); ci.categoryName = rd["Category_Name"].ToString(); ci.Category_Id = Convert.ToInt32(rd["Category_Id"]); ci.Content_Id = Convert.ToInt32(rd["Content_Id"]); ci.Content_Name = rd["Content_Name"].ToString(); ci.year = Convert.ToInt32(rd["Year"]); ci.userid = Convert.ToInt32(rd["Update_User_Id"]); ci.usename = rd["UserName"].ToString(); ci.Lastupdatetime = Convert.ToDateTime(rd["Last_Update_Date_Time"]); ci.picture_id = Convert.ToInt32(rd["Picture_Id"]); ci.picname = rd["Path"].ToString(); c.Add(ci); } } conn.Close(); return(c); }
public List <Change_Request_Index> GetAllChangeRequest_Index_with_filter(int rid, int cid, int yearcode) { /* * yearList.Add(new SelectListItem() { Text = "1990~ The End of the Cold War", Value = "1" }); * yearList.Add(new SelectListItem() { Text = "1914~ Great War", Value = "2" }); * yearList.Add(new SelectListItem() { Text = "1837~ The age of Victoria", Value = "3" }); * yearList.Add(new SelectListItem() { Text = "1776~ American Revolution", Value = "4" }); * yearList.Add(new SelectListItem() { Text = "1688~ Glorious Revolution", Value = "5" }); * yearList.Add(new SelectListItem() { Text = "1453~ The Fall of Constantinople", Value = "6" }); * yearList.Add(new SelectListItem() { Text = "962~ The Holly Roman Empire", Value = "7" }); * yearList.Add(new SelectListItem() { Text = "476~ The End of the Roman Empire", Value = "8" }); * yearList.Add(new SelectListItem() { Text = "A.D.", Value = "9" }); * yearList.Add(new SelectListItem() { Text = "B.C.", Value = "10" }); */ conn.ConnectionString = mysqlconnection; if (conn.State != ConnectionState.Open) { conn.Open(); } //ACTIVE = 1 : ACTIVE ACTIVE = 0 : INACTIVE string sqlcmd = @"SELECT cr.*,r.regionName AS Region_Name,c.Category_Name AS Category_Name,u.Name AS UserName,p.Picture_Path AS Path FROM change_request cr LEFT JOIN region r ON (r.regionID = cr.Region_Id) LEFT JOIN category c ON (c.Category_Id = cr.Category_Id) LEFT JOIN user u ON(u.User_Id = cr.Update_User_Id) LEFT JOIN picture p ON(p.Picture_Id = cr.Picture_Id) WHERE cr.Active = 1";// ORDER BY cr.Last_Update_Date_Time"; if (rid != 999) { sqlcmd = sqlcmd + @" AND cr.Region_Id = @rid "; } if (cid != 999) { sqlcmd = sqlcmd + @" AND cr.Category_Id = @cid "; } if (yearcode != 11) { if (yearcode == 1) { sqlcmd = sqlcmd + @" AND cr.year >= 1990 "; } else if (yearcode == 2) { sqlcmd = sqlcmd + @" AND cr.year >= 1914 AND cr.year < 1990 "; } else if (yearcode == 3) { sqlcmd = sqlcmd + @" AND cr.year >= 1837 AND cr.year < 1914 "; } else if (yearcode == 4) { sqlcmd = sqlcmd + @" AND cr.year >= 1776 AND cr.year < 1837 "; } else if (yearcode == 5) { sqlcmd = sqlcmd + @" AND cr.year >= 1688 AND cr.year < 1776 "; } else if (yearcode == 6) { sqlcmd = sqlcmd + @" AND cr.year >= 1453 AND cr.year < 1688 "; } else if (yearcode == 7) { sqlcmd = sqlcmd + @" AND cr.year >= 962 AND cr.year < 1453 "; } else if (yearcode == 8) { sqlcmd = sqlcmd + @" AND cr.year >= 476 AND cr.year < 962 "; } else if (yearcode == 9) { sqlcmd = sqlcmd + @" AND cr.year >= 0 AND cr.year < 476 "; } else if (yearcode == 10) { sqlcmd = sqlcmd + @" AND cr.year < 0 "; } } sqlcmd = sqlcmd + @" ORDER BY cr.Year, c.Category_Name,r.regionName"; List <Change_Request_Index> c = new List <Change_Request_Index>(); using (MySqlCommand cmd = new MySqlCommand(sqlcmd, conn)) { if (rid != 999) { cmd.Parameters.AddWithValue("@rid", rid); } if (cid != 999) { cmd.Parameters.AddWithValue("@cid", cid); } using (MySqlDataReader rd = cmd.ExecuteReader()) { while (rd.Read()) { Change_Request_Index ci = new Change_Request_Index(); ci.Change_Request_Id = Convert.ToInt32(rd["Change_Request_Id"]); ci.Region_Id = Convert.ToInt32(rd["Region_Id"]); ci.regionName = rd["Region_Name"].ToString(); ci.categoryName = rd["Category_Name"].ToString(); ci.Category_Id = Convert.ToInt32(rd["Category_Id"]); ci.Content_Id = Convert.ToInt32(rd["Content_Id"]); ci.Content_Name = rd["Content_Name"].ToString(); ci.year = Convert.ToInt32(rd["Year"]); ci.userid = Convert.ToInt32(rd["Update_User_Id"]); ci.usename = rd["UserName"].ToString(); ci.Lastupdatetime = Convert.ToDateTime(rd["Last_Update_Date_Time"]); ci.picture_id = Convert.ToInt32(rd["Picture_Id"]); ci.picname = rd["Path"].ToString(); c.Add(ci); } } }; conn.Close(); return(c); }