Introduction
SharePoint Calendar Lists not only offer standard “1:00-2:30 pm” type entries, but also entries that span multiple days, and all-day items, which have no set start or end time. Retrieving this data means learning something about CAML queries and SharePoint gotchas – and I hope this article saves you some time.
Overview
The “sliding window” calendar web part shows data for 1- 7 contiguous days. The number of days displayed can be configured via the tool pane. If the web part is set to show 3 days, you’ll see today, tomorrow, and the day after that. For example, if today is Wednesday, you’ll see Wednesday-Thursday-Friday; on Thursday, the window “slides” to Thursday-Friday-Saturday. The goal is to display a lot of information in a small amount of space. To that end, only the title of each item is listed and hyperlinked - and when the mouse hovers over an item, a tooltip displays the start and end times, title, location, and description.
Retrieving SharePoint list data
using (SPWeb web = SPContext.Current.Site.OpenWeb(listCalendarUrl))
{
SPList list = null;
try { list = web.Lists[listCalendar]; }
catch { }
if (null == list)
return;
DateTime[] weeks;
if (((int)StartDay + NumberOfDays) > 7)
weeks = new DateTime[] { StartDate, StartDate.AddDays(7) };
else
weeks = new DateTime[] { StartDate };
If the sliding window spans the weekend, then we’ll need to look at two weeks’ worth of items, so we’ve stored our target days in the “weeks” array, and we’ll create as many queries as we need.
foreach (DateTime day in weeks)
{
SPQuery queryRecurring = new SPQuery();
queryRecurring.ExpandRecurrence = true;
queryRecurring.CalendarDate = day;
queryRecurring.Query = "<Where><DateRangesOverlap>"
+ "<FieldRef Name=\"EventDate\" /><FieldRef Name=\"EndDate\" />" +
"<FieldRef Name=\"RecurrenceID\" />"
+ "<Value Type=\"DateTime\"><Week />" +
"</Value></DateRangesOverlap></Where>";
SPListItemCollection listItems;
listItems = list.GetItems(queryRecurring);
CAML – Collaborative Application Markup Language.
CAML queries are used to retrieve SharePoint data. They look a bit like SQL queries expressed in XML, but in a prefix notation. For schema information on CAML queries, try: Query Schema, and for help with building queries, try: U2U CAML Query Builder and Execution Tool Released.
Gotcha: In CAML queries, you must use the name of the column, not the display name, and the real name can be hard to find, especially, if you renamed any columns. To find column names for any SharePoint list, open the list, edit the list settings, and scroll down until you find the list of columns.
Click on a column name to view its properties:
In the textbox at the right, the column name is “Start Time” - but that is the display name. The real column name is at the end of the URL(!). Look at the rightmost bit of the address bar to see “Field=EventDate”; and “EventDate” is the name we want.
Back to the CAML query. Recurring events … recur. For example, a seminar that occurs every week for 10 weeks, from 2 – 3 pm on Thursdays. These list items are created at runtime, not stored in the database (like an ordinary event), and a standard query will not return them. The DateRangesOverlap element must be used to find recurring events; it will return everything for the specified interval.
In this example, we’re choosing to look at one week at a time. Let’s take another look at that CAML query in an easier-to-read format:
In the FieldRef elements of the query (which use the real column name), EventDate is the start date, EndDate the end date, and RecurranceID is not only the ID you’ll need for the hyperlink, but contains the start day and the time of the recurring event. An example of a RecurranceID for a recurring event: “4.0.2008-05-27T17:00:00Z”. In contrast, an example of a RecurranceID for an ordinary event: “7”.
A couple of attributes need to be set on the query also:
queryRecurring.ExpandRecurrence = true;
queryRecurring.CalendarDate = day;
Set ExpandRecurrence to true so the recurring values get created. The CalendarDate property sets the actual date, which, combined with the CAML query’s “Week” value, will cause the query to grab all the entries in the week containing the “CalendarDate”.
Processing the list items collection
Now that we have the item collection, we need to figure out if each item is an all-day, multi-day, or ordinary item; check if the item’s start date is in our date range, and if so, add it to our sorted list, calendarCells.
If it is a multi-day item, there will be only one occurrence, and we’ll need to add the other days manually; but, there may be one each week if the time span bridges the weekend. This raises the possibility of accidentally adding the same items twice, so we’ll save the uniqueId property in the Hashtable, processedListItems, so we can screen for already-added items. Each “cell” in the sorted list, calendarCells, will be 1 hour of a day.
bool multiday;
bool allday;
foreach (SPListItem item in listItems)
{
DateTime itemStartDate = (DateTime)item["EventDate"];
DateTime itemEndDate = (DateTime)item["EndDate"];
multiday = (itemEndDate - itemStartDate >
new TimeSpan(1, 0, 0, 0)) ? true : false;
allday = (itemEndDate - itemStartDate ==
new TimeSpan(23, 59, 0)) ? true : false;
if (multiday)
if (day >= itemStartDate && day <= itemEndDate)
itemStartDate = day;
if (!ItemIsInDateRange(itemStartDate, day, weeks))
continue;
string key = string.Empty;
if (multiday || allday)
key = AllDayHour + "-" +
((int)itemStartDate.DayOfWeek).ToString();
else
{
key = itemStartDate.TimeOfDay.Hours.ToString() + "-" +
((int)itemStartDate.DayOfWeek).ToString();
if (key.Length < 4)
key = "0" + key;
}
string titleText = string.Empty;
if (multiday || allday)
titleText = item["Title"].ToString();
else
titleText = itemStartDate.ToShortTimeString() +
" " + item["Title"].ToString();
string linkUrl = web.Url + "/"
+ item.Url.ToString().Substring(0, item.Url.LastIndexOf("/") + 1)
+ "DispForm.aspx?ID=" + item.RecurrenceID.ToString();
string titleLink = "<a href=" + Quote(linkUrl)
+ " title=" + Quote(tip(item, multiday || allday)) +
" >" + titleText + "</a>";
string UniqueID = item.UniqueID + item.RecurrenceID;
if (!processedListItems.Contains(UniqueID))
{
AddCell(key, titleLink);
if (multiday)
{
DateTime thisEndDate = (StartDate.AddDays(NumberOfDays) <
itemEndDate) ? StartDate.AddDays(NumberOfDays) : itemEndDate;
for (DateTime nextDay = itemStartDate.AddDays(1);
nextDay <= thisEndDate; nextDay = nextDay.AddDays(1))
{
if (ItemIsInDateRange(nextDay))
{
key = AllDayHour + "-" +
((int)nextDay.DayOfWeek).ToString();
AddCell(key, titleLink);
}
}
}
processedListItems.Add(UniqueID, null);
}
}
}
}
}
Last bits
You’ll notice that tooltips (or screentips) here are handled by assigning them to the title attribute of the <a>tags, and there are commented-out lines nearby assigning JavaScript functions to the onmouseover and onmouseout events. If you prefer to use JavaScript tooltips, just un-comment these lines, and also the script-registering code in the OnLoad method, and a few lines in the tip method. (Don’t forget to upload the JavaScript file to the web server.) With JavaScript tooltips, you can include images, links, etc., in the tooltips.
Once the sorted list is built, we’re ready to build a DataTable, add matching columns to a GridView, and set the GridView’s DataSource to the table. A naming convention is used to tie the DataTable columns to the GridView columns. The Gridview columns are template fields, so HTML can be used.
One of the challenges of web part design is that there is no design surface, so you really can’t “see” what you’re doing. I found it very helpful to preview the GridView in a separate project -a sort of workbench- where I could just throw things on a design surface, set properties, play with the code, and spin it up fast.
Coda
Note: WebPart deployment is not covered here; for an introduction, see: Write Custom WebParts for SharePoint 2007.
Another note: You’ll probably need to refresh the project reference to the Sharepoint.dll, typically found here – C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\Microsoft.SharePoint.dll.
