Making your site's content date-searchable with an ASP.NET Calendar control
Last Friday before I got on the newsdesk to do my broadcast of the evening news, I had an epiphany. Last month, I finally got around to doing something I've been meaning to do for months - add full-text search capabilities to the news database for my company's site. While this has exponentially improved the user experience people have in interacting with us, and has significantly increased traffic - it still wasn't perfect.
And right before I went on the air, the notion of connecting my site's news articles with a Calendar control hit me. Simple, easy to use, and instantly effective. I was able to hammer out the code and get a working search page going in a few hours, and deployed the new service shortly thereafter, to rave reviews from my users.
We're the type of company that publishes anywhere from 16-25 new stories online daily, so this is the perfect case for daily searchability - we publish often and in enough volume to warrant such an application. Here's the code that shows how I did it, and note the following features:
- the Calendar control uses startDate and endDate variables of type DateTime that set a minimum and maximum range of dates wherein users can click (days without stories or days that have yet to pass shouldn't be accessible). This is all handled in the Calendar's OnDayRender event. If a user happens to click on a day that's within the acceptable range but for some reason doesn't have any content, in which case an IndexOutOfRangeException would be thrown, a friendly error message is presented, saying there's no data for that day.
- the code builds multiple Repeater controls programmatically and adds them to a PlaceHolder control on the page, rather than setting Repeaters declaratively. This is done by instantiating objects of a custom class that derives from the ITemplate interface. I use this type of construct a lot in data-centric programming, and it's based off of the must-read example by Nikhil Kothari and Mike Pope on MSDN on the subject.
- content is presented for both single days and for 7-day week ranges, depending on what link within the Calendar the user clicked (an individual date or a week range). The data is accessed via a stored procedure that conditionally returns data for as many days was requested (1 or 7). When the SPROC returns a week's worth of stories, it actually executes a batch T-SQL statement that returns 7 recordsets. This collection is iterated over and bound to the Repeaters within the page.
- each day's (or week's) content is cached via an entry in ASP.NET's Cache API. This eliminates the inevitable task of repeated database calls for the same data. Because this type of thing will inevitably add up, I set the Cache to expire the entry in 20 minutes, and set the CacheItemPriority enumeration to CacheItemPriority.Low to keep things moderate, in terms of memory usage.
PAGE CODE
-----------
<%@ Page Language="C#" ClientTarget="ie5" ContentType="text/html" Trace="false" Debug="false" EnableSessionState="False" EnableViewState="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
DateTime startDate = new DateTime(2004,2,1);
DateTime endDate = DateTime.Now;
private void LimitVisibleDays(object sender, DayRenderEventArgs e)
{
if(e.Day.Date < startDate || e.Day.Date > endDate)
e.Day.IsSelectable = false;
}
private void SelectedDate_Changed(object sender, EventArgs e)
{
DataSet ds = new DataSet();
if(ViewState["CurrentMonth"] != null)
Rundown.VisibleDate = (DateTime)ViewState["CurrentMonth"];
// get a count of the number of days selected to determine if the user picked a single day or a week range
if(Rundown.SelectedDates.Count > 1)
{
if(Cache["DataCalendar-"+Rundown.SelectedDate.ToShortDateString()+"-MULTIDAY"] == null)
{
DataSet cachedData = GetStoryResultSet(true,Rundown.SelectedDate.ToShortDateString()); // the user selected a date range (an entire week)
Cache.Insert("DataCalendar-"+Rundown.SelectedDate.ToShortDateString()+"-MULTIDAY",cachedData,null,DateTime.Now.AddMinutes(20),Cache.NoSlidingExpiration,CacheItemPriority.Low,null);
}
ds = (DataSet)Cache["DataCalendar-"+Rundown.SelectedDate.ToShortDateString()+"-MULTIDAY"];
}
else
{
if(Cache["DataCalendar-"+Rundown.SelectedDate.ToShortDateString()+"-SINGLEDAY"] == null)
{
DataSet cachedData = GetStoryResultSet(false,Rundown.SelectedDate.ToShortDateString()); // the user selected a single day
Cache.Insert("DataCalendar-"+Rundown.SelectedDate.ToShortDateString()+"-SINGLEDAY",cachedData,null,DateTime.Now.AddMinutes(20),Cache.NoSlidingExpiration,CacheItemPriority.Low,null);
}
ds = (DataSet)Cache["DataCalendar-"+Rundown.SelectedDate.ToShortDateString()+"-SINGLEDAY"];
}
try
{
pnlStoryArchive.Visible = true;
lblNoRecords.Visible = false;
for(int i=0;i<ds.Tables.Count;i++)
{
Repeater r = new Repeater();
r.DataSource = ds.Tables[i].DefaultView;
r.HeaderTemplate = new DailyNewsStories(ListItemType.Header,Convert.ToDateTime(ds.Tables[i].Rows[0]["StoryDate"]));
r.ItemTemplate = new DailyNewsStories(ListItemType.Item);
r.AlternatingItemTemplate = new DailyNewsStories(ListItemType.AlternatingItem);
r.FooterTemplate = new DailyNewsStories(ListItemType.Footer);
r.DataBind();
placeDays.Controls.Add(r);
}
}
catch(IndexOutOfRangeException ex)
{
pnlStoryArchive.Visible = false;
lblNoRecords.Visible = true;
lblNoRecords.Text = "There are no entries for " + Rundown.SelectedDate.ToLongDateString();
}
}
private void Keep_Months_Visible(object sender,MonthChangedEventArgs e)
{
// retain the currently-selected date in the Calendar control so the user won't have to cycle there again after selecting a date
ViewState["CurrentMonth"] = e.NewDate;
}
private DataSet GetStoryResultSet(bool IsMultipleDayQuery, string datePicked)
{
SqlConnection conn = new SqlConnection("server=localhost;database=<YOUR-DB>;uid=sa;pwd=pwd;enlist=false;");
SqlDataAdapter da = new SqlDataAdapter("GetCalendarStories",conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add(new SqlParameter("@QueryDate",SqlDbType.SmallDateTime)).Value = Convert.ToDateTime(datePicked);
da.SelectCommand.Parameters.Add(new SqlParameter("@IsMultipleDayQuery",SqlDbType.Bit)).Value = IsMultipleDayQuery;
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
conn.Close();
}
catch(Exception ex)
{
return null;
}
finally
{
if (conn != null || conn.State == ConnectionState.Open)
conn.Close();
}
return ds;
}
/* CLASS TO CREATE REPEATER CONTROL TEMPLATES PROGRAMMATICALLY */
public class DailyNewsStories : ITemplate
{
// data members
private ListItemType _item;
private DateTime _dateHeader;
// overloaded constructor
public DailyNewsStories(ListItemType item,DateTime dateHeader)
{
this._item = _item;
this._dateHeader = dateHeader;
}
public DailyNewsStories(ListItemType item)
{
this._item = item;
}
// implement the sole interface method
public void InstantiateIn(Control container)
{
Literal lit = new Literal();
switch(this._item)
{
case ListItemType.Header:
lit.Text = "<table style=\"padding-bottom:35px;\" width=\"100%\" cellpadding=\"11\" cellspacing=\"3\" border=\"0\"><tr>\n\t\t<th align=\"left\"><hr style=\"padding-top:35px;\" size=\"1\" align=\"left\" width=\"85%\" color=\"#000080\"/>" + string.Format("{0:D}",this._dateHeader) + "\n\t\t</th>\n\t\t</tr>";
break;
case ListItemType.Item:
lit.Text = "\n\t\t<tr style=\"background-color:#f1f1f1;font-size:7pt;\">\n\t\t\t<td align=\"left\"><b><a href=\"http://www.yoursite.com/detail_page.aspx?id=";
lit.DataBinding += new EventHandler(lit_DataBinding);
break;
case ListItemType.AlternatingItem:
lit.Text = "\n\t\t<tr style=\"background-color:#ffffff;font-size:7pt;\">\n\t\t\t<td align=\"left\"><b><a href=\"http://www.yoursite.com/detail_page.aspx?id=";
lit.DataBinding += new EventHandler(lit_DataBinding);
break;
case ListItemType.Footer:
lit.Text = "\n\t\t</table>";
break;
}
// add the control to the Control tree
container.Controls.Add(lit);
}
private void lit_DataBinding(object sender,EventArgs e)
{
Literal lit = (Literal)sender;
RepeaterItem container = (RepeaterItem)lit.NamingContainer;
lit.Text += DataBinder.Eval(container.DataItem,"StoryID") + "\">" + DataBinder.Eval(container.DataItem,"StoryTitle") + "</a></b><br/>" + DataBinder.Eval(container.DataItem,"StoryBody") + "</td>\n\t\t</tr>" : "</td>\n\t\t</tr>";
}
}
</script>
<html>
<body>
<form runat="server">
<body style="font-family:Verdana;font-size:9pt;color:#000000;">
<center><b><font size="5">Search our archive by date</font></b>
<br />
Select a date from the calendar below to see that day's stories
<hr size="1" color="#000080" width="85%" align="center" />
</center>
<br />
<asp:calendar id="Rundown" selectionmode="DayWeek" selectweektext="Select entire week" onvisiblemonthchanged="Keep_Months_Visible" onselectionchanged="SelectedDate_Changed" ondayrender="LimitVisibleDays" showtitle="true" runat="server" firstdayofweek="Monday" borderwidth="2px" backcolor="white" width="300px" font-size="7pt" height="180px" font-names="Verdana" bordercolor="#999999" borderstyle="outset" daynameformat="FirstLetter" cellpadding="4">
<todaydaystyle backcolor="#cccccc" forecolor="white" />
<selectorstyle backcolor="#cccccc" />
<nextprevstyle verticalalign="Bottom" />
<dayheaderstyle font-size="7pt" font-bold="true" backcolor="#cccccc" />
<selecteddaystyle font-bold="true" forecolor="white" backcolor="#666666" />
<titlestyle font-bold="true" bordercolor="black" backcolor="#999999" />
<weekenddaystyle backcolor="#ffffcc" />
<othermonthdaystyle forecolor="#808080" />
</asp:calendar>
<br />
<asp:label id="lblNoRecords" forecolor="red" runat="Server" />
<asp:panel id="pnlStoryArchive" runat="Server" visible="false">
<asp:Placeholder id="placeDays" runat="server" />
</asp:panel>
</body>
</form>
</body>
</html>
STORED PROCEDURE
CREATE PROCEDURE GetCalendarStories
(
@QueryDate SMALLDATETIME,
@IsMultipleDayQuery BIT = 0
)
AS
DECLARE @incrementor INT
SET @incrementor = 1
IF @IsMultipleDayQuery = 1
BEGIN
-- get the first story for the latest day in the week
SELECT StoryID,StoryTitle,StoryBody FROM StoryTable WHERE StoryDate >= @QueryDate AND StoryDate < @QueryDate + 1;
-- get the remaining stories for the week, counting forwards
WHILE @incrementor <= 6
BEGIN
SET @QueryDate = @QueryDate + 1
SELECT StoryID,StoryTitle,StoryBody FROM StoryTable WHERE StoryDate >= @QueryDate AND StoryDate < @QueryDate + 1;
SET @incrementor = @incrementor + 1
END
END
ELSE
BEGIN
SELECT StoryID,StoryTitle,StoryBody FROM StoryTable WHERE StoryDate >= @QueryDate AND StoryDate < @QueryDate + 1;
END
GO