public List <MandP> PositiveAndNegativeSearch(Members m) { List <MandP> mp = new List <MandP>(); string sql = $@" SELECT * FROM MandP WHERE MPAccount = N'{m.Account}'"; try { conn.Open(); //開啟DB連線 SqlCommand cmd = new SqlCommand(sql, conn); //取得Sql資料 SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { MandP mData = new MandP(); mData.MPAccount = dr["MPAccount"].ToString(); mData.PID = Convert.ToInt32(dr["PID"]); mData.status = dr["status"].ToString(); mp.Add(mData); } } catch (Exception s) { Console.WriteLine(s.Message); } finally { conn.Close(); } return(mp); }
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); }