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);
}
