public static DataTable getAreabyID(string id)
{
string sql = @"Select * from Area where id = @id";
SqlParameter param1 = new SqlParameter("@id", SqlDbType.NVarChar);
param1.Value = id;
return(DAO.GetDataBySQLWithParameters(sql, param1));
}
public static DataTable getTeambyArea(string id)
{
string sql = @"Select * from Team where AreaID = @id order by score desc";
SqlParameter param1 = new SqlParameter("@id", SqlDbType.NVarChar);
param1.Value = id;
return(DAO.GetDataBySQLWithParameters(sql, param1));
}
public static DataTable getCountRow(int topic)
{
string query = "Select count(postID) from Post where topicID = @topic";
SqlParameter param1 = new SqlParameter("@topic", SqlDbType.Int);
param1.Value = topic;
return(DAO.GetDataBySQLWithParameters(query, param1));
}
public static DataTable getComment(int postID)
{
string query = "Select * from Comment where postID = @postID";
SqlParameter param1 = new SqlParameter("@postID", SqlDbType.Int);
param1.Value = postID;
return(DAO.GetDataBySQLWithParameters(query, param1));
}
public static DataTable getAllCountRow(string s)
{
string query = "Select count(postID) from Post where title like '%' + @title + '%' ";
SqlParameter param1 = new SqlParameter("@title", SqlDbType.NVarChar);
param1.Value = s;
return(DAO.GetDataBySQLWithParameters(query, param1));
}
public static DataTable getTop3Post(int topic)
{
string sql = @"SELECT TOP 3 * from Post where TopicID= @topic order by date desc";
SqlParameter param1 = new SqlParameter("@topic", SqlDbType.Int);
param1.Value = topic;
return(DAO.GetDataBySQLWithParameters(sql, param1));
}
public static DataTable GetAccountByRole(string s)
{
string sql = @"select id,username, password,email, role from Account where role = @role";
SqlParameter param1 = new SqlParameter("@role", SqlDbType.NChar);
param1.Value = s;
return(DAO.GetDataBySQLWithParameters(sql, param1));
}
public static DataTable getPostbyTopic(int s)
{
string sql = @"select postID, title, shortDesc,longDesc,imgLink,date, Topic.TopicName,writer from Post,Account,Topic
where Post.writer = Account.id and Post.TopicID = Topic.ID and topicID=@topic";
SqlParameter param1 = new SqlParameter("@topic", SqlDbType.Int);
param1.Value = s;
return(DAO.GetDataBySQLWithParameters(sql, param1));
}
public static DataTable Login(string id, string password, string role)
{
string sql = @"select * from Account where id = @id and password =@password and role = @role";
SqlParameter param1 = new SqlParameter("@id", SqlDbType.NVarChar);
param1.Value = id;
SqlParameter param2 = new SqlParameter("@password", SqlDbType.NVarChar);
param2.Value = password;
SqlParameter param3 = new SqlParameter("@role", SqlDbType.NChar);
param3.Value = role;
return(DAO.GetDataBySQLWithParameters(sql, param1, param2, param3));
}
public static DataTable getPost(string s, DateTime from, DateTime to)
{
string sql = @"select postID, title, shortDesc,longDesc,imgLink,date,Topic.ID ,Topic.TopicName,writer from Post,Account,Topic
where Post.writer = Account.id and Post.TopicID = Topic.ID and writer = @name and date between @from and @to";
SqlParameter param1 = new SqlParameter("@name", SqlDbType.NChar);
param1.Value = s;
SqlParameter param2 = new SqlParameter("@from", SqlDbType.Date);
param2.Value = from;
SqlParameter param3 = new SqlParameter("@to", SqlDbType.Date);
param3.Value = to;
return(DAO.GetDataBySQLWithParameters(sql, param1, param2, param3));
}
public static DataTable getPostbySearchPaging(string s, int index, int pageSize)
{
string sql = @"select * from (select ROW_NUMBER() over(order by postID desc) as rn,
postID, title, shortDesc,longDesc,imgLink,date, Topic.TopicName,writer from Post,Account,Topic
where Post.writer = Account.id and Post.TopicID = Topic.ID and title like '%' + @title + '%')as x where rn between
@pageIndex*@pageSize-(@pageSize-1) and @pageIndex*@pageSize";
SqlParameter param1 = new SqlParameter("@title", SqlDbType.NVarChar);
param1.Value = s;
SqlParameter param2 = new SqlParameter("@pageIndex", SqlDbType.Int);
param2.Value = index;
SqlParameter param3 = new SqlParameter("@pageSize", SqlDbType.Int);
param3.Value = pageSize;
return(DAO.GetDataBySQLWithParameters(sql, param1, param2, param3));
}