public int EditContent(Change_Request newobj) { var result = 0; conn.ConnectionString = mysqlconnection; if (conn.State != ConnectionState.Open) { conn.Open(); } //MYSQL database set PK content_id auto increment var sqlcmd = @"UPDATE content SET Content_Name = @contentname,Year = @year, Update_User_Id = @userid, Last_Update_Date_Time = @updatetime,Video_Key = @video, Wiki_Key = @wiki,Detail = @detail,Picture_Id = @pictureid WHERE Region_Id = @regionid AND Category_Id = @categoryid AND Content_Id = @contentid"; using (var cmd = new MySqlCommand(sqlcmd, conn)) { cmd.Parameters.AddWithValue("@contentid", newobj.Content_Id); cmd.Parameters.AddWithValue("@regionid", newobj.Region_Id); cmd.Parameters.AddWithValue("@categoryid", newobj.Category_Id); cmd.Parameters.AddWithValue("@contentname", newobj.Content_Name); cmd.Parameters.AddWithValue("@year", newobj.year); cmd.Parameters.AddWithValue("@userid", newobj.userid); cmd.Parameters.AddWithValue("@updatetime", DateTime.Now); cmd.Parameters.AddWithValue("@video", newobj.video_key); cmd.Parameters.AddWithValue("@wiki", newobj.wiki_key); cmd.Parameters.AddWithValue("@detail", newobj.detail); cmd.Parameters.AddWithValue("@pictureid", newobj.picture_id); result = cmd.ExecuteNonQuery(); } conn.Close(); return(result); }
//maybe add a Active and Reason column for cr? public int AddOneChangeRequest(Change_Request newobj) { int result = 0; conn.ConnectionString = mysqlconnection; if (conn.State != ConnectionState.Open) { conn.Open(); } var sqlcmd = @"INSERT INTO change_request (Region_Id,Category_Id,Content_Id, Content_Name,Year,Update_User_Id,Last_Update_Date_Time,Video_Key, Wiki_Key,Detail,Picture_Id,Reason,Request_Type,Active )VALUE(@regionid,@categoryid,@contentid,@contentname,@year, @userid,@updatetime,@video,@wiki,@detail,@pictureid,@reason,@type,@active)"; using (var cmd = new MySqlCommand(sqlcmd, conn)) { //cmd.Parameters.AddWithValue("@change_id", newobj.Change_Request_Id); cmd.Parameters.AddWithValue("@regionid", newobj.Region_Id); cmd.Parameters.AddWithValue("@categoryid", newobj.Category_Id); if (newobj.Content_Id != 0) { // It a edit change request cmd.Parameters.AddWithValue("@contentid", newobj.Content_Id); } else { // add new cmd.Parameters.AddWithValue("@contentid", 0); } cmd.Parameters.AddWithValue("@contentname", newobj.Content_Name); cmd.Parameters.AddWithValue("@year", newobj.year); cmd.Parameters.AddWithValue("@userid", newobj.userid); cmd.Parameters.AddWithValue("@updatetime", DateTime.Now); cmd.Parameters.AddWithValue("@video", newobj.video_key); cmd.Parameters.AddWithValue("@wiki", newobj.wiki_key); cmd.Parameters.AddWithValue("@detail", newobj.detail); cmd.Parameters.AddWithValue("@reason", newobj.reason); if (newobj.Content_Id != 0) { //edit cmd.Parameters.AddWithValue("@type", 1); } else { //add new cmd.Parameters.AddWithValue("@type", 0); } cmd.Parameters.AddWithValue("@active", 1); cmd.Parameters.AddWithValue("@pictureid", newobj.picture_id); result = cmd.ExecuteNonQuery(); } conn.Close(); return(result); }
//not done //insert into the content table public int transfer(int crid) { conn.ConnectionString = mysqlconnection; if (conn.State != ConnectionState.Open) { conn.Open(); } var promte_changeR = new Change_Request(); var sqlcmd = @"select * from change_request where Change_Request_Id = @crid"; using (var cmd = new MySqlCommand(sqlcmd, conn)) { cmd.Parameters.AddWithValue("@crid", crid); using (MySqlDataReader rd = cmd.ExecuteReader()) { if (rd.Read()) { promte_changeR.Change_Request_Id = Convert.ToInt32(rd["Change_Request_Id"]); // not sure what happened, testing promte_changeR.Region_Id = (int)rd["Region_Id"]; promte_changeR.Category_Id = Convert.ToInt32(rd["Category"]); promte_changeR.Content_Id = Convert.ToInt32(rd["Content_Id"]); promte_changeR.Content_Name = rd["Content_Name"].ToString(); promte_changeR.year = Convert.ToInt32(rd["Year"]); promte_changeR.userid = Convert.ToInt32(rd["Update_User_Id"]); promte_changeR.Lastupdatetime = Convert.ToDateTime(rd["Last_Update_Date_Time"]); promte_changeR.video_key = rd["Video_Key"].ToString(); promte_changeR.wiki_key = rd["Wiki_Key"].ToString(); promte_changeR.detail = rd["Detail"].ToString(); promte_changeR.picture_id = Convert.ToInt32(rd["Picture_Id"]); promte_changeR.request_type = Convert.ToInt32(rd["Request_Type"]); } } } var result = 0; if (promte_changeR.request_type == 1) { //edit result = EditContent(promte_changeR); } else { //add new result = AddNewContent(promte_changeR); } RequestDeactivate(crid); return(result); }
public Change_Request GetOneChangeRequest(int crid) { conn.ConnectionString = mysqlconnection; if (conn.State != ConnectionState.Open) { conn.Open(); } 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 Change_Request_Id = @change_id "; Change_Request ci = new Change_Request(); using (var cmd = new MySqlCommand(sqlcmd, conn)) { cmd.Parameters.AddWithValue("@change_id", crid); using (MySqlDataReader rd = cmd.ExecuteReader()) { if (rd.Read()) { ci.Change_Request_Id = Convert.ToInt32(rd["Change_Request_Id"]); // not sure what happened, testing ci.Region_Id = (int)rd["Region_Id"]; ci.regionName = rd["Region_Name"].ToString(); ci.Category_Id = Convert.ToInt32(rd["Category_Id"]); ci.categoryName = rd["Category_Name"].ToString(); 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.Lastupdatetime = Convert.ToDateTime(rd["Last_Update_Date_Time"]); ci.video_key = rd["Video_Key"].ToString(); ci.wiki_key = rd["Wiki_Key"].ToString(); ci.detail = rd["Detail"].ToString(); ci.picture_id = Convert.ToInt32(rd["Picture_Id"]); ci.reason = (rd["Reason"]).ToString(); ci.picname = rd["Path"].ToString(); } } } conn.Close(); return(ci); }
public ActionResult AddOneChangeRequest(string ddlregionforCR, string ddlcategoryforCR, string contentname, string year, string reason, string detail, string wiki, string video, HttpPostedFileBase picfile) { var path = string.Empty; var userid = 1;//temp var picid = 0; if (picfile != null) { if (picfile.ContentLength > 0) { var filename = Path.GetFileName(picfile.FileName); path = Path.Combine(Server.MapPath("~/UploadPicture"), filename); picfile.SaveAs(path); picid = m.SavePicturePath(filename, userid); } } //if picid = 0 then user didn't upload a picture var newchange = new Change_Request(); newchange.Region_Id = Convert.ToInt32(ddlregionforCR); newchange.Category_Id = Convert.ToInt32(ddlcategoryforCR); newchange.picture_id = picid; newchange.year = Convert.ToInt32(year); newchange.video_key = video; newchange.wiki_key = wiki; newchange.Content_Name = contentname; newchange.detail = detail; newchange.reason = reason; newchange.picture_id = picid; var o = m.AddOneChangeRequest(newchange); return(RedirectToAction("Index")); }
public void EditOne(Change_Request newobj) { var o = m.EditOneChangeRequest(newobj); }