Esempio n. 1
0
        /// <summary>
        /// Search Events
        /// </summary>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public List <NewsAndEventsItem> SearchEventsHeadlines(string searchKey)
        {
            List <NewsAndEventsItem> eventsList = new List <NewsAndEventsItem>();

            try
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM News_And_Events where Type = 2 AND Title like @SearchKey  ORDER BY  Id DESC; SELECT SCOPE_IDENTITY() ", mConnection);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@SearchKey", ("%" + searchKey + "%"));

                using (cmd)
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            NewsAndEventsItem news = new NewsAndEventsItem();
                            news.NewsId   = (int)reader.GetValue(reader.GetOrdinal("Id"));
                            news.Title    = reader.GetString(reader.GetOrdinal("Title"));
                            news.NewsDate = (DateTime)reader.GetValue(reader.GetOrdinal("Date"));
                            eventsList.Add(news);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(eventsList);
        }
Esempio n. 2
0
        /// <summary>
        /// Get Events Headiles according to the provided page size and page index
        /// </summary>
        /// <param name="pageSize">required page size</param>
        /// <param name="pageIndex">required page index</param>
        /// <returns>Events list related to required page</returns>
        public List <NewsAndEventsItem> GetEventsHeadlines(int pageSize, int pageIndex)
        {
            List <NewsAndEventsItem> eventsList = new List <NewsAndEventsItem>();

            try
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM  (SELECT ROW_NUMBER() OVER(ORDER BY Id desc) as rownum,Id, Title, Date,Type FROM News_And_Events where Type = 2) as news WHERE rownum > (@pageNumber * @pageSize) AND rownum <= ((@pageNumber * @pageSize) + @pageSize) ORDER BY  Id DESC; SELECT SCOPE_IDENTITY() ", mConnection);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@pageSize", pageSize);
                cmd.Parameters.AddWithValue("@pageNumber", pageIndex);

                using (cmd)
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            NewsAndEventsItem news = new NewsAndEventsItem();
                            news.NewsId   = (int)reader.GetValue(reader.GetOrdinal("Id"));
                            news.Title    = reader.GetString(reader.GetOrdinal("Title"));
                            news.NewsDate = (DateTime)reader.GetValue(reader.GetOrdinal("Date"));
                            eventsList.Add(news);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(eventsList);
        }
Esempio n. 3
0
        /// <summary>
        /// Get news or event details
        /// </summary>
        /// <param name="id">News or Event id</param>
        /// <returns>News or Event details</returns>
        public NewsAndEventsItem GetNewsOrEventDetails(int id)
        {
            NewsAndEventsItem newsAndEvent = new NewsAndEventsItem();

            try
            {
                SqlCommand cmd = new SqlCommand("select t2.Id,t2.Date,t1.NewsAndEventBody,t2.Title from (select * from dbo.News_And_Event_Details where Id = @Id) t1 INNER JOIN (select * from dbo.News_And_Events where Id = @Id)  t2 ON t1.Id = t2.Id; SELECT SCOPE_IDENTITY() ", mConnection);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@Id", id);

                using (cmd)
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            newsAndEvent.NewsId   = (int)reader.GetValue(reader.GetOrdinal("Id"));
                            newsAndEvent.Title    = reader.GetString(reader.GetOrdinal("Title"));
                            newsAndEvent.NewsBody = reader.GetString(reader.GetOrdinal("NewsAndEventBody"));
                            newsAndEvent.NewsDate = (DateTime)reader.GetValue(reader.GetOrdinal("Date"));
                            break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(newsAndEvent);
        }