/// <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);
}
/// <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);
}
/// <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);
}