public List<String> Read_Comments()
{
SqlCommand st = new SqlCommand("SELECT body, gid, Users.username FROM Comments JOIN Users ON Comments.uid=Users.id");// JOIN Games ON Comments.gid=Games.id");
dbc c = new dbc(st);
return c.r(3);
}
public void Create_New_Comment(List<String> pms)
{
SqlCommand st = new SqlCommand("INSERT INTO Comments (id, body, uid, gid) VALUES (@id, @body, @uid, @gid)");
st.Parameters.Add(new SqlParameter("body", pms[0]));
st.Parameters.Add(new SqlParameter("uid", pms[1]));
st.Parameters.Add(new SqlParameter("gid", pms[2]));
st.Parameters.Add(new SqlParameter("id", Convert.ToInt32(Read_Max()[0])+1));
dbc c = new dbc(st);
c.cud();
}
private List<String> Read_Max()
{
dbc c = new dbc(new SqlCommand("SELECT max(id) FROM Comments"));
return c.r(1);
}
public void Update_User(String uid, String type)
{
SqlCommand st = new SqlCommand("UPDATE Users SET acctype=@type WHERE id=@uid");
st.Parameters.Add(new SqlParameter("uid", Convert.ToInt32(uid)));
st.Parameters.Add(new SqlParameter("type", type));
dbc c = new dbc(st);
c.cud();
}
public void Update_Password(List<String> pms)
{
SqlCommand st = new SqlCommand("UPDATE Users SET password=@pwd2 WHERE username=@un AND password=@pwd");
st.Parameters.Add(new SqlParameter("un", pms[0]));
st.Parameters.Add(new SqlParameter("pwd", pms[1]));
st.Parameters.Add(new SqlParameter("pwd2", pms[2]));
dbc c = new dbc(st);
c.cud();
}
public List<String> Read_Users()
{
SqlCommand st = new SqlCommand("SELECT username, email, id, acctype FROM Users");
dbc c = new dbc(st);
return c.r(4);
}
public List<String> Read_Login(String usr, String pwd)
{
SqlCommand st = new SqlCommand("SELECT id, username, acctype FROM Users WHERE username=@una AND password=@pwd");
st.Parameters.Add(new SqlParameter("una", usr));
st.Parameters.Add(new SqlParameter("pwd", pwd));
dbc c = new dbc(st);
return c.r(3);
}
public void Delete_User(String uid)
{
SqlCommand st = new SqlCommand("DELETE FROM Users WHERE id=@uid");
st.Parameters.Add(new SqlParameter("uid", Convert.ToInt32(uid)));
dbc c = new dbc(st);
c.cud();
}
public void Create_New_User(List<String> pms)
{
SqlCommand st = new SqlCommand("INSERT INTO Users VALUES (@id, @un, @pwd, 'regular', @mail)");
st.Parameters.Add(new SqlParameter("un", pms[0]));
st.Parameters.Add(new SqlParameter("pwd", pms[1]));
st.Parameters.Add(new SqlParameter("mail", pms[2]));
st.Parameters.Add(new SqlParameter("id", Convert.ToInt32(Read_Max()[0])+1));
dbc c = new dbc(st);
c.cud();
//new SqlCommand("SELECT count(id) FROM Users", myConnection)
}
public List<String> Read_Scores()
{
SqlCommand st = new SqlCommand("SELECT * FROM Scores");// JOIN Games ON Comments.gid=Games.id");
dbc c = new dbc(st);
return c.r(3);
}
public void Create_New_Score(List<String> pms)
{
SqlCommand st = new SqlCommand("INSERT INTO Scores VALUES(@id, @score, @uid, @gid)");
st.Parameters.Add(new SqlParameter("score", pms[0]));
st.Parameters.Add(new SqlParameter("uid", pms[1]));
st.Parameters.Add(new SqlParameter("gid", pms[2]));
st.Parameters.Add(new SqlParameter("id", Convert.ToInt32(Read_Max()[0]) + 1));
dbc c = new dbc(st);
c.cud();
}
public List<String> Read_Category(String kat)
{
SqlCommand st = new SqlCommand("SELECT * FROM Games WHERE kategorie=@kat");
st.Parameters.Add(new SqlParameter("kat", kat));
dbc c = new dbc(st);
return c.r(5);
}
public void Create_New_Game(List<String> pms)
{
SqlCommand st = new SqlCommand("INSERT INTO Games VALUES(@id, @tit, @desc, @kat, @cov)");
st.Parameters.Add(new SqlParameter("tit", pms[0]));
st.Parameters.Add(new SqlParameter("desc", pms[1]));
st.Parameters.Add(new SqlParameter("kat", pms[2]));
st.Parameters.Add(new SqlParameter("cov", pms[3]));
st.Parameters.Add(new SqlParameter("id", Convert.ToInt32(Read_Max()[0])+1));
dbc c = new dbc(st);
c.cud();
}