public PositiveAndNegativeReturn PN(int id) { PositiveAndNegativeReturn pn = new PositiveAndNegativeReturn(); string sql = $@" SELECT * FROM PrincessConnect WHERE Id = N'{id}' "; try { conn.Open(); //開啟DB連線 SqlCommand cmd = new SqlCommand(sql, conn); //取得Sql資料 SqlDataReader dr = cmd.ExecuteReader(); //SqlDataReader 的預設位置是在第一筆記錄之前。 因此,您必須呼叫 Read 才能開始存取任何資料。 if (dr.Read()) { pn.positive = Convert.ToInt32(dr["positive"]); pn.negative = Convert.ToInt32(dr["negative"]); } } catch (Exception s) { Console.WriteLine(s.Message); } finally { conn.Close(); } return(pn); }
public ActionResult NegativeEdit(PrincessConnect p, Members m) { PositiveAndNegativeReturn returnPandN = new PositiveAndNegativeReturn(); returnPandN = PrincessConnectDBService.SearchNegative(p, m); string json = JsonConvert.SerializeObject(returnPandN); return(Json(json)); }
public ActionResult bookIDGetData(int i) { PositiveAndNegativeReturn pn = new PositiveAndNegativeReturn(); pn = PrincessConnectDBService.PN(i); string json = JsonConvert.SerializeObject(pn); return(Json(json)); }
public PositiveAndNegativeReturn SearchNegative(PrincessConnect p, Members m) {//這邊一律處理使用者點讚這個動作,沒有=>新增到關聯表,使用者以經典過讚了=>從關聯表移除這列,PCR表該行讚減一 //使用者是點倒讚=>更新關聯表倒讚變讚, string sql = $@" SELECT * FROM MandP WHERE MPAccount = N'{m.Account}' AND PID = N'{p.Id}'"; PositiveAndNegativeReturn Data = new PositiveAndNegativeReturn(); MandP mData = new MandP(); try { conn.Open(); //開啟DB連線 SqlCommand cmd = new SqlCommand(sql, conn); //取得Sql資料 SqlDataReader dr = cmd.ExecuteReader(); //SqlDataReader 的預設位置是在第一筆記錄之前。 因此,您必須呼叫 Read 才能開始存取任何資料。 if (dr.Read()) { mData.MPAccount = dr["MPAccount"].ToString(); mData.PID = Convert.ToInt32(dr["PID"]); mData.status = dr["status"].ToString(); if (mData.status.Equals("n")) { dr.Close(); new SqlCommand($@" DELETE FROM MandP WHERE MPAccount = N'{m.Account}' AND PID = N'{mData.PID}' AND status = N'{'n'}'", conn).ExecuteNonQuery(); SqlDataReader drTemp = new SqlCommand($@" SELECT * FROM PrincessConnect WHERE Id = N'{p.Id}'", conn).ExecuteReader(); if (drTemp.Read()) { int negativeCount = Convert.ToInt32(drTemp["negative"]); drTemp.Close(); new SqlCommand($@" UPDATE PrincessConnect SET negative = N'{negativeCount - 1}' WHERE Id='{p.Id}'", conn).ExecuteNonQuery(); Data.negative = negativeCount - 1; } } else if (mData.status.Equals("p")) { dr.Close(); new SqlCommand($@" UPDATE MandP SET status = '{'n'}' WHERE MPAccount = N'{m.Account}' AND PID = N'{p.Id}'", conn).ExecuteNonQuery(); SqlDataReader drTemp = new SqlCommand($@" SELECT * FROM PrincessConnect WHERE Id = N'{p.Id}'", conn).ExecuteReader(); if (drTemp.Read()) { int positiveCount = Convert.ToInt32(drTemp["positive"]); int negativeCount = Convert.ToInt32(drTemp["negative"]); drTemp.Close(); new SqlCommand($@" UPDATE PrincessConnect SET positive = N'{positiveCount - 1}' , negative = N'{negativeCount + 1}' WHERE Id='{p.Id}'", conn).ExecuteNonQuery(); Data.positive = positiveCount - 1; Data.negative = negativeCount + 1; } } } else { dr.Close(); string insertSql = $@" INSERT INTO MandP(MPAccount,PID,status) VALUES( N'{m.Account}',N'{p.Id}',N'{'n'}' ); "; new SqlCommand(insertSql, conn).ExecuteNonQuery(); SqlDataReader drTemp = new SqlCommand($@" SELECT * FROM PrincessConnect WHERE Id = N'{p.Id}'", conn).ExecuteReader(); if (drTemp.Read()) { int negativeCount = Convert.ToInt32(drTemp["negative"]); drTemp.Close(); new SqlCommand($@" UPDATE PrincessConnect SET negative = N'{negativeCount + 1}' WHERE Id='{p.Id}'", conn).ExecuteNonQuery(); Data.negative = negativeCount + 1; } } } catch (Exception s) { Console.WriteLine(s.Message); } finally { conn.Close(); } return(Data); }