Example #1
0
        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;
        }
Example #2
0
 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);
 }
Example #3
0
 public static void CreateNews(News news)
 {
     dal.CreateNews(news);
 }
Example #4
0
 public static void UpdateNews(News news)
 {
     dal.UpdateNews(news);
 }
Example #5
0
 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);
 }
Example #6
0
        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;
        }
Example #7
0
        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;
        }