public PaginationQueryResult<News> GetNews(PaginationQueryCondition condition)
{
PaginationQueryResult<News> result = new PaginationQueryResult<News>();
string sql = "SELECT TOP " + condition.PageSize + " news.id, title, content, create_time, news.category_id, news_categories.name FROM news INNER JOIN news_categories ON (news_categories.id = news.category_id) ";
if (condition.CurrentPage > 1)
sql += " WHERE news.id<(SELECT MIN(id) FROM (SELECT TOP " + condition.PageSize * (condition.CurrentPage - 1) + " news.id FROM news INNER JOIN news_categories ON (news_categories.id = news.category_id) ORDER BY news.id DESC) AS D)";
sql += " ORDER BY news.id DESC; SELECT COUNT(*) FROM news INNER JOIN news_categories ON (news_categories.id = news.category_id)";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, null))
{
while (dr.Read())
{
News news = new News();
news.Id = dr.GetInt32(0);
news.Title = dr.GetString(1);
news.Content = dr.GetString(2);
news.CreateTime = dr.GetDateTime(3);
news.Category = new NewsCategory();
news.Category.Id = dr.GetInt32(4);
news.Category.Name = dr.GetString(5);
result.Results.Add(news);
}
dr.NextResult();
while (dr.Read())
{
result.TotalCount = dr.GetInt32(0);
}
}
return result;
}
public void CreateNews(News news)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputNVarcharParameter("@title", 100, news.Title),
SqlUtilities.GenerateInputParameter("@content", SqlDbType.Text, news.Content),
SqlUtilities.GenerateInputIntParameter("@category_id", news.Category.Id),
SqlUtilities.GenerateInputDateTimeParameter("@create_time", news.CreateTime),
SqlUtilities.GenerateInputParameter("@is_display", SqlDbType.Bit, news.IsDisplay)
};
string sql = "INSERT INTO news(title, content, category_id, create_time, is_display) VALUES (@title, @content, @category_id, @create_time, @is_display)";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public static void CreateNews(News news)
{
dal.CreateNews(news);
}
public static void UpdateNews(News news)
{
dal.UpdateNews(news);
}
public void UpdateNews(News news)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", news.Id),
SqlUtilities.GenerateInputNVarcharParameter("@title", 100, news.Title),
SqlUtilities.GenerateInputParameter("@content", SqlDbType.Text, news.Content),
SqlUtilities.GenerateInputIntParameter("@category_id", news.Category.Id),
SqlUtilities.GenerateInputDateTimeParameter("@create_time", news.CreateTime),
SqlUtilities.GenerateInputParameter("@is_display", SqlDbType.Bit, news.IsDisplay)
};
string sql = " UPDATE news SET title = @title, content = @content, category_id = @category_id, create_time = @create_time, is_display = @is_display WHERE id = @id";
SqlHelper.ExecuteNonQuery(CommandType.Text, sql, param);
}
public News GetNewsById(int id)
{
News news = null;
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", id)
};
string sql = "SELECT id, title ,content ,create_time, is_display, category_id FROM news WHERE id = @id";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
news = new News();
news.Id = dr.GetInt32(0);
news.Title = dr.GetString(1);
news.Content = dr.GetString(2);
news.CreateTime = dr.GetDateTime(3);
news.IsDisplay = dr.GetBoolean(4);
news.Category = new NewsCategory();
news.Category.Id = dr.GetInt32(5);
news.Category.Name = GetNewsCategoryById(news.Category.Id).Name;
}
}
return news;
}
public List<News> GetNewsByCategoryId(int catId)
{
SqlParameter[] param = new SqlParameter[] {
SqlUtilities.GenerateInputIntParameter("@id", catId)
};
List<News> result = new List<News>();
string sql = "SELECT news.id, title, content, create_time, news.category_id, news_categories.name FROM news INNER JOIN news_categories ON (news_categories.id = news.category_id) WHERE news.category_id = @id AND news.is_display='True' ORDER BY news.id DESC ";
using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sql, param))
{
while (dr.Read())
{
News news = new News();
news.Id = dr.GetInt32(0);
news.Title = dr.GetString(1);
news.Content = dr.GetString(2);
news.CreateTime = dr.GetDateTime(3);
news.Category = new NewsCategory();
news.Category.Id = dr.GetInt32(4);
news.Category.Name = dr.GetString(5);
result.Add(news);
}
}
return result;
}