public static DataTable getAccountByUsername(string username)
{
string sql = " select * from Account where [Account].username = @username";
SqlParameter p1 = new SqlParameter("@username", SqlDbType.VarChar);
p1.Value = username;
return(DAO.GetDataBySQLWithParameters(sql, p1));
}
public static DataTable getPostContentAndImageByPostID(int id)
{
string sql = "select [Post].content, [Post].image from [Post] where postid = @id ";
SqlParameter p1 = new SqlParameter("@id", SqlDbType.Int);
p1.Value = id;
return(DAO.GetDataBySQLWithParameters(sql, p1));
}
public static DataTable getPostContentAndImageByPostID22(int id)
{
string sql = "select [Post].content, [Post].image, [Account].username, [Post].title from [Post], " +
"[Account] where [Post].writtername = [Account].username and postid = @id";
SqlParameter p1 = new SqlParameter("@id", SqlDbType.Int);
p1.Value = id;
return(DAO.GetDataBySQLWithParameters(sql, p1));
}
public static DataTable getAllAccountByNameAndIsWriter(string username, int isWriter)
{
string sql = @" select * from Account where [Account].isAdmin = 0 and [Account].username like N'%" + username + "%' ";
if (isWriter != 0)
{
sql += " and [Account].isWriter = @writer";
SqlParameter p1 = new SqlParameter("@writer", SqlDbType.Int);
p1.Value = isWriter;
return(DAO.GetDataBySQLWithParameters(sql, p1));
}
return(DAO.GetDataBySQL(sql));
}
public static DataTable getAllPostsByWriter(string writername)
{
string sql = @"select [Post].postid, [Account].username, [Category].categoryname, [Post].isApprove, " +
" [Platform].platformname, [Post].title, [Post].content, [Post].image, " +
" [Post].upvote, [Post].datecreated " +
" from Post, Category, [Platform], [Account] " +
" where [Post].categoryid = [Category].categoryid and [Post].platformid = [Platform].platformid " +
" and [Post].writtername = [Account].username and [Post].isApprove = 1 and [Post].writtername = @name ";
SqlParameter p1 = new SqlParameter("@name", SqlDbType.VarChar);
p1.Value = writername;
return(DAO.GetDataBySQLWithParameters(sql, p1));
}
public static DataTable getPostByTitileCategoryPlatformAndDatePostAndAprrove(int categoryid, int platformid, DateTime from,
DateTime to, string title, int isApprove, string writername)
{
string sql = @"select [Post].postid, [Account].username, [Category].categoryname, " +
" [Platform].platformname, [Post].title, [Post].content, [Post].image, " +
" [Post].upvote, [Post].datecreated " +
" from Post, Category, [Platform], [Account] " +
" where [Post].categoryid = [Category].categoryid and [Post].platformid = [Platform].platformid " +
" and [Post].writtername = [Account].username and [Post].isApprove = " + isApprove + " and [Post].datecreated between @from and @to " +
" and [Post].title like N'%" + title + "%' and [Post].writtername = '" + writername + "'";
SqlParameter p1 = new SqlParameter("@from", SqlDbType.DateTime);
p1.Value = from;
SqlParameter p2 = new SqlParameter("@to", SqlDbType.DateTime);
p2.Value = to;
if (categoryid != 0)
{
sql += " and [Post].categoryid = @categoryid ";
SqlParameter p3 = new SqlParameter("@categoryid", SqlDbType.Int);
p3.Value = categoryid;
if (platformid != 0)
{
sql += " and [Post].platformid = @platformid ";
SqlParameter p4 = new SqlParameter("@platformid", SqlDbType.Int);
p4.Value = platformid;
return(DAO.GetDataBySQLWithParameters(sql, p1, p2, p3, p4));
}
return(DAO.GetDataBySQLWithParameters(sql, p1, p2, p3));
}
else
{
if (platformid != 0)
{
sql += " and [Post].platformid = @platformid ";
SqlParameter p4 = new SqlParameter("@platformid", SqlDbType.Int);
p4.Value = platformid;
return(DAO.GetDataBySQLWithParameters(sql, p1, p2, p4));
}
}
return(DAO.GetDataBySQLWithParameters(sql, p1, p2));
}